SQL Server : Triggers Compared to Constraints

Before you start this article I strongly recommend you to read my previous article which I explained about Triggers and its Types: for more info please go here SQL Server : Triggers

Triggers Compared to Constraints

Constraints and triggers each have benefits that make them useful in special situations. The primary benefits of triggers is that they can contain complex processing logic that user Transact-SQL code. Therefore, triggers can support all of the functionality of constrains; however, triggers are not always the best method for a given feature.

Entity integrity should always be enforced at the lowest level by indexes that are part of PRIMARY KEY and UNIQUE constraints or are created independently of constraints. Domain integrity should be enforced through CHECK constraints, and referential integrity (RI) should be enforced through FOREIGN KEY constraints, assuming their features meet the functional needs of the application.

Triggers are most useful when the features supported by constraints cannot meet the functional needs of the application. For Example :

• FOREIGN KEY constraints can validate a column value only with an exact match to a value in another column, unless the REFERENCES clause defines a cascading referential action.
• A CHECK constraints can validate a column value only against a logical expression or another column in the same table. If your application requires that a column value be validated against a column in another table, you must use a trigger.
• Constraints can communicate about error only through standardized system error messages. If you application requires customized messages and more complex error handling, you must use a trigger.

Triggers can cascade changes through related tables in the database; however, these changes can be executed more efficiently through cascading referential integrity constraints.

• Triggers can disallow or roll back changes that violate referential integrity, thereby cancelling the attempted data modification. Such a trigger might go into effect when you change a foreign key and the new value does not match its primary key.
• If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution but prior to the AFTER trigger exectuion. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed.

Example :

While entering the details of new employee, the salary has to be entered according to the grade. If the grade it changed, the basic salary should also be changed accordingly. Instead of manually doing this, the HR manager wanted the basic salary of the employees to be entered automatically whenever an employee is added or grade is changed.

Solution : Create sal_info and emp tables as follows

 CREATE TABLE sal_info (grade char(1), bsal numeric(18,0))

Insert some values into the sal_info table:

 INSERT into sal_info
  SELECT 'A', 1000 Union All
  SELECT 'B', 2000 Union All
  SELECT 'C', 3000

Now, create the emp table as follows:

 CREATE TABLE emp(emp_no int, emp_name varchar(10), dept_no int, grade char(1), bsal numeric(18,0), doj datetime)

Now, Create the required trigger as follow :

 CREATE TRIGGER tr_emp On emp 
 For insert, update
  AS
  Declare @sal numeric(18,0)
  SELECT @sal = sal_info.bsal from sal_info, inserted where inserted.grade = sal_info.grade
  Update emp set bsal = @sal from emp.inserted where emp.emp_no = inserted.emp_no

An insert/update is written for the table emp. Whenever a new record is inserted or updated, the new grade is obtained from the inserted table. The corresponding basic salary is obtained from the table sal_info and the basic salary in the emp table is set to this value.

This trigger can be checked by inserted a record with a null value for the field bsal. when a selet statement is givem, the value for bsal will also be present.

 INSERT into emp Values(100, 'Arvind', 30, 'B', null, getdata())

 SELECT * from emp