After a whole day working with MS SQL triggers (after reading all the Miicrosoft documents I can find), I event found out that the triggers in MS SQL are actually done at statement level. I was used to working with Ingres that triggers are at row level. So, for INSERT/UPDATE/DELETE triggers, I can record the changes at row level.
create rule audit_mytable_inserted after insert of mytable execute procedure audit_mytable ( p_new_value = new.value, p_old_value = old.value) ;
In MS SQL, I can not do like above. I have to do the following:
CREATE TRIGGER [dbo].[mytableTrigger] ON [dbo].[mytable] AFTER UPDATE, INSERT AS IF UPDATE(myvalue) BEGIN INSERT INTO myvalues ( myvalue, change_date, status ) SELECT myvalue, GetDate(), 'inserted' FROM inserted where myvalue = 1; INSERT INTO myvalues ( myvalue, change_date, status ) SELECT myvalue, GetDate(), 'deleted' FROM deleted; END
MySQL is even worse. Quote from MySQL website “MySQL triggers are activated by SQL statements only. They are not activated by changes in tables made by APIs that do not transmit SQL statements to the MySQL Server; in particular, they are not activated by updates made using the NDB API”.
I believe Oracle handles triggers similar to Ingres. That is, the triggers are at row level.
I prefer the way PostgreSQL implemented triggers by allowing both statement and row level triggers.