Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update Trigger record-level 1

Status
Not open for further replies.

firsttube

Technical User
Apr 21, 2004
165
CA
I want to update a date field every time a record is updated, but only for the record that was updated.

I have this:
Code:
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.
 
Code:
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
 
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.

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

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
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

Code:
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:
Personal Blog:
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top