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.

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

1 comment

Leave a comment

Your email address will not be published. Required fields are marked *