SQL Server Triggers
Published: Aug 06, 2017
Triggers are kind of like stored procedures… they’re simply pieces of code that can execute based on another event… ie. They’re “triggered” by another event.

These triggers can perform follow-up events or they can actually replace the action that the user thinks is happening. The former is called an “AFTER” trigger and the latter an “INSTEAD OF” trigger.

So why would we want or use triggers in our database?

Well they can be incredibly handy within the database architecture in many scenarios. However, you do have to be careful because they do come with a performance hit and can cause no end of problems if used incorrectly.

For example, if we wish to insert data into a table and have that insert also enter something into a logging table, then a trigger would be a good way to achieve this.

But where you have to be careful is that, in the example above, the original Insert won’t commit until the trigger actions have also committed.

You also need to be careful of nesting triggers. Basically of having an action which fires a trigger, causing something to happen on another table… but that table also has a trigger… and the next table has a trigger etc. This can be disastrous to both troubleshoot and for performance.

There are 2 main types of Trigger available… INSTEAD OF, and AFTER.

The After Trigger, as its name indicates, performs something AFTER an Update / Insert / Delete has occurred on a table.

The Instead Of trigger, also as implied, performs something INSTEAD OF the action you were performing. Therefore you could simply rollback an Insert, or maybe alter data as it’s updated (maybe adding tax to a value which is being forcibly entered as a net value by an application).

Okay, those may be lame examples, but you get the gist.

It’s also key to note that Triggers have access to special “hidden” tables Inserted and Deleted which it can use in order to perform its actions.

If you wished to make an Update to a record and log a before and after view, then you could use a trigger to do this. In regard to an Update, the Deleted table would hold the before state and the Inserted table holds the after state.

Hopefully you can see that there are some good uses for Triggers in an environment. Just make sure that you use them wisely.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron