Update Trigger record-level 1

[firsttube]

I want to update a date field every time a record is updated, but only for the record that was updated.

I have this:

CREATE TRIGGER updateDateEdit ON [dbo].[Tester] 
FOR UPDATE
AS
UPDATE dbo.Tester SET DATE_UPD = GETDATE()

but it sets the date for all records, not just the one that was updated.

can this be done?

Information is not Knowledge, Knowledge is not Wisdom, Wisdom is not Truth, Truth is not Beauty, Beauty is not Love, Love is not Music, Music is the best.

 

[bborissov]

CREATE TRIGGER updateDateEdit ON [dbo].[Tester]
FOR UPDATE
AS
UPDATE Tester SET DATE_UPD = GETDATE()
FROM dbo.Tester Tester
INNER JOIN Updated ON Tester.PK = Updated.PK

--- Updated table has the same structure as Tester

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ptheriault]

Yes, you need the where clause in your update statement. You will need to get the id of the row you have updated. You want something like this.

DECLARE @id int

SELECT @id = id from inserted

UPDATE ...
WHERE id = @id.

 

[bborissov]

Oops, Table name is INSERTED, not UPDATED, sorry I forgot about that.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

DECLARE @id int
SELECT @id = id from inserted
UPDATE ...
WHERE id = @id.

REMEMBER, triggers fire per statement not per row
That code will fail miserably
you need to use the inserted pseudo-table

CREATE TRIGGER updateDateEdit ON [dbo].[Tester]
FOR UPDATE
AS
IF @@ROWCOUNT =0 
RETURN

UPDATE Tester SET DATE_UPD = GETDATE()
FROM dbo.Tester Tester
INNER JOIN inserted ON Tester.PK = inserted.PK

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ptheriault]

Thanks Denis,
I forgot about that..

 

[firsttube]

Thanks everyone....
Thanks to SQLDenis for a successful trigger!

Information is not Knowledge, Knowledge is not Wisdom, Wisdom is not Truth, Truth is not Beauty, Beauty is not Love, Love is not Music, Music is the best.