Triggers

Triggers are one of the most misunderstood features in Microsoft SQL server. Most of that misunderstanding comes from people implementing triggers with little understanding to their impact. Even worse, they’ll implement them without testing. And the worst of all, not using them, when the functionality being requested is the very definition of what a trigger is and does.

So what is a trigger? Do you know what an event is in Visual Basic? A trigger is a notification that a certain event has happened. When this notification is made, you can assign business logic to respond to this condition.

The problem that some perceive is if you do anything when an event is fired, it makes the database slow.

That’s simply not true.

How do I know? Because, every activity you do in Microsoft SQL is logged. That in and of itself is a trigger. If the same people who say adding triggers makes the database slow, ever consider their server speedy, then they would have to admit triggers don’t slow down the server.

Bad code does.

But we’re going to leave that for another time. Optimizing code is a life-long study. It’s not something I can cover in a single post. Let alone this post, where I plan to introduce you to triggers.

For this post, I’m going to show you how to create a data modification trigger. A data modification trigger is one that happens when you make a change to some data (an insert, an update, or a delete). Creating one of these triggers is dead simple… It’s like creating a stored procedure.

CREATE TRIGGER triggerName ON tableName <FOR|AFTER|INSTEAD OF> <INSERT|UPDATE|DELETE>
AS
BEGIN
  ..do something useful..
END

That’s it. That’s all there is to creating a trigger. The FOR, AFTER, and INSTEAD OF are options for when to actually do the body of the trigger.

FOR and AFTER indicate the trigger is fired only when all the operations specified in the triggering SQL statement have executed successfully (meaning if there is an error, the trigger will rollback too). INSTEAD OF will actually replace the operation indicated (INSERT, UPDATE, or DELETE).

Just like everything else in SQL you can also ALTER or DROP the procedure if needed.

ALTER TRIGGER triggerName ON tableName <FOR|AFTER|INSTEAD OF> <INSERT|UPDATE|DELETE>
AS
BEGIN
  .. do something useful..
END
GO
DROP TRIGGER triggerName

In addition to DML (data manipulation language) triggers, you can create Data Definition Language triggers, these will fire when you alter the database (such as creating a table or view). I’ll cover these in a later post. For now, you have the basics…Learn to master this, and then we can cover DDL triggers.

If you have any questions, send them in, I’m here to help!

Tags: , , , ,

No comments yet.

Leave a Reply

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