OK, so we’re all familiar with triggers now, right? You have seen that using a trigger you can log a change to a table in a special log file, or we can create a related record in another table. But all of these events happen after the action that triggers the trigger has completed.

What if you want to do something before the INSERT, UPDATE, or DELETE?

What if you want to do something instead of the action? For example, what if you want to simply flag a record as “deleted” rather then actually deleting the record?

What then, hot shot?

That’s where the INSTEAD OF TRIGGER comes in. Actually, the INSTEAD OF TRIGGER gives you one extra tool, you can now create triggers for views. The INSTEAD OF will handle inserting data into separate tables. You can simplify the data handling through this trigger.

Creating an INSTEAD OF Trigger

CREATE TRIGGER t_objectName_triggerAction
ON TableOrViewName
INSTEAD OF INSERT|UPDATE|DELETE
AS
    some T-SQL code here

Just like our FOR triggers, you start with the CREATE TRIGGER triggername ON objectName. The part we change is we use INSTEAD OF rather than FOR or AFTER.

Now I’d like to point out something that may not be obvious. If you build an INSTEAD OF INSERT trigger, then the values will not be in the table or view… but you will still have access to them from the inserted table.

Once you start to master this technique you can start doing some additional business logic in your database. Extra logging, and even intervening in user’s actions become possible. Be aware of the power you’ll now have over the basic functions INSERT, UPDATE, and DELETE.

If you have any questions, please, send them in. I’m here to help!