Changing A Field's Value When User Updates Data
Sep 10, 2007
Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.
View 9 Replies
ADVERTISEMENT
May 10, 2004
i currently have a table like this..
user_id username app_id app2_id app3_id app4_id
1 john 3 4 5 6
2 mike 4 5 6 6
3 manager 4 5 6 6
4 vicepres 5 6 6 6
5 ceo 6 6 6 6
6 board 6 6 6 6
the basic pattern is... a user has approvers, and those approvers have approvers as well... i have 4 columns of approvers.. and if my first approver is the manager, then my second approver will be the managers approver and my third approver will be the managers approver's approver and so on..
on my actual page, i have select fields for the app, app2, app3, app4 and i need it so that when i change the very first app, it'll automatically update app2, app3, and app4
any ideas how i would do this? im pretty new to sql but im thinking i would use some type of join?
thanks
View 7 Replies
View Related
Dec 19, 2007
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based:
Code Block
create table #IncomeAndExpenseData (
recordID nvarchar(5)NOT NULL,
itemID int NOT NULL,
itemvalue decimal(18, 2) NULL,
monthitemvalue decimal(18, 2) NULL
)
The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr.
For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table.
If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers.
How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
View 4 Replies
View Related
Aug 24, 2000
Is there a way to change the physical mapping of a user defined data type to the physical SQL Server datatype. It seems that once you create them, it is just about impossible to change it.
Thanks,
Andrew Abrams
View 3 Replies
View Related
May 14, 2008
We have a database that originally had all objects owned by a vendor id. We are in the process of changing the ownership to 'dbo' and wish to remove the vendor id but have found there are a few user defined datatypes that are also owned by this vendor id. How can I change the ownership of them to 'dbo'? They are currently being used in some of the tables. I tried the sp_changeobjectowner but that doesn't work for these.
thanks for any advice!
View 3 Replies
View Related
Jul 20, 2005
Hi GuysWonder if you could help me.Basically I produce an accounts package that uses a SQL 2000 DB as theRDBMS. I always instruct users to login as 'sa' and the relevantpassword when doing an update to my program, as sometimes I need to dodatabase changes for new stuff.Found that one of my users has not only logged in with their loginname (in this case Edward), but have also made this login a 'db owner'so that when I created 2 new user-defined data types they belong toEdward rather than dbo.This must have happened a long time ago, but now that they want tomove Edward round the roles and/or delete him from a copy of thedatabase that they have, they can't because he's the owner of theseuser-defined types.This brings me to the reason for my post, how can I change the ownerfrom Edward to dbo for these data types? I found an article ontechnet of how to do this, but when it suggests changing myuser-defined type to standard format it doesn't seem to work.Any ideas?RgdsRobbie
View 1 Replies
View Related
Sep 12, 2006
Hi,
I have a user defined data type which is called DEmployeeName,
it's length is varchar(20), how do i change it into varchar(30) without droping it?
I'm using SQL server 2005.
Thanks in advance..
View 1 Replies
View Related
Jun 18, 2004
Hi all,
I recently inherited multiple databases for a research study. These databases use an Access front end with the tables stored on SQL Server. Currently, there is a folder for each database on a network drive. I make changes to the front ends (forms, reports etc.) in a development version of the dbs, test them, have a user test them, and then import them to the production front end.
Each user has a copy of the front end on their 'C:' drive. The previous developer put together a separate VB app that copies the changed mdb files from the network drive to their 'C:' drive. This doesn't seem like the best solution to me but I haven't come up with a better one. I would appreciate any input.
Thanks,
Monk
View 1 Replies
View Related
Nov 27, 2007
We have a Table that contains a schedule. The schedule has certain pieces of information that are required to be updated by certain members of the
organization, specifically Shift, Start Date, and Line. Each record also has a column for the customer that it is for.
The schedule has a Column, [CSR] which lists the name of the person who is responsible for updating it. Format of that is domainnamejohndoe
What we would like to do is block domainnamejohndoe from being able to update or insert on any of the records that he is not responsible for.
So if he attempts to change the shift for example, it denies the change and possible pops up an error indicating that the change was blocked because he is not the responsible party.
Input in this is through a query which views the table data in MS Access. It cannot be moved to a Form without some serious redesign, so I cannot put the block in there. I assumed that this could be blocked with a Trigger that checks the [CSR] column, matches it to the logged in user through the suser_sname(suser_sid()) and then either allows or denies the update.
So is this a viable idea, or should I explore the move to an input form and make the change there in MS Access via VBA.
At first we started with just an audit trail, but I started to think that the audit trail would be needed only for records that should be allowed to be changed and that we should deny changes to those who are not allowed to make them.
Below is the audit trail trigger, I figure that the change should occur somewhere within that first IF statement, or make the first IF the second, and the first should be the permission check.
At this point I am stuck as to how to block the updates. I tried using Deny but that was not working. Filtering the results by only showing the logged in user their records is not an option as they need visibility to the entire schedule.
This is for SQL 2000 SP4/Windows 2000 SP4
CREATE TRIGGER audit_mschange
ON dbo.T_PP_Table_2
FOR update AS
IF (UPDATE([Start Dt]) OR Update(shift) or Update
(comments)
or Update(status) or Update (line))
BEGIN
INSERT INTO T_MS_RB_AUDIT
(ord_no,line_tank_old,line_tank_new,shift_old,shif t_new,comments_old,comments_new,status_old,status_ new,trx_dt,trx_username,
start_dt_old,start_dt_new)
SELECT ins.[PP Ord No],
del.line,ins.line,
del.shift,ins.shift,
del.comments,ins.comments,
del.status, ins.status,
getdate(),
suser_sname(suser_sid()),
del.[Start Dt],
ins.[Start Dt]
FROM inserted ins, deleted del
WHERE ins.[PP Ord No]=del.[PP Ord No] AND
ins.autonumber=del.autonumber AND
(ins.status <> del.status OR ins.[Start Dt] <>
del.[Start Dt] or ins.status <> del.status
or ins.comments <> del.comments or ins.line <>
del.line )
END
View 11 Replies
View Related
Jun 5, 2006
Bob writes "I want to change the value of a field but it is part of a
unique index. Can someone help me do this.
I need to update about 55000 rows.
thank you very much."
View 2 Replies
View Related
Jan 27, 2008
Hello,
I have a field on my SRS Report called directioncode.
This field gives me a value of 1 or 0.
How do I fix this code so
if the value of directioncode is 0, the text "Incoming" is displayed
if the value of directioncode is 1, the text "Outgoing" is displayed?
Current value in the expression area of my field....
=Fields!directioncode.Value
View 5 Replies
View Related
Jan 15, 2007
Hi all:
I need to change the year to many records that, by mistake, were put with a 2007 date, but they should be still with 2006, can anyone help me ?
I use Sql Server 2000 and the field is of type datetime
Thanks:eek:
View 1 Replies
View Related
Jan 27, 2008
This is a followup to my last post. If a field name is changed in the database, what is the easiest way to determine what stored procedures and triggers that reference that field are now broken?
View 3 Replies
View Related
Dec 7, 2006
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
View 3 Replies
View Related
Nov 1, 2006
Hello can anybody out there help.
I am just getting ready to move a system to production using SQL 2005 and SQL Mirroring. The problem that i have is when the databases fails over my connection string user account losses its rights on the DB untill i run the SP_CHANGE_USERS_LOGIN script. I have been trying to come up with a method of getting around this so far the only fix that i have is to make my user account sysadmin but this is far from ideal for production, does anyone have any ideas i was thinking that changing the SID of the User so that they match might get over the problem.
Any help will be good
Matt.
View 3 Replies
View Related
Mar 30, 1999
How can I change a field type whilst it is populated?
I have tried :
insert new_table
select *
from old_table
but I get :
Disallowed implicit conversion from datatype 'text' to datatype 'varchar'
Table: 'davy.dbo.new_table', Column: 'de_area'
Use the CONVERT function to run this query.
My table formats are as follows :
TABLE dbo.new_table (
de_pk int NOT NULL ,
de_name char (25) NOT NULL ,
de_area char(255) NULL
)
TABLE dbo.new_table (
de_pk int NOT NULL ,
de_name char (25) NOT NULL ,
de_area text(16) NULL
)
View 1 Replies
View Related
Jan 3, 2005
I have a table with approx 1200 names in the format of "John Doe" I need to edit the names to the format of "Doe, John"
Any easy way in SQL to do this or am I better off dumping to text file, making the changes in another app and then updating the table?
View 3 Replies
View Related
Jan 17, 2006
Hi,
Is it possible to change any fieldname of an existing table?I mean to say
by TSQL statement.We know that we can alter the data type and width etc.
But I haven't got any info about filedname change.So if it is possible
Please help...
And Is there any TSQL command to alter multiple columns in a single statement?
Please help...
Thanks!!
Joydeep
View 7 Replies
View Related
Jul 7, 2006
We have a small table of about 13 million rows that needs altered. A column in the table needs to be changed from a varchar(20) to a varchar(500). When we ran the alter table script, 3 hrs later and it wasn't done running. Any suggestions on what we can do to speed up the process?
Thanks ahead of time
DMW
Edit:
We are running SQL Server 2000 and the db at the time was running in simple mood
View 1 Replies
View Related
Oct 18, 2007
SQL 2000 Enterprise Manager won’t let me drop a unique key index and move it to another field. I get an error regarding ‘drop index not allowed because of unique key constraint enforcement.
Is there anyway to change fields for unique keys in SQL Enterprise Manager?
Thanks, Al
View 4 Replies
View Related
Jul 7, 2000
Can the user change their password in Enterprise Manager? When they follow the documentation, this does not seem to work
unless they are sa: (I know about sp_password....we are trying to find a GUI method for typical user.)
Expand a server group; then expand a server.
Expand Security; then click Logins.
In the details pane, right-click the login to modify, and then click Properties.
In Password, on the General tab, enter a new password.
Confirm the password.
View 2 Replies
View Related
Feb 12, 2001
My SQL servers are using integrated windows nt security. Our user account is changing to a new domain. Is there an easy way to change the server logins to point to the new domain instead of removing the user and adding a new login from the new domain.
View 1 Replies
View Related
Aug 8, 2007
I'm trying to automate the creation of a databse and a User/Login but i'm running into problem with a conflict between a new user to which i'm trying to assign a WindowsLogin which is already linked to the dbo. (SQL Server 2005)
Is there a way to reassign the dbo's LoginName to some othe user
Here the code
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ABC')
BEGIN
declare @cmd nvarchar(max)
set @cmd = 'CREATE USER [ABC] FOR LOGIN ' + @WindowsLogin + ' WITH DEFAULT_SCHEMA=[Shared]'
exec(@cmd)
END
Which give me the error
Msg 15063, Level 16, State 1, Server SR-DEV-GOI1, Line 1
The login already has an account under a different user name.
The script is running under the @WindowsLogin in question !
Thanks All
Gilbert
View 2 Replies
View Related
May 4, 2003
Here's the deal:
I import a flat file from a legacy system, and then convert it into a single table. That works simply enough.
Then I have a SP that querys that table using a parameter for an accountID. My business tier calls that SP and returns the results to the calling tier (my web application). Easy enough...
Now for the question. The people who created the flat file (written in COBOL) decided to use "codes" to represent data. So, for instance, if I'm looking for the account plan, I can expect to see characters like ], or [, or +, etc... These characters have a special meaning, like:
] = Plan A
[ = Plan B
+ = Plan C, and so on.
Currently, the web application displays those characters, but I want it to display the actual plan name. Is there a way that when I execute the SP, the SP could pull the necessary records, and whenever it encounters a certain "plan" character, it could convert it into a "readable" name? Say that it sees that the plan_type field has a value of "]" for twenty records, so it converts those twenty records' plan_type value from "]" into "Plan A"? I'm not sure if I can do that, but I want to at least evaluate the option if I can.
I've evaluated other options, like using a CASE statement in my code, but I shot that down quickly...for obvious reasons. I don't wanna be changing my web application or business tier each time these guys update a plan name, or add a new one, delete an existing one, etc...
I've also thought about creating a dictionary table than contains the plan's code and its name, and then just INNER JOIN the first table with the dict table. This would keep my SP very simple (it's very straight-forward right now, and I like that). That way, if a plan name is ever changed, or a new one is added, I simply update the dict table using a simple query. However, if my SP is doing the conversion, I could just as easily update the SP.
Either of these methods would work for me, and I *do* know how to do the latter (dict table). However, there are quite a few other fields that I may have to do this for. I believe when I left for the day on Friday, my last count was 14 fields total that needed translation. That would mean 14 different dict tables! That could certainly affect my SP performance with all those INNER JOINS!
Therefore, I'm certainly interested in figuring out if it's possible to do the former method (SP), and then I shall decide which method is best for my situation.
Feel free to include your thoughts on which process you think is better as well. I'm really riding the fence with this one. However, if I can't find out how to change field values in my SP, then obviously I'll make a decision very quickly...
Thanks in advance.
View 3 Replies
View Related
Jul 17, 2004
Hi
My DB is on the web so but I want change Field Lenght of one of my Fileds on my DB
its 70 rights now I want change it to 120 its a nvarchar
how can I do it , I am using a ASP page
Thanks
View 1 Replies
View Related
May 23, 2007
Why is it that when you change a field name is SQL Server it sometimes completely messes things up. I renamed a field in one of my tables from Emp# to EmpNumber. I had a view based on this table and naturally I knew I would have to change a view I had based on the table. I opened the view and changed the field from Emp# to EmpNumber but when I tried to open the view I got an error “Invalid column Name EMP#”. I have not been able to fix this. I have dropped and recreated the view, refreshed all the objects using enterprise manager, refreshed all the objects using Query Analyzer, shut down and restarted my computer, taken my database offline and put it back on line. The field name EMP# is not in any tables in my database and not referenced any views or procs . I am just starting on this database so I could verify this very easily because I only have a few views and procs.
Has anyone had this problem or more importantly does anyone know why this is happening or how to fix it?
View 9 Replies
View Related
Jan 6, 2004
Hi
I have two databases that are merged using replication, and I want to change the length of one of the fields. Can anyone think of a way of doing it that doesn't require dropping the whole publication and rebuilding it? Thanks Ed
View 2 Replies
View Related
Dec 26, 2006
Hi Everyone,I have a question about dynamically changing the length of a varchar(n)field, in case the value I'm trying to insert is too big and will givea "truncated" error, but before the error is given! i.e. Is there somekind of a way to "test" the length of the field while Inserting thevalue into it, and to have it automatically increase its length to thelength of the value being inserted, in case the value is too big?I've been able to do this in a "primitive" way, simply by identifyingthe specific error number in case the value is being truncated, andthen increasing the length of the varchar(n) field by using the ALTERcommand, and then duplicating the insert statement, but is there astandard (shorter) way of doing this?Here is my code (I'm working in an ASP environment):<%var_txt = "abcdefghijklmnopqrstuvwxyz12345678789"sql = "Insert Into Table1 (text) Values ('" & var_txt & "')"On Error Resume Nextconn.Execute sqlIf err = -2147217833 ThenResponse.Write "Error Recognized Successfully!<br /><br />"sql = "ALTER TABLE Table1 ALTER COLUMN text VARCHAR(" &Len(var_txt) &") NOT NULL"On Error Resume Nextconn.Execute sqlIf err<>0 ThenResponse.Write "Error while trying to alter Column:<br/>" & err & "= " & err.description & "<br />"ElseResponse.Write "Column altered successfully to: " &Len(var_txt) &"<br />"sql = "Insert Into Table1 (text) Values ('" & var_txt &"')"On Error Resume Nextconn.Execute sqlIf err<>0 ThenResponse.Write "<br />Error number 2:<br />" &err.description &"<br />"ElseResponse.Write "Now it was added successfully!HaHa!<br />"End IfEnd IfElseResponse.Write "Success."End If%>Thanks in advance!
View 2 Replies
View Related
Sep 18, 2015
I've got this virus URL... in our production server. It is changing the values in a table column.
View 6 Replies
View Related
Apr 17, 2011
Is there a way to prevent a particular user with SQL login from changing their password?
We have an application where we only want to prevent a certain user from changing their password but do not want to use windows authentication?
View 14 Replies
View Related
Apr 8, 2004
Is there an automatic way of changing the Domain user password getting used for running the SQL Server as a Domain user account? I'm taking about EM---Security----Domain User name and the password getting used for running the SQL Server?
View 1 Replies
View Related
Oct 15, 2007
I tried to cahnge this account option to built in account option in SQL ( MSSQLSERVER) properties box. Then SQL Server was log on as Local system. But then I can not view my reports which i created. Now I changed it again to this account option. but I dont exaclty remember the password. Is there anyway I can get to know the password I created.
Thanks
View 1 Replies
View Related
Jun 3, 2015
Is there a kind of log to trace the change on DB user, such as when the password/permission is changed, etc. If yes, how can i get this log; If no, is there a way to achieve this thing?
View 2 Replies
View Related
