August 26, 2008 at 9:52 am
Greetings all. I need to capture the date a certain field is updated, so I put a simple update trigger on the table, and I thought I was done. I then realized that when a new record was added, which populated the field in question at the time of insert, it was not capturing the update time, because it was not an update. I changed the trigger to...
ALTER TRIGGER tr_tblscheduled_lus_ecd_update
ON tblscheduled_lus
FOR INSERT,UPDATE
AS
IF @@ROWCOUNT = 0 RETURN
IF UPDATE(ecd)
BEGIN
UPDATE tblscheduled_lus
SET ecd_updated = GETDATE()
FROM tblscheduled_lus s,
inserted i
WHERE s.id = i.id
END
and it seems to work. My question is, will this trigger always know to populate ecd_updated whether an existing record is updated, or a new one is inserted? I tested it and it seems to work, but I may be missing something.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
August 26, 2008 at 10:06 am
It will work for inserts and updates.
August 26, 2008 at 10:24 am
As Steve replied, it will work on insert and update.
One little remark:
You only perform the extra update if the column is updated, meaning it is mentioned in the insert or update clause.
This does not mean it actualy changed the contained value !
from bol:
Testing for UPDATE or INSERT Actions to Specific Columns
You can design a Transact-SQL trigger to perform certain actions based on UPDATE or INSERT modifications to specific columns. Use UPDATE() or COLUMNS_UPDATED in the body of the trigger for this purpose. UPDATE() tests for UPDATE or INSERT tries on one column. COLUMNS_UPDATED tests for UPDATE or INSERT actions that are performed on multiple columns and returns a bit pattern that indicates which columns were inserted or updated.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 26, 2008 at 11:06 am
Thanks guys.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply