Conditional triggers

[MMIMadness]

I have a trigger that updates a date time field every time the particular record is edited, so i can keep a bit of an audit trail. but what i need to do is stop the trigger from fireing if either of two feilds are changed as i don't need to audit them and it does muck up the audit procedure. is their any way of checking if a particular field has been changed and then stopping the trigger/update code to fire.

E.G.

If col1 = change or col2 = change then
don't run update
else
update audit col
end if

that sort of thing.

if anyone can help i would be most gratful.

Adam,

The current trigger code

CREATE TRIGGER Update_time ON [dbo].[Tbl_Core_details] 
FOR insert, UPDATE
AS
DECLARE 
@TrigTime DateTime
set @TrigTime = getDate()

update 
tbl_core_details
SET
Update_time = (@trigtime)
from 
inserted, tbl_core_details
WHERE
tbl_core_details.apid = inserted.apid

 

[NoCoolHandle]

There is an updated property/method you can use in a trigger to determine which col was updated..

(of course you could also look at the inserted and deleted tables to get the same info..)

However using the updated method..

Create Trigger tr_someTrigger 
on SomeTable
for Update
as
If Update(somecol)
    begin
      -- do something
    end
else if update(someothercol)
    begin
       -- somehtging eles
     end

\

HTH

Rob

 

[SQLDenis]

>>However using the updated method..


won't work

update table1 set value =2 where value =2

the if update() will fire but nothing has changed

you have to join deleted and inserted pseudo tables and also account from and to values changing to NULL

read Fun With SQL Server Update Triggers

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

 

[SQLSister]

And while you are at it, get rid of the old style joins in your triggers. THey will not work when you move to 2005. Use ASI standard joins instead.

"NOTHING is more important in a database than integrity." ESquared

 

[SQLDenis]

>>And while you are at it, get rid of the old style joins in your triggers. THey will not work when you move to 2005. Use ASI standard joins instead.

Both are ANSI ;-)

select * from table1,table2 will work no problem, it works also in sql server 2008 July CTP

What does not work is *= and =* this has to be done with left and right joins instead

But I do agree that you should use normal joins since it is clearer what you are joining without scrolling to the WHERE clause



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

 

[SQLSister]

Sorry, I thought I read they were taking this support out including the inner joins.



"NOTHING is more important in a database than integrity." ESquared