In my Previous Articles i explained about
Triggerfor more info just go through this link - 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 KEYconstraints can validate a column value only with an exact match to a value in another column, unless the
REFERENCESclause defines a cascading referential action.
CHECKconstraints 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.
Triggerscan 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 OFtrigger execution but prior to the
AFTERtrigger exectuion. If the constraints are violated, the
INSTEAD OFtrigger actions are rolled back and the
AFTERtrigger is not executed.
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.
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', 3000Now, 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