trigger refresher

[Bell1991]

How do i create a trigger for update. I need it to update the modified date and how it was updated based on the id that was just updated..

It has been a while since i worked with triggers..

Create TRIGGER tblUpdate on tblA
For Update
As
Update tblA
set dtDateModified = getDate() ,
FlagHowModified = 'web'
where ID = (ID just updated)

Thanks

 

[ptheriault]

You would use the ID from the Inserted table.

Create TRIGGER tblUpdate on tblA
For Update
As
Update tblA
    set dtDateModified = getDate() ,
         FlagHowModified = 'web'
FROM tblA A
 JOIN INSERTED 
  ON i.ID = A.id

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[SQLDenis]

I would add to the condition

instead of

JOIN INSERTED
ON i.ID = A.id

I would do

JOIN INSERTED
ON i.ID = A.id
and i.SomeCole <> a.Somecol

because the trigger will fire even if you do this


update table set
val = val
where id = 1

see nothing got changed but the trigger will still fire

and also beware of nulls
you have to use cloalese around this becuase if you change from or to nulls it won't match

read this (

http://sqlservercode.blogspot.com/2005/12/fun-with-sql-server-update-triggers.html)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[LNBruno]

Hey, and as long as we're at it (FAQ in the making?):

-- make sure we use the correct database:
USE TheRightDatabase
GO

-- drop the trigger if it exists:
IF OBJECT_ID ('trg_UpdateStatistics_U','TR') IS NOT NULL
   DROP TRIGGER trg_UpdateStatistics_U
GO

-- build the new and improved (?) trigger:
CREATE TRIGGER trg_UpdateStatistics_U
    ON dbo.StatisticalStuffWeTrack
   FOR UPDATE
AS...

< M!ke >
Acupuncture: a jab well done.

 

[SQLDenis]

since we are at it the first stement should be

if @@rowcount =0
return


because if nothing happened you want to exit as fast as possible

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com
Google Interview Questions

 

[warkre]

But where should the if@@ be? [I'm new to SQL and try to
get a trigger working. 1st checking what's here ]
-- 8< ------------ 8< --------- 8< ---------
-- make sure we use the correct database:
USE TheRightDatabase
GO

-- drop the trigger if it exists:
IF OBJECT_ID ('tblUpdate','TR') IS NOT NULL
DROP TRIGGER tblUpdate
GO

-- build the new and improved (?) trigger:
Create TRIGGER tblUpdate on tblA
For Update
As
if @@rowcount =0
return
Update tblA
set dtDateModified = getDate() ,
FlagHowModified = 'web'
FROM tblA A
JOIN INSERTED
ON i.ID = A.ID
AND i.SomeCol <> a.Somecol

 

[SQLDenis]

>>AND i.SomeCol <> a.Somecol


this won't work if you allow NULL values, use ISNULL or COALESCE


This is correct
-------------------------------------
Create TRIGGER tblUpdate on tblA
For Update
As
if @@rowcount =0
return


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions