April 20, 2006 at 7:12 am
Need to create a Trigger to UPDATE a tables DATETIME column to current DATE/TIME any time an UPDATE takes place on the given row.
Unfortunately, this TRIGGER is UPDATING "ALL" ChangeDate values in the table. Can someone examine and supply TRIGGER SQL to only UPDATE the ROW being UPDATED.
CREATE TRIGGER trgu_TABLE_A ON TABLE_A FOR UPDATE
AS
BEGIN
UPDATE TABLE_A SET ChangeDate = (SELECT GETDATE() FROM INSERTED)
END
Thx in advance
April 20, 2006 at 7:15 am
look up "TRIGGER INSERTED" on msdn.
April 20, 2006 at 7:25 am
You need a "where" clause on that update to only update the row in question.
April 20, 2006 at 7:26 am
You need to JOIN the inserted table to the updated table using the primary key, e.g.
UPDATE a
SET ChangeDate = GETDATE()
FROM TABLE_A a
INNER JOIN inserted i
ON i.[ID] = a.[ID]
Far away is close at hand in the images of elsewhere.
Anon.
April 20, 2006 at 7:48 am
CREATE TRIGGER [trgu_TABLE_A] ON [dbo].[TABLE_A] AFTER UPDATE, INSERT
AS
BEGIN
UPDATE TABLE_A SET ChangeDate = GETDATE()
FROM inserted i INNER JOIN TABLE_A c ON i.ID = c.ID
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply