SQL SERVER – TRIGGERS

• Microsoft SQL Server 2005 provides two primary mechanisms for enforcing business rules and data integrity: constraints and triggers.
• A trigger is a special type of stored procedure that automatically takes effect when a language event executes.
• SQL Server includes two general types of triggers: DML triggers and DDL triggers.
• DDL triggers are new to SQL Server 2005. These triggers are invoked when a data definition language (DDL) event takes place in the server or database.
• DML triggers are invoked when a data manipulation language (DML) event takes place in the database. DML events include INSERT, UPDATE, or DELETE statements that modify data in a specified table or view.
• A DML 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.

DML triggers are useful in these ways:

• They can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential integrity constraints.
• They can guard against malicious or incorrect INSERT, UPDATE, and DELETE operations and enforce other restrictions that are more complex than those defined with CHECK constraints.
• Unlike CHECK constraints, DML 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.
• They can evaluate the state of a table before and after a data modification and take actions based on that difference.
• Multiple DML 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.

Types of DML Triggers:

AFTER Triggers: AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of Microsoft SQL Server. AFTER triggers can be specified only on tables.

INSTEAD OF Triggers: INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. DDL triggers execute in response to a variety of data definition language (DDL) events. These are primarily CREATE, ALTER, and DROP statements. DML and DDL triggers can be created in the SQL Server 2005 Database Engine directly from Transact-SQL. SQL Server allows for creating multiple triggers for any specific statement.

Syntax: CREATE TRIGGER trigger_name

ON  table | view

[ WITH ENCRYPTION ]

FOR | AFTER | INSTEAD OF

[ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ]

AS

BEGIN

sql_statements

END

table | view: Is the table or view on which the DML trigger is executed and is sometimes referred to as the trigger table or trigger view. A view can be referenced only by an INSTEAD OF trigger.

WITH ENCRYPTION: Encrypts the text of the CREATE TRIGGER statement.

AFTER: Specifies that the DML trigger be fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires. AFTER is the default when FOR is the only keyword specified. AFTER triggers cannot be defined on views.

INSTEAD OF: Specifies that the DML trigger be executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. INSTEAD OF triggers are not allowed on updatable views that use WITH CHECK OPTION. SQL Server raises an error when an INSTEAD OF trigger is added to an updatable view WITH CHECK OPTION specified. The user must remove that option by using ALTER VIEW before defining the INSTEAD OF trigger.

[ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ]: Specifies the data modification statements that activate the DML trigger when it is tried against this table or view. At least one option must be specified. Any combination of these options in any order is allowed in the trigger definition. For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE. Similarly, the UPDATE option is not allowed on tables that have a referential relationship specifying a cascade action ON UPDATE.

-A Trigger that will restrict the operations to be performed before 9 A.M and after 5 P.M

CREATE TRIGGER EMP_TRG

ON EMP AFTER INSERT, UPDATE, DELETE

AS

BEGIN

DECLARE @DT INT

SET @DT=DATENAME(HH, GETDATE())

IF @DT NOT BETWEEN 9 AND 16

BEGIN

ROLLBACK

RAISERROR('CANNOT PERFORM DML OPERATIONS NOW', 15, 1)

END

END

-After the trigger is created try to perform any DML Operations on the EMP table before 9 A.M and after 5 P.M the Trigger will fire and restrict the operations.

• When we try to perform any DML Operation on a table when a trigger is present on it the values of the DML statement will be captured in the trigger inside 2 Magic Tables Inserted and Deleted that have the same structure of the table.
• The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.
• The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added at the same time to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.
• An update transaction is similar to a delete operation followed by an insert operation; the old rows are copied to the deleted table first, and then the new rows are copied to the trigger table and to the inserted table.

-A trigger that will convert the DName and Loc into upper case when the user insert in lower case.

CREATE TRIGGER DEPT_CONVERT_TRG

ON DEPT AFTER INSERT

AS

BEGIN

DECLARE @DEPTNO INT

DECLARE @DNAME VARCHAR(50)

DECLARE @LOC VARCHAR(50)

SELECT @DEPTNO=DEPTNO, DNAME=@DNAME, @LOC=LOC FROM INSERTED

UPDATE DEPT SET DNAME=UPPER(@DNAME), LOC=UPPER(@LOC) WHERE DEPTNO=@DEPTNO

END

-To test the trigger execute the following statement which will convert the data into upper case in the table:

INSERT INTO DEPT VALUES(50, 'research' ,'hyderabad')

-The above trigger can be written in the following way also:

CREATE TRIGGER DEPT_CONVERT_TRG2

ON DEPT INSTEAD OF INSERT

AS

BEGIN

INSERT INTO DEPT

SELECT DEPTNO, UPPER(DNAME), UPPER(LOC) FROM INSERTED

END

-A Trigger which will generate a unique Deptno when the user inserts a record into the dept table only by specifying DName and Loc when a primary key constraint is present on the Deptno column.

CREATE TRIGGER DEPT_GENERATE_TRG

