21 - SQL Server Triggers

21.1 Implementing Triggers

Data manipulation is necessary with many operations in SQL server. Several database objects require manipulation of data. The trigger allows user to implement the data manipulation. A trigger is a set of SQL statements that are activated in response to certain actions.

The data integrity is ensured with triggers in SQL server for data manipulation. A trigger is a stored procedure for executing in response to certain events.

Types of Triggers

In SQL Server, there are several types data manipulation operations. The following trigger types are used in SQL Server.

1) Data Manipulation Language ( DML )

2) Data Definition Language ( DDL )

1) DML Triggers

A DML trigger is used when the tables are affected by the DML statements, as INSERT, UPDATE and DELETE. They help user in maintaining the consistent, reliable, and proper data in the tables.

The characteristics of the DML trigger are as shown below:

1) They do not return any data to the user

2) The incorrect, inconsistent and unauthorized changes are restricted by the user

3) They cannot be explicitly invoked or executed

4) They are fired automatically when the data modifications statement is executed

5) The nesting up to 32 levels is allowed in the triggers

User fires a trigger in response to the INSERT, DELETE and UPDATE statements. Two temporary tables are created which are known as magic tables. They are known as inserted or deleted. The structure of these tables is similar to the database tables.

The DML triggers are further distinguished as

1) INSERT trigger

2) DELETE trigger

3) UPDATE trigger

1) INSERT trigger

The trigger is fired when the attempt is made to insert a row in the trigger table. After the INSERT statement is executed the new row is added to the Inserted table.

2) DELETE trigger

It is fired when the attempt is made to delete the row from the trigger table. The deleted rows are added to the Deleted table. There are no common rows between deleted and database table.

The DELETE trigger is implemented in three ways as mentioned below:

a) The cascade method: It is used to delete the corresponding records from the dependent table when the record is deleted from the master table

b) The restrict method: It restricts the deletion of records from the master table if the related records are present in the dependent table.

c) The nullify method: It is used to nullify the values in the specified columns of the dependent tables whenever a record is deleted from the master table.

3) UPDATE trigger

It is fired when the UPDATE statement is executed in the trigger table. Two logical tables are used for the operations performed by the trigger. The Deleted table contains the original rows and the Inserted table contains the new rows.

2) DDL Triggers

The DDL triggers are fired in response to the DDL statements as CREATE TABLE and ALTER TABLE. The task as database auditing is performed by the DDL trigger. DDL operations can contain creation of table or view, modification of a table or procedure.

Nested Triggers

Nested triggers are fired due to the actions of other triggers. DML and DDL triggers can be nested while performing an action of initiating another trigger. Consider an example of DELETE trigger on the Department table deleted the corresponding records of employee table and the DELETE trigger on the Employee table inserts those deleted employee records in the EmpHistory table.

Recursive Triggers

Recursive triggers are special type of nested triggers. The recursive trigger can call itself. The types of recursive trigger are as follows:

a) Direct

b) Indirect

a) Direct recursive trigger

When a trigger is fired and actions are performed causing the same trigger to fire again. The trigger is known as direct recursive trigger.

The following diagram shows the execution of the direct recursive trigger.

              

Indirect recursive trigger

An indirect recursive trigger fires a trigger on another table and the nested trigger ends up firing the first trigger again. Consider an example, table A fires a trigger, it fires an update on table B. The table B fires another trigger on table C. Table C causes another trigger on table A again. The update trigger is fired on table A again.

The following figure shows the execution of indirect recursive trigger.

21.2 Creating Triggers

The CREATE TRIGGER statement is used to create the trigger. The syntax for creating trigger is as shown below:

The example of create trigger is demonstrated below:

             create trigger tr1 on empholiday1
             after update as
             begin

                 update empholiday1
                set empid=202 from inserted when inserted.vacationhours=80;

             end

The following statement is used to display the data inserted into the magic table.

             create trigger trg2 on empholiday1
             after update as

             begin

                select * from deleted
                select * from inserted
             end

The after update trigger is created on the empholiday1 table. When the trigger is fired, it is executed and displays the previous values and the updates values.

Execute the following UPDATE statement on the empholiday1 table.

           update empholiday1 set empid=270 where empid=201

