Avoid These Common Business Rule Implementation Mistakes : Page 2

• 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 back—if. 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.