ON DEPT INSTEAD OF INSERT

AS

BEGIN

DECLARE @DEPTNO INT

SELECT @DEPTNO=DEPTNO FROM INSERTED

IF @DEPTNO IS NULL

SELECT @DEPTNO=ISNULL(MAX(DEPTNO), 0) + 10 FROM DEPT

INSERT INTO DEPT SELECT @DEPTNO, DNAME, LOC FROM INSERTED

END

-To test the following Trigger execute the following statement:

INSERT INTO DEPT(DNAME, LOC) VALUES('RESEARCH', 'HYDERABAD')

-A program which will restrict the Delete operation if the Job of the person is Manager.

ALTER TRIGGER EMP_DELETE_TRG

ON EMP AFTER DELETE

AS

BEGIN

DECLARE @JOB VARCHAR(50)

SELECT @JOB=JOB FROM DELETED

IF @JOB='MANAGER'

BEGIN

ROLLBACK

RAISERROR('CANNOT DELETE MANAGER FROM THE TABLE', 15, 1)

END

END

-To test the following Trigger execute the following statement:

DELETE FROM EMP WHERE EMPNO=1002

A Trigger which will restrict to update the Salary of the Employee if the New Salary is less than the Old Salary.

CREATE TRIGGER EMP_UPDATE_TRG

ON EMP AFTER UPDATE

AS

BEGIN

DECLARE @OLDSAL MONEY

DECLARE @NEWSAL MONEY

SELECT @OLDSAL=SAL FROM DELETED

SELECT @NEWSAL=SAL FROM INSERTED

IF @OLDSAL > @NEWSAL

BEGIN

ROLLBACK

RAISERROR('NEW SAL CANNOT BE LESS THAN OLD SAL', 15, 1)

END

END

NESTED TRIGGERS: Triggers can be nested to a maximum of 32 levels. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call a third trigger, and so on. To disable nested triggers, set the nested triggers option of sp_configure to 0 (off). The default configuration allows for nested triggers.

-A Trigger which will fire when a record is inserted into the Emp table which verifies whether the given Deptno is present in the Dept table or not if not it will insert a record into it.

CREATE TRIGGER EMP_NESTED_TRG

ON EMP AFTER INSERT

AS

BEGIN

DECLARE @DEPTNO INT

SELECT @DEPTNO=DEPTNO FROM INSERTED

IF NOT EXISTS(SELECT * FROM DEPT WHERE DEPTNO=@DEPTNO)

INSERT INTO DEPT VALUES(@DEPTNO, NULL, NULL)

END

-A Trigger which will fire when a record is inserted into the Dept table which verifies whether the given Deptno is present in the DeptDetails table or not if not it will insert a record into it.

CREATE TRIGGER DEPT_NESTED_TRG

ON DEPT AFTER INSERT

AS

BEGIN

DECLARE @DEPTNO INT

DECLARE @DID INT

SELECT @DEPTNO=DEPTNO FROM INSERTED

IF NOT EXISTS(SELECT * FROM DEPTDETAILS WHERE DEPTNO=@DEPTNO)

BEGIN

SELECT @DID=MAX(DID)+ 1 FROM DEPTDETAILS

INSERT INTO DEPTDETAILS VALUES(@DID, @DEPTNO, NULL)

END

END

-After creating the 2 Triggers if we try to insert a record into the Emp table with a Deptno not present in the Dept table it will insert a record into the Dept table which will internally check whether the Deptno is present in the DeptDetails table or not and inserts a records into it if not present.

Instead of Triggers on Complex Views which are not updatable:

CREATE VIEW EMP_DEPT

AS

SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC FROM EMP E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO

-After creating the view try to execute the following insert which will fail because complex views are by default non updatable:

INSERT INTO EMP_DEPT VALUES(1100, 'RAJU', 4500, 50, 'IT', 'BANGLORE')

-If the above statement has to execute we need to define a Instead of trigger on the view so that the View become updatable.

CREATE TRIGGER VIEW_INSERT_TRG

ON EMP_DEPT INSTEAD OF INSERT

AS

BEGIN

INSERT INTO EMP(EMPNO, ENAME, SAL, DEPTNO)

SELECT EMPNO, ENAME, SAL, DEPTNO FROM INSERTED

INSERT INTO DEPT(DEPTNO, DNAME, LOC)

SELECT DEPTNO, DNAME, LOC FROM INSERTED

END

-In the same way a trigger which will allow delete operations to be performed on the View:

CREATE TRIGGER VIEW_DELETE_TRG

ON EMP_DEPT INSTEAD OF DELETE

AS

BEGIN

DECLARE @DEPTNO INT

DECLARE @COUNT INT

SELECT @DEPTNO=DEPTNO FROM DELETED

SELECT @COUNT=COUNT(*) FROM EMP WHERE DEPTNO=@DEPTNO

DELETE FROM EMP WHERE DEPTNO=@DEPTNO

IF @COUNT=1

DELETE FROM DEPT WHERE DEPTNO=@DEPTNO

END