Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.