December 17, 2002 at 8:27 am
I have a first column called TIMELOG (datetime type). How do I update this datetime field whenever the user changes data in one or more columns in the row? I wrote a trigger which generates new time whenever the user performs an INSERT but just couldnt figure how to do it with an UPDATE.
Here is the code:
CREATE TRIGGER [UpdateData] ON dbo.TEST
FOR INSERT, UPDATE, DELETE
AS
update TEST set timelog = Getdate() where timelog is null
Please help.
December 17, 2002 at 8:35 am
Does your table have a primary key (or unique key)? If so:-
CREATE TRIGGER [UpdateData] ON dbo.TEST
FOR INSERT, UPDATE, DELETE
AS
update T set T.timelog = Getdate()
from TEST T inner join Inserted i on T.PrimaryKey = i.PrimaryKey
Regards,
Andy Jones
.
December 17, 2002 at 11:04 am
If you need it for INSERT only, create a DEFAULT on the timelog table and set it to GETDATE(). You do not need a trigger for it, it is much slower. Also, why are you creating a DELETE trigger?
For UPDATE you can use the trigger but the best way is to set the timelog column to GETDATE() in your UPDATE statement, not in the trigger.
December 18, 2002 at 11:09 am
As mromm mentioned,if for INSERT only, then it better set a default value = Getdate() for this column for the Test table.
When updating, avoid using trigger rather that UPDATE statement or ADO. When using these two, u need to notify the date format if datetime captured from local machine
December 18, 2002 at 10:50 pm
Andy,
That is one amazing code. It works great. For mromm, I knew how to add getdate() to the default but I also knew that I had to write more code to do the update with the trigger. Anyhow, combining the default getdate() and Andy's code gets the job done. Thanks for all your help.
December 19, 2002 at 4:43 am
Just an additional comment here. I love triggers and use them often. However, I also have been moving more and more logic into Stored Procedures and away from the app doing straight selects. My personnal view is if you have no body hitting the tables directly and you can wrap your query logic into SPs then put GETDATE() into you SPs logic and move out of the trigger.
The reason is this. When an update to the table is made the first update is processed. Whena trigger fires such as this it actually makes another transaction to the database. Also, the initial transacton does not signal completion until the triger has also completed. In a high transaction environment you eventually will start seeing contention from this. If you have the option and can move to an SP then you make one update and the transaction completes much faster.
December 19, 2002 at 11:07 pm
Antares66,
I understood the contention business. The update trigger action in theory could lead to an infinite loop. With my situation, how would you move this to the SPs using Andy's code so we can avoid the double update? Thanks in advance.
December 20, 2002 at 2:24 am
I think the point was just being made was that using a trigger performs two writes to the database and not one (one to insert/update the record and then one more update from the trigger). If all data access can be performed via stored procedures then you could move the updating of the timelog field directly to the insert/update statement i.e.
update Test
set
X = 1
,Y = 2
,Timelog = getdate()
.....
and not use a trigger at all (you could do the same sort of thing with the insert or use a default). But you can obviously only do this if all data access can be controlled and is only via the stored procedures i.e. if somebody updated some test data via an ad-hoc query then you would have to ensure the Timelog field was also updated (using the trigger automates this) although you could deny ad-hoc queries via privileges and guarentee data access is only via the stored procedures. You will have to decide on the best approach depending on your requirements.
Regards,
Andy Jones
.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply