Document Sample
Triggers Powered By Docstoc

A trigger is a special type of stored procedure that automatically takes effect when
the data in a specified table is modified. Triggers are invoked in response to an
INSERT, UPDATE, or DELETE statement. A trigger can query other tables and can
include complex Transact-SQL statements. The trigger and the statement that fires it
are treated as a single transaction, which can be rolled back from within the trigger.
If a severe error is detected (for example, insufficient disk space), the entire
transaction automatically rolls back.

Triggers are useful in these ways:

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

      Triggers can enforce restrictions that are more complex than those defined
       with CHECK constraints.

       Unlike CHECK constraints, triggers can reference columns in other tables. For
       example, a trigger can use a SELECT from another table to compare to the
       inserted or updated data and to perform additional actions, such as modify
       the data or display a user-defined error message.

      Triggers can also evaluate the state of a table before and after a data
       modification and take action(s) based on that difference.

      Multiple triggers of the same type (INSERT, UPDATE, or DELETE) on a table
       allow multiple, different actions to take place in response to the same
       modification statement.

Creating a Trigger:

 CREATE TRIGGER trigger_name
 ON { table | view }
   { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
            sql_statement [ ...n ]


You can use the FOR clause to specify when a trigger is executed:

      AFTER

       The trigger executes after the statement that triggered it completes. If the
       statement fails with an error, such as a constraint violation or syntax error,
       the trigger is not executed. AFTER triggers cannot be specified for views, they
       can only be specified for tables. You can specify multiple AFTER triggers for
       each triggering action (INSERT, UPDATE, or DELETE). If you have multiple
       AFTER triggers for a table, you can use sp_settriggerorder to define which
       AFTER trigger fires first and which fires last. All other AFTER triggers besides
       the first and last fire in an undefined order which you cannot control.

       AFTER is the default in SQL Server 2000. You could not specify AFTER or
       INSTEAD OF in SQL Server version 7.0 or earlier, all triggers in those versions
       operated as AFTER triggers.


       The trigger executes in place of the triggering action. INSTEAD OF triggers
       can be specified on both tables and views. You can define only one INSTEAD
       OF trigger for each triggering action (INSERT, UPDATE, and DELETE).
       INSTEAD OF triggers can be used to perform enhance integrity checks on the
       data values supplied in INSERT and UPDATE statements. INSTEAD OF triggers
       also let you specify actions that allow views, which would normally not
       support updates, to be updatable.

Triggers Compared to Constraints

Constraints and triggers each have benefits that make them useful in special
situations. The primary benefit of triggers is that they can contain complex
processing logic that uses Transact-SQL code. Therefore, triggers can support all of
the functionality of constraints; 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 constraint 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 errors only through standardized system
       error messages. If your application requires (or can benefit from) 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 canceling 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 execution. If the
       constraints are violated, the INSTEAD OF trigger actions are rolled back and
       the AFTER trigger is not executed.

       While entering the details of a 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 ‘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 follows:

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 inserting a record with a null value for the field bsal.
When a select statement is given, the value for bsal will also be present.

 INSERT INTO emp VALUES(100,’Arvind’,30,’B’,null,getdate())


 Emp_no        emp_name       dept_no       grade bsal doj
 100           Arvind         30            B     2000 2006-06-15

Altering a Trigger:

The definition of an existing trigger can be altered without dropping it. The
altered definition replaces the definition of the existing trigger with the new


 ALTER TRIGGER trigger_name
 ON { table | view }
   { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
            sql_statement [ ...n ]



       This example creates a trigger that prints a user-defined message to the
client when a user tries to add or change data in the emp table. Then, the
trigger is altered using ALTER TRIGGER to apply the trigger only on INSERT
activates. This trigger is helpful because it reminds the user who updates or
inserts rows into this table.

 ON emp
 AS RAISERROR(40008,16,10)

 ON emp
 AS RAISERROR(40008,16,10)

Dropping a Trigger:

A trigger can be dropped by using, DROP TRIGGER statement. A trigger gets
dropped automatically when its associated table is dropped. Permissions to drop a
trigger defaults to the table owner. But the members of the system
administrators(sysadmin) and the database owner(db_owner) can drop any object
by specifying the owner in the DROP TRIGGER statement.


 DROP TRIGGER trigger_name

Disabling or Enabling a Trigger:

        Triggers can be enabled or disabled using ALTER TABLE statement. On
disabling a trigger, the actions in the trigger are not performed until the trigger is re-


 ALTER TABLE table_name
 {ALL | trigger_name[, --n]


Shared By:
Description: this sql triggers document is useful for database developers and database testers