The Magic of Triggers

I was recently asked a question at work by one of our developers.  He needed some help on updating one of his stored procedures.  He was looking to see if there was some kind of variable that he could use to pass the current table he was querying.  This made me curious as to why he would pass such a variable.  After some discussion, I realized that he was looking to create audit trail for our internal website using that stored procedure.

My suggestion is quite uncommonly known for people new to Microsoft SQL Server.  I introduced him to the idea of triggers.

SQL Triggers

Now, what are triggers and why are they so useful?  Triggers are a special kind of stored procedure that occurs (is triggered) when a certain event happens on a given table.  Different events that can activate a Trigger include:

• Insert
• Update
• Delete

So, now onto why they are useful.  Let's say, for instance, that you have a Customer table and every time a new customer is added, a contact is created in your Contact table.  Now, you could write a stored procedure that you would call once you've added the customer account.  On the other hand, why not create a trigger that would insert the new contact into the Contact table automatically, without a need for calling anything.  The trigger would look something like this:

CREATE TRIGGER tContact
ON Customer
AFTER INSERT 
AS 

INSERT INTO Contact (CustomerId, FirstName, LastName, EMail, PhoneNumber)
SELECT CustomerId, FirstName, LastName, Email, PhoneNumber

from Inserted

GO

SQL Server also creates two temporary tables that can be used during the Trigger: Deleted and Inserted.  The Deleted table contains original data from the table during Update and Delete statements.  The Inserted table contains data that is effected during Insert and Delete statements.  These tables could be used to grab data for whatever the purpose of your trigger is.

Overall, Triggers can be very useful, depending on the purpose.  Don't create too many triggers, though, as this could create too much overhead on the system.  Also, triggers are extremely hard to debug, so for debugging purposes, stored procedures might be better.  For more on the disadvantages triggers, visit the following blog:

Disadvantages of Triggers