Database Triggers

During my experience at work, I have used a few triggers on various projects and I have demonstrated how to implement one in my own personal development portfolio. The purpose of today's blog post will be to explain what a Database Trigger actually is, discuss the different types of Trigger and cover their main concepts.

What is a Database Trigger? 

Triggers are a special type of stored procedure which is executed when an event occurs. They can contain exactly the same code as any other stored procedure, so if you haven't already, it may be worth looking at my 4-part blog series on Stored Procedures before you continue reading:

• Stored Procedures (Part 1) 
• Stored Procedures (Part 2)
• Stored Procedures (and their vulnerabilities!) (Part 3)
• Stored Procedures (Part 4) - Cursors

In those previous blog posts, I have covered a lot of the brunt work and syntax that may commonly be found in both Stored Procedures and Triggers; Triggers are basically an extended functionality of the Stored Procedure.  

Types of Database Trigger

There are 3 types of Trigger, which I am going to discuss and cover within this section. 

DML Triggers

Firstly, DML stands for Data Manipulation Language and is the syntax that is responsible for inserting, updating and deleting data within a database. So it probably wouldn't surprise you to know that DML Triggers are created against a database table or view and are fired when either an INSERT, UPDATE or DELETE statement occurs. This is quite possibly the most common trigger that you will come across, and in my case the only trigger type I have ever actually used (though it is still important to know what the other types are in case you ever have a need to use them!).

The example below shows a Trigger that I wrote in order to populate a custom full record number for each User Asset within the database, and runs whenever an INSERT or UPDATE event occurs . The image below quite large so you will have to click on it to see it full size (sorry!). Follow the green comments as they explain exactly what the trigger does:

As you can see, Triggers follow a very similar syntax and set up as any other Stored Procedure. The main difference is (obviously) declaring it as a TRIGGER instead of a PROCEDURE and also declaring when the trigger will be fired (so on INSERT, UPDATE, or DELETE):

When inserting data, a temporary table called inserted is created.
When updating data, 2 temporary tables are created; inserted and deleted.
When deleting data, a temporary deleted table is created.

When these events occur within the Trigger, the inserted and deleted tables are then made available to the Trigger itself so you can manipulate the result set within either, which can be incredibly useful.

The FOR clause, can also be interchanged:

• FOR/AFTER - The trigger is fired after all constraints have been passed once the data has been added to the database. This is the default trigger type. FOR was used pre-SQL Server 2000 and AFTER was introduced later, however regardless on which one you use they both do the same thing. 
• INSTEAD OF - The trigger is fired as an alternative to an INSERT, UPDATE or DELETE statement. This clause is most commonly used on Views because you cannot insert into them easily (as you can only modify tables and columns with no underlying data attached. The trigger can be used to pick out the individual up-datable parts and put them into the correct underlying sub-tables. 

On occasions, you may find that nothing has actually occurred within the trigger. So for performance reasons, just escape out of it to prevent that content from running unnecessarily using the following code (however, note that it must always be within the first line of the body of your trigger!):

DDL Triggers 

DDL stands for Database Definition Language and is the syntax that is responsible for controlling the database schema and structure. DDL Triggers are fired when either a DDL event occurs (such as creating new databases or modifying permissions to a database) or when a user logs onto the SQL Server Instance.  Here is a short list of example DDL Trigger Events, though note that there are more (thanks to Microsoft for providing the list in their book: Microsoft SQL Server 2008 - Database Development MCTS Exam 70-433 (ISBN: 978-0-7356-2639-3)):

• CREATE_DATABASE
• GRANT_DATABASE
• REVOKE_DATABASE
• DENY_DATABASE
• GRANT_SERVER
• REVOKE_SERVER
• DENY_SERVER
• DROP_LOGIN 
• UPDATE_STATISTICS 
• DROP_TRIGGER
• ALTER_TABLE

DDL Triggers contain scope clauses, which allow you to define exactly in what instance that the trigger should fire:

• ON ALL SERVER - is triggered on all database instances on that SQL Server.
• ON DATABASE - is triggered only on that database.

In order to retrieve information within a DDL trigger, you will need to use the EVENTDATA function. This returns XML, which can be queried separately (there will be a future blog post to explain how to do that!).

LOGON Triggers

LOGON Triggers are only fired when a user logs onto the SQL Server Instance; after the authentication of the user but before the user session loads. LOGON Triggers are used when restricting access, so typically when trying to restrict the number of people attempting to log onto that particular SQL Server Instance at any one time. This type of trigger is more commonly only used by Database Maintenance staff and administrators. For more information on these, follow this link:

http://msdn.microsoft.com/en-us/library/bb326598.aspx

Concluding Note

I hope you have found this blog post (and my other posts!) useful. If you have any feedback or questions, please do not hesitate to leave a comment below and I will try to reply to you as quickly as I can. Thanks for reading!