dcsimg
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Avoid These Common Business Rule Implementation Mistakes : Page 2

Some solutions for implementing business rules can allow invalid data to get into the database. Find out where the loopholes lie in these all-too-common approaches.


advertisement

Sometimes Triggers Just Do Not Fire

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 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.



Thanks for your registration, follow us on our social networks to keep up-to-date