Trigger Question - IF Update()

[hawspipe]

Hi All,
I'm hoping that someone will be able to help me out or atleast point me in the right direction.

I'm trying to use the If Update() in my trigger, my question is - can the value(column) that is placed in the parens be added "on-the-fly". The code below shows what I'm trying to do, but a variable can't be placed in the parens as it is current showing.

The reason behind this is that my users will have access to a table that will define which table/columns that they want to be notified when it is updated. Each customer's choice will be different. There are only about 10 tables, however, each table can have anywhere between 0-20 columns that are being checked to see if then have been updated.

CREATE TRIGGER test_trigger ON item_master
FOR UPDATE
 
 AS 

DECLARE @TR_COLUMN_NAME varchar(40) 
DECLARE item_cursorupdate CURSOR 

FOR SELECT TR_COLUMN_NAME 
FROM BATCH_MISC 
Where TR_TABLE_NAME='item_master'  

 OPEN item_cursorupdate 

 FETCH NEXT FROM item_cursorupdate 
  INTO @TR_COLUMN_NAME

 WHILE @@FETCH_STATUS=0  
  BEGIN IF update(@TR_COLUMN_NAME)  
    BEGIN INSERT INTO logtable (item_id,field,date) VALUES
   (1,@TR_COLUMN_NAME, getdate())
    
  END 

 FETCH NEXT FROM item_cursorupdate INTO @TR_COLUMN_NAME        
    
 END close item_cursorupdate 
deallocate item_cursorupdate

I would like to have to only worry about 10 triggers (one for each table). Any thoughts or ideas.

 

[SQLDenis]

AFAIK No
However UPDATE() fires everytime even if you do something like
UPDATE Table1 SET Amount = 100 WHERE Amount =100

You have to compare deleted and inserted pseudo-tables and account for NULL values and multirow statements

A little more detail is available here

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

you can just bnuild a bunch of IF conditions

IF update(Name)
BEGIN INSERT INTO logtable (item_id,field,date) VALUES
(1,@TR_COLUMN_NAME, getdate())

END

IF update(Phone)
BEGIN INSERT INTO logtable (item_id,field,date) VALUES
(1,@TR_COLUMN_NAME, getdate())

END

etc
etc
etc

And don't forget about multi-row statements a trigger fires once per statement not once per row




Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/