Triggers, when to use them, when not to use them

It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info.

 Today we are going to look at triggers. Triggers are a great way to keep your database in a consistent state. There are two types of triggers, DML triggers and DLL triggers. DML triggers respond to Data Manipulation Statements (Insert, Delete, Update) DDL triggers respond to Data Definition Language events.

Some things that DML triggers are used for:

• Keeps your databases from having wrong data by doing checks that can't be handled with constraints
• Filling in values that are not supplied and can't be handled through default constraints since these don't fire on updates
• Calculation summary values and updates the summary table with that value
• Used as a mechanism to maintain an audit trail for DML statements

Some things that DDL triggers are used for:

• Automatically add columns to a table if they were not added, for example LastUpdated and InsertedBy columns
• Notify a DBA when a database has been created, dropped or altered
• Used as a mechanism to maintain an audit trail for DDL statements, capture every time an object has been created, dropped or altered and by who

Most common mistake people make when first starting writing triggers is that they write it in such a way that it will only work if you insert/update/delete one row at a time. A trigger fires per batch not per row, you have to take this into consideration otherwise your DML statements will blow up. How to do this is explained in this post Coding SQL Server triggers for multi-row operations, there is no point recreating that post here.

Another problem that I see is that some people think a trigger is SQL Server's version of crontab, you will see code that sends email, kicks off jobs, runs stored procedures. This is the wrong approach, a trigger should be lean and mean, it should execute as fast as possible, if you need to do some additional things then dump some data from the trigger into a processing table and then use that table to do your additional tasks. Don't use triggers as a messaging system either, SQL Server comes with Service Broker, use that instead. 

Triggers might look like hammers to some people but I guarantee you not everything is a nail....

You could end up with a real difficult thing to debug, one trigger that kicks off other triggers, now have fun debugging the trigger hell you got yourself into....or worse debug this mess if you inherited this....this is like the GOTO spaghetti code of databases.

Since triggers work besides the scenes you might spend hours debugging something only to find out that a trigger modified the value

One thing I always find interesting is when someone sees two n rows affected statements when they only did one insert, you know a person like that has not been exposed to triggers yet

Some people will say that you don't need triggers for anything and that they do more harm than good, I myself don't agree with that, triggers have a place but they should not be abused and overused, the same can be said of views

Fun With SQL Server Update Triggers

Below is some code that will show how to test for updated field values in an update trigger. As you can see the IF UPDATE (field) is true even when the values don’t change. Another thing to keep in mind is that if a value changes from NULL to something else and vice-versa, and you are comparing deleted and inserted tables without using COALESCE or ISNULL it won’t return those rows. Run the code below to see what I mean


CREATE TABLE TestTrigger (TestID INT identity,
name VARCHAR(20),
value DECIMAL(12,2) ,
CONSTRAINT chkPositiveValue CHECK (value > 0.00) )


INSERT INTO TestTrigger
SELECT 'SQL',500.23


CREATE TRIGGER trTest
ON TestTrigger
FOR UPDATE
AS

IF @@ROWCOUNT =0
RETURN

IF UPDATE(value)
BEGIN
SELECT '1', * FROM deleted d JOIN inserted i ON d.testid =i.testid
SELECT '2',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND i.value <> d.value
SELECT '3',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND COALESCE(i.value,-1) <> COALESCE(d.value,-1)
END
GO

--Let's update the value to 100
UPDATE TestTrigger SET value = 100 WHERE testid =1
--we get back all 3 rows


--Let's run the same statement
UPDATE TestTrigger SET value = 100 WHERE testid =1
--we get back the first row


--Let's really update
UPDATE TestTrigger SET value = 200 WHERE testid =1
--we get back all 3 rows

--Let's update with NULL
UPDATE TestTrigger SET value =NULL WHERE testid =1
--we get back rows 1 and 3, row 2 is not returned because it can't compare it

--Let's update with NULL again
UPDATE TestTrigger SET value =NULL WHERE testid =1
--we get back row 1

--Let's update with 300
UPDATE TestTrigger SET value =300 WHERE testid =1
--we get back rows 1 and 3, row 2 doesn't return because it can't compare NULL to 300

--Let's update with 500
UPDATE TestTrigger SET value =500 WHERE testid =1
--we get back all 3 rows