Chris Harshman (1/10/2011)
In my previous life as an Oracle DBA / Developer, I think I understand this question better than people with just a SQL Server background. Other database systems have the concept of a "BEFORE" trigger that occurrs before the actual statement being executed applies changes to the table. SQL Server only has AFTER triggers
No SQL Server has AFTER, FOR and INSTEAD OF triggers. Instead of is your BEFORE trigger which is a silly concept with the term BEFORE. Before I insert to a table or update a table, I haven't done anything. INSTEAD of makes more sense. INSTEAD of INSERTING or UPDATING a table, do xyz, which likely includes an INSERT or an UPDATE statement.
Books Online see "CREATE TRIGGER"
FOR | AFTERAFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.
AFTER is the default when FOR is the only keyword specified.
AFTER triggers cannot be defined on views.
INSTEAD OF
Specifies that the DML trigger is executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. INSTEAD OF cannot be specified for DDL or logon triggers.
At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, you can define views on views where each view has its own INSTEAD OF trigger.
INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION. SQL Server raises an error when an INSTEAD OF trigger is added to an updatable view WITH CHECK OPTION specified. The user must remove that option by using ALTER VIEW before defining the INSTEAD OF trigger.