Because triggers are very powerful and flexible, many developers do not hesitate to use them whenever they need to implement a business rule. However, triggers have the two following problems:
- When you create a trigger, the existing data is not validated. Somebody can drop a trigger, add some invalid data, and recreate the trigger. As a result, you will have incorrect data and won't know it.
- In some cases, triggers just do not fire. Both the server-level setting 'nested triggers' and the database-level setting
recursive_triggers
may prevent a trigger from firing. For example, the following simple trigger, although not a complete solution to the problem (see the comments in the body), illustrates the point:
create trigger manager_must_be_employee
on employee
after insert, update
-- this trigger does not handle deletes, just to keep things simple
-- a complete solution should prevent from deleting of a manager with employee
as
declare @contractor_has_employees int,
@manager_is_contractor int
if update(status)
begin
select @manager_is_contractor = count(*)
from inserted, employee
where employee.status = 'Contractor' and employee.employee_id = inserted.manager_id
if @manager_is_contractor > 0
begin
raiserror('Cannot insert anyone reporting to a Contractor', 16, 1)
rollback tran
return
end
select @contractor_has_employees = count(*)
from inserted, employee
where inserted.status = 'Contractor' and inserted.employee_id = employee.manager_id
if @contractor_has_employees > 0
begin
raiserror('Cannot set manager''s status to Contractor', 16, 1)
rollback tran
return
end
end
Although the following simple trigger does not cover all the bases, it does prevent some errors:
update employee set status = 'Contractor' where employee_id = 3
Server: Msg 50000, Level 16, State 1, Procedure manager_must_be_employee, Line 32
Cannot set manager's status to Contractor
Because Jill Larsen reports to Sam Brown, Sam cannot be a contractor. If this trigger fires, it correctly raises an error and rolls backif. Suppose you have 'nested triggers' set to 1 on your server, and another trigger may modify an employee's status. Your trigger still protects your data:
create table contract(employee_id int, amount float)
go
create trigger only_contractors_have_contracts
on contract
after insert
as
update employee set status='Contractor'
from employee, inserted
where inserted.employee_id = employee.employee_id
go
insert into contract values(3, 199.00)
go
Server: Msg 50000, Level 16, State 1, Procedure manager_must_be_employee, Line 34
Cannot set manager's status to Contractor
Say the application has been fully tested and deployed, and there is no invalid data in the employee table. However, if the DBA sets 'nested triggers' to 0, the same insert statement, insert into contract values(3, 199.00)
, will fire the trigger only_contractors_have_contracts
, which will modify the employee table. However, because 'nested triggers' is set to 0, the manager_must_be_employee
trigger will not fire and the statement will succeed.
Now, suppose you have invalid data in the employee table and you do not know about it. Because Jill Larsen reports to Sam Brown, Sam cannot be a contractor. But he is:
select * from employee
employee_id first_name last_name manager_id status
----------- ---------- ---------- ----------- ----------
1 Jane Wilson NULL Employee
2 Sue Smith 1 Contractor
3 Sam Brown 1 Contractor
4 Jill Larsen 3 Employee
(4 row(s) affected)
As you have seen, sometimes triggers do not fire. Make sure that the 'nested triggers' and recursive_triggers
settings in your test and production environments match.