Update Trigger on specific field update

[firsttube]

I have this trigger:

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


UPDATE Tester SET DATE_UPD = DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
FROM dbo.Tester Tester
INNER JOIN inserted ON Tester.ID = inserted.ID

This will add today's date in the DATE_UPD field whenever a record is updated. What I wasnt is only for this trigger to run when the field called STATUS is updated. Is this possible?

thanks
ft


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.

 

[SQLDenis]

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

IF UPDATE (Status)
BEGIN
UPDATE Tester SET DATE_UPD = DateAdd(Day, DateDiff(Day, 0, GetDate()), 0)
FROM dbo.Tester Tester
INNER JOIN inserted ON Tester.ID = inserted.ID
WHERE Tester.Status <> inserted.Status
END

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[firsttube]

That worked great, thanks SQLDenis!

I have another question now

I have 2 tables, A and B, and I want to make a trigger on table B that will alter a matching record in table A when a specific field in table B is updated:

CREATE TRIGGER upd_tableA_Number ON dbo.TableB 
FOR UPDATE
AS

IF @@ROWCOUNT =0 
RETURN

IF UPDATE(Number)
Begin
UPDATE dbo.TableA SET Number = inserted.Number
FROM dbo.TableB TableB, dbo.TableA TableA
INNER JOIN inserted ON TableA.RecID = TableB.RecID
end

this does not seem to work...any ideas?

thanks
ft





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.

 

[SQLDenis]

because you don't have a join between inserted and a or b

I don't know the join condition between inserted and a, here is a start


IF UPDATE(Number)
Begin
UPDATE a SET a.Number = i.Number
FROM dbo.TableA a
INNER JOIN TableB b on a.RecID = b.RecID
INNER JOIN inserted i ON a.RecID = i.RecID --Not sure what your column is
end

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Catadmin]

I think you got confused where you're joining tables with the 2 different join styles.

CREATE TRIGGER upd_tableA_Number ON dbo.TableB 
FOR UPDATE
AS

IF @@ROWCOUNT =0 
RETURN

IF UPDATE(Number)
Begin
UPDATE dbo.TableA 
SET Number = inserted.Number
FROM inserted 
Inner Join dbo.TableA a
on inserted.RecID = a.RecID
end

Now if RecID is an identity field that has just been created, you'll want to do some Scope_Identity() stuff in your trigger. Otherwise, I believe the above will work.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLDenis]

>>Now if RecID is an identity field that has just been created, you'll want to do some Scope_Identity() stuff in your trigger

Can't be true, this is an update trigger

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Catadmin]

DOH!

Sorry. Having a blonde moment here.

Scary thing is, I'm a brunette. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"