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.
The term BEFORE trigger is, in my opinion, not silly. ANd it is defintely not the same as an INSTEAD OF trigger.
A BEFORE trigger (which does not exist in SQL Server, but is implemented in severl other DBMS's) will fire after
the rows to be updated (or inserted or deleted) are determined, but before
the modification is actually carried out. In a BEFORE trigger, the data in the inserted pseudo-table can be changed. After the BEFORE trigger has executed, the database will carry out the changes that were determined before the trigger was fired - but it will also respect modification made by the trigger to the changed data.
An INSTEAD OF trigger also fires after determining the rows to be affected and before actually affecting them, but this type of trigger runs (as the name implies) instead of
actually changing the data. If the INSTEAD OF trigger does nothing, then the changes will just be silently ignored.
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis