Triggers Overview

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
AS
	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.

Tags:

Trackbacks/Pingbacks

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

    […] and alter DML triggers. This objective may include but is not limited to: INSERTED, DELETED, INSTEAD OF, EXECUTE […]

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.