Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



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