The following values are displayed as output after executing the following query.

      

Creating an INSERT Trigger

An INSERT Trigger is fired when new rows are added in the trigger table. The following statement is used for demonstrating the INSERT Trigger is as shown below: 

    create trigger trginsert on employeedata
    for insert
    as
            DECLARE @ContactID int
            select @ContactID = ContactID from inserted

            if ( @ContactID! = 1234)
            begin
                        print ‘ The ContactID is not correct’
                        rollback transaction
            end          

    insert into employeedata values ( 701, 3456, ‘sam’, ‘analyst’);

 

Creating an UPDATE trigger

 

An UPDATE trigger is fired when user needs to update the records in the trigger table.

Consider the following statement is used to UPDATE Trigger.         

           create trigger trgupdateexam
           on examdata
           for update
           as

               if UPDATE ( subid)
              begin
              DECLARE @subid int
              select @subid = 201 from examdata

              if ( @subid=201)
                        begin
                        print ‘ The subname id Science’
                        rollback transaction
                        end

           end

Creating a DELETE Trigger

A DELETE trigger is used when the user needs to delete the rows from the trigger table. The following statement is used to delete the trigger from the table.

              CREATE TRIGGER trgDeleteStud
              ON Student
              FOR DELETE
              AS
                  PRINT ‘Deleting the records is not possible’
                  ROLLBACK TRANSACTION
             RETURN

Creating an AFTER Trigger

An AFTER Trigger is used when execution of DML statement. The following statement is used to execute the trigger.

            CREATE TRIGGER trgStudData ON Student
            AFTER DELETE
            AS
           PRINT ‘User is attempting to delete the data’

Creating a DDL Trigger

The DDL trigger is used when the DDL statements are executed. The administrative tasks on the database are performed in the database. The following statement is used to create the DDL trigger.

           CREATE TRIGGER trgnew1
           ON DATABASE
           FOR DROP_TABLE
           AS
                PRINT ‘The trigger should be disabled and then the table can be
                Dropped’
                ROLLBACK

21.3 Managing Triggers

The following operations can be performed on a trigger.

1) Alter Trigger

There are requirements when user needs to modify the created trigger. The ALTER TRIGGER statement is used to modify the trigger.

The syntax for the ALTER TRIGGER is as shown below:

         ALTER TRIGGER trigger_name
         { FOR | AFTER | INSTEAD OF } { event_type [ , …. N ] |
         DDL_DATABASE_LEVEL_EVENTS }
         {
         AS
        {
           Sql_statement [ …. n ] }    
         }

Consider an example to modify the trgDeleteStudData used for deleting the records from the Student trigger. The following statement is used for altering the trigger.

ALTER TRIGGER trgDeleteStudData ON Student 
INSTEAD OF DELETE 
AS
    PRINT ‘Deletion of records is not allowed’
    ROLLBACK TRANSACTION
RETURN

 

2) Deleting trigger

There are requirements when user needs to delete a trigger. The DROP TRIGGER statement is used to delete the trigger.

The syntax for deleting a trigger is as shown below:

        DROP TRIGGER { trigger }

where,

trigger is the name of the trigger to be deleted.

The following statement is used to drop the trgDeleteStudData.

       DROP TRIGGER trgDeleteStudData

2) Disabling a Trigger

User needs to prevent the execution of a trigger for some operations on a table. Once the trigger is disabled, it will not be executed until it is enabled by the user. The trigger is not deleted, but it is present in the database.

SQL Server provides the DISABLE TRIGGER statement to disable the trigger. The syntax for disabling a trigger is as shown below:

DISABLE TRIGGER { [ schema_name . ] trigger_name | ALL }

ON { object_name | DATABASE }

The example demonstrating the disabling of trigger is as shown below:

            DISABLE TRIGGER trgdatavalue ON DATABASE

4)Enabling the trigger

User can enable the disabled trigger by using the ENABLE TRIGGER statement. The syntax for enabling the trigger is as shown below:

ENABLE TRIGGER { [ schema_name . ] trigger_name | ALL }

ON { object_name | DATABASE }

The following example is used to enable the trigger.

          ENABLE TRIGGER trgdatavalue ON DATABASE

 

 

Like us on Facebook