INSTEAD OF Triggers

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!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *