Trigger difference between SQL Server and Oracle

(From the guide of migrating Oracle to SQL Server 2008)

For DML triggers only.

1. Row level trigger vs statement level (table) trigger

The first major difference between Oracle and SQL Server triggers is that the most common Oracle trigger is a row-level trigger (FOR EACH ROW), which fires for each row of the source statement. SQL Server, however, supports only statement-level triggers, which fire only once per statement, irrespective of the number of rows affected.

Oracle Row-level triggers are emulated with a cursor loop in SQL Server.
The following code is for after insert.

DECLARE ForEachInsertedRowTriggerCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECT [ROWID], , .. FROM inserted

OPEN ForEachInsertedRowTriggerCursor
FETCH NEXT FROM ForEachInsertedRowTriggerCursor INTO v1,v2...
WHILE @@fetch_status = 0
BEGIN
...
FETCH NEXT FROM ForEachInsertedRowTriggerCursor INTO v1,v2...
END

CLOSE ForEachInsertedRowTriggerCursor DEALLOCATE ForEachInsertedRowTriggerCursor

Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, use a statement trigger to:
    - Make a complex security check on the current time or user
    - Generate a single audit record


2. temporary tables used in trigger

In a row-level trigger, Oracle uses an :OLD alias to refer to column values that existed before the statement executes, and to the changed values by using a :NEW alias. SQL Server uses two pseudotables, inserted and deleted, and each can have multiple rows.

If the triggering statement is UPDATE, a row's older version is present in deleted, and the newer in inserted. But it is not easy to tell which pair belongs to the same row if the updated table does not have a primary key or the primary key was modified. PK is important if you want to implement triggers on that table.


3 No Before trigger in SQL Server

The third major difference between Oracle and SQL Server triggers comes from Oracle BEFORE triggers. Because Oracle fires these triggers before the triggering statement, it is possible to modify the actual field values that will be stored in the table, or even cancel the execution of the triggering statement if it is found to be unnecessary. To emulate this in SQL Server, you must create INSTEAD OF triggers. All triggers for a specific event should go into one target instead of trigger.

4. No column sensitive trigger in SQL Server

Sometimes an Oracle trigger is defined for a specific column with the UPDATE OF column [, column ]... ] clause. To emulate this, it can be done with the following SQL Server construction:


IF (UPDATE(column) [OR UPDATE(column) . . .]
BEGIN

END

(
Update(column)
Returns a Boolean value that indicates whether an INSERT or UPDATE attempt was made on a specified column of a table or view. UPDATE() is used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions
)