How to achieve row level row level functionality in triggers?Help!?

[marcarls]

Hi,
I have written triggers for diffrent databases that we are going to use to audit changes in some of our tables. In Oracle for example you can create a triggers that fires for each inserted row, which is useful if the update statement updates more than one row. But I have understood that this is not supported in MS SQL Server.

Is there anyone who has an idea of how I can achieve this anyway by writing the trigger in some specific way. I am a bit desperate at this time since this is an urgent problem at the moment and I do not know how to solve it.

Any tip would be helpful and I would love you forever!!

Regards Maria

 

[nigelrivett]

What do you wish to do.
Usually you would not try to act on each row individually but would use set based operations and join the inserted, deleted and underlying table.

If you really must you could define a cursor to loop through the rows.

If you want to generate an audit trail see

http://www.nigelrivett.net/Triggers_2_Creating_Audit_Trails.html

http://www.nigelrivett.net/AuditTrailTrigger.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[marcarls]

What I want to do is this.

I have a log table where I log changes, not just per row but per column. Example:
ID Column_name Old_value New_value
1 telephone 031-56879 08-45878
2 Address My old adr My new address

My update trigger check per column which columns that is beeing update.
IF UPDATE(telephone)
INSERT INTO log_table(ID, column_name, old_value, new_value)
SELECT @log_id, 'telephone', old.telephone, new.telephone
FROM deleted old, inserted new
WHERE new.ID = old.ID
AND ( ( New.telephone IS NULL
and old.telephone IS NOT NULL)
OR (new.telephone IS NOT NULL
and old.telephone IS NULL)
OR (new.telephone != old.telephone ) )
IF @@rowcount > select @log_id = @log_id + 1

If a record is inserted a count up my log_id, so next insert of changed column will have a unique id.

This works fine if just one record is updated. But if I write a statemant that updates more that one row I will recieve a Violation of PRIMARY KEY constraint error. Since my trigger is written to function on row level. So I want to make it work as a rowlevel trigger.

Hm I hope I could make myself understood, english is not my best way of expressing myself

 

[SQLSister]

Why not just let the id be an autonumber field (Set it as an integer withthe identity property turned on) and not try to figure out the number yourself inthe trigger? Then you can run the update statement minus the id field and all will be happy.

In SQL server you should try to avoid row by row processing, it is very inefficient.