Triggers in MS SQL versus triggers in other databases

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.

spacer