Sunday, 21 August 2011

TRIGGER

The SQL CREATE TRIGGER statement provides a way for the database management system to actively control, monitor, and manage a group of tables whenever an insert, update, or delete operation is performed. The statements specified in the SQL trigger are executed each time an SQL insert, update, or delete operation is performed. An SQL trigger may call stored procedures or user-defined functions to perform additional processing when the trigger is executed.

Unlike stored procedures, an SQL trigger cannot be directly called from an application. Instead, an SQL trigger is invoked by the database management system on the execution of a triggering insert, update, or delete operation. The definition of the SQL trigger is stored in the database management system and is invoked by the database management system, when the SQL table, that the trigger is defined on, is modified.

Advantages of using SQL trigger

  • SQL Trigger provides an alternative way to check integrity.
  • SQL trigger can catch the errors in business logic in the database level.
  • SQL trigger provides an alternative way to run scheduled tasks. With SQL trigger, you don’t have to wait to run the scheduled tasks. You can handle those tasks before or after changes being made to database tables.
  • SQL trigger is very useful when you use it to audit the changes of data in a database table.

Disadvantages of using SQL trigger

  • SQL trigger only can provide extended validation and cannot replace all the validations. Some simple validations can be done in the application level.  For example, you can validate input check in the client side by using javascript or in the server side by server script using PHP or ASP.NET.
  • SQL Triggers executes invisibly from client-application which connects to the database server so it is difficult to figure out what happen underlying database layer.
  • SQL Triggers run every updates made to the table therefore it adds workload to the database and cause system runs slower.
 Creation of Triggers 

Triggers are created with the CREATE TRIGGER statement. This statement specifies that the on which table trigger is defined and on which events trigger will be invoked.

To drop Trigger one can use DROP TRIGGER statement.

Code: SQL
CREATE TRIGGER [owner.]trigger_name ON[owner.] table_name FOR[INSERT/UPDATE/DELETE] AS IF UPDATE(column_name) [{AND/OR} UPDATE(COLUMN_NAME)...] { sql_statements }

Trigger rules and guidelines

A table can have only three triggers action per table : UPDATE ,INSERT,DELETE. Only table owners can create and drop triggers for the table.This permission cannot be transferred.A trigger cannot be created on a view or a temporary table but triggers can reference them. A trigger should not include SELECT statements that return results to the user, because the returned results would have to be written into every application in which modifications to the trigger table are allowed. They can be used to help ensure the relational integrity of database.On dropping a table all triggers associated to the triggers are automatically dropped .

The system stored procedure sp_depends can be used to find out which tables have trigger on them. Following sql statements are not allowed in a trigger they are:-
Code: SQL
ALL CREATE statements,ALL DROP statements,ALTER TABLE AND ALTER DATABASE,TRUNCATE TABLE,GRANT AND REVOKE,UPDATE STATISTICS,RECONFIGURE,LOAD DATABASE AND LOAD TRANSACTION, ALL DISK statements,SELECT INTO

INSERT trigger

When an INSERT trigger statement is executed ,new rows are added to the trigger table and to the inserted table at the same time. The inserted table is a logical table that holds a copy of rows that have been inserted. The inserted table can be examined by the trigger ,to determine whether or how the trigger action are carried out.

The inserted table allows to compare the INSERTED rows in the table to the rows in the inserted table.The inserted table are always duplicates of one or more rows in the trigger table.With the inserted table ,inserted data can be referenced without having to store the information to the variables.

DELETE trigger

When a DELETE trigger statement is executed ,rows are deleted from the table and are placed in a special table called deleted table.

UPDATE trigger

When an UPDATE statement is executed on a table that has an UPDATE trigger,the original rows are moved into deleted table,While the update row is inserted into inserted table and the table is being updated.

Syntax
Code: SQL
IF UPDATE<column_name> CREATE TRIGGER trigger1 ON member FOR UPDATE AS IF UPDATE(last_name) BEGIN RAISEERROR('Transaction cannot be processed') ROLLBACK TRANSACTION END

Multi-row trigger

A multi-row insert can occur from an INSERT with a SELECT statement.Multirow considerations can also apply to multi-row updates and multi-row deletes.
Code: SQL
CREATE TRIGGER adult_insert ON adult FOR INSERT AS DECLARE @rcnt int SELECT @rcnt = @@rowcount IF(SELECT COUNT(*) FROM member,inserted WHERE member.mem_num = inserted.mem_num)=0 BEGIN PRINT 'Transaction not processed' PRINT 'No entry for this member' ROLLBACK TRANSACTION END IF(SELECT COUNT(*) FROM member,inserted WHERE member.mem_num = inserted.mem_num)<> @rcnt BEGIN PRINT 'Not all adults have an entry in the member table' PRINT 'Multi-row insert transaction has been rolled backed' ROLLBACK TRANSACTION END