Skip to content

Triggers Overview

2008 August 25
by Shannon Lowder

OK, What do you know about triggers?

Did you know you can use them to run a bit of T-SQL on data changes? Those would be called Data Manipulation Language (DML) Triggers. You can set those up for tables so that a certain action occurs with each INSERT, UPDATE, or DELETE. You can build as many of these for the same table as you want, so long as you give each one a different name.

Did you know you can use them to run a bit of code each time a change is made to the database? These are Data Definition Language (DDL) triggers. You can create triggers that fire for CREATE, ALTER, and DROP statements. These can be very powerful, but can also be very dangerous. We’ll dive more into them later.

Both types of triggers are created using similar syntax.

CREATE TRIGGER t_object_action
ON object
FOR action
	T-SQL statement(s)

Both DML and DDL triggers can be managed code, packaged in a Common Language Runtime (CLR) trigger. This can be useful when you’re trying to create a trigger to do something T-SQL is generally poor at, such as text manipulation.

DDL triggers can only run after their associated action has run, there is no such thing as a DDL trigger that is an INSTEAD OF trigger.

DDL triggers do not create the inserted or deleted tables. Any information about the events that trigger a DDL trigger are captured using the EVENTDATA function.

That’s it for our overview. Stay tuned for more articles on triggers. They can be incredibly useful, if they are properly implemented and used for their intended purpose. If you have any questions, please send them in.

One Response leave one →

Trackbacks and Pingbacks

  1. Microsoft Exam 70-433 (part 2) | Shannon Lowder

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS