Creating and Managing Triggers in SQL Server

Discussion in 'SQL Server' started by MinalS, Oct 28, 2014.

  1. A trigger is a stored procedure used to execute the statements when an event is raised. The trigger is used statements for actions like insert, delete or update the data. The basics about triggers in sql server is here. In this article we will discuss about several trigger types used for manipulating the data. The following trigger types are mentioned below:
    1. Data Modification Language ( DML ) triggers
    2. Data Definition Language ( DDL ) triggers
    Before we begin just an introduction from above article about how to create triggers.

    Creating Triggers



    User can create trigger using the CREATE TRIGGER statement. The syntax for creatinf a trigger is as shown below:

    Code:
    CREATE TRIGGER trigger_name
    ON { table | view }
    { FOR | AFTER | INSTEAD OF }
    [ WITH [ ENCRYPTION ] [ EXECUTE AS ] ]
    { AS
    	{ sql_statement [ …n ] }
    }
    
    Where,
    • trigger_name is the name of the trigger to be created
    • table | view specifies the name of the table or view on which the trigger is created.
    • FOR | AFTER | INSTEAD OF specifies the execution of the trigger
    • WITH ENCRYPTION encrypts the text of the CREATE TRIGGER statement
    • EXECUTE AS specifies the context in which the trigger is created
    Consider the following example of creating a trigger in SQL.

    Code:
    create trigger tr1 on empholiday1
    after update as
    begin
        update empholiday1
        set empid = 202 from inserted where inserted.vacationhours=80;
    end
    
    In the above statement, the trigger as tr1 is created on the empholiday table. The trigger is fired when the UPDATE statement on the empholiday table. The trigger updates the vacation hours with the updated value.

    The following statement creates a trigger to display the data inserted in the magic tables.
    Code:
    create trigger trg2 on empholiday1
    after update as
    begin
        select * from deleted
        select * from inserted
    end
    
    In the above statement an AFTER UPDATE trigger on the empholiday table is created.

    Consider an update statement to be executed on the empholiday table
    Code:
    update empholiday1 set empid=270 where empid=201
    
    The following output is generated when the trigger is fired on the empholiday table.

    [​IMG]

    In the above output, the result on the top shows the values before the execution of the UPDATE statement and the lower result set shows the values after the values are updated.

    DML Triggers



    A DML Trigger is used when the DML statements as INSERT, UPDATE or DELETE are affecting the tables. The data is maintained in these tables.

    The characteristics of the DML trigger are as mentioned below:
    1. They are fired automatically by the SQL server when a modification statement is executed.
    2. It does not return any value to the user
    3. They cannot be explicitly invoked or executed
    4. It is used to prevent inconsistent changes to the data
    The SQL Server creates two temporary tables as magic tables. They are created when the INSERT, DELETE or UPDATE statement are executed. The tables are known as Inserted and Deleted. The structure is similar to the table on which the trigger is defined.

    The values in the Inserted table are the copy of the records inserted in the trigger table. The Deleted table contains the records that are deleted from the trigger table.

    Consider the examples of INSERT, UPDATE and DELETE triggers in SQL server.

    Insert Trigger in SQL

    The insert trigger is fired at the time of adding the new rows to the trigger table.

    Consider the creation of a trigger on the employeedata in the SQL Server.
    Code:
    create trigger trginsert on employeedata
    for insert
    as 
    DECLARE @ContactID int
    select @ContactID=ContactID from inserted
    if(@ContactID != 1234)
    begin
        print 'The Contact ID is not correct'
        rollback transaction
    end
    
    In the above code, the trigger named as trginsert is created on the employeedata table. If the user wants to modify the contact id of the user, the data cannot be modified.

    Insert the following values in the employeedata table.
    Code:
    insert into employeedata values (701,3456,'sam', 'analyst');
    
    The following statement is displayed when the trigger is raised by the server.

    [​IMG]

    UPDATE trigger in SQL

    An UPDATE trigger is fired when the user is updating the records in the trigger table. The following statement is used to create the update trigger on the examdata table.
    Code:
    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 is Science'
            rollback transaction
        end
    end
    
    Update the values in the examdata table by using the following statement.
    Code:
    update examdata set subid = 208 where subname = 'Science'
    
    The following message is displayed when the update trigger is used by the server.

    [​IMG]

    Delete Trigger in SQL

    The DELETE Trigger is fired when the user deletes the rows from the trigger table. Consider an example for trigger creation to disable the deletion of rows from the salary table

    Code:
    CREATE TRIGGER trgdeletedata
    	ON salary
    	FOR DELETE
    	AS
    		PRINT ‘ Deletion of data is not possible’
    		ROLLBACK TRANSACTION
    	RETURN
    
    AFTER TRIGGER in SQL

    The AFTER TRIGGER is fired when the DML statement is executed. The following statement is used to perform the AFTER TRIGGER execution.

    Code:
    CREATE TRIGGER trgdeletevalue ON salary
    AFTER DELETE 
    AS
    PRINT ‘The deletion is successful’
    
    When there are multiple AFTER Triggers for a DML operation, the sequence of the trigger execution can be changed. The sp_settriggerorder system stored procedure is used to change the sequence of the execution.

    Code:
    sp_settriggerorder <triggername>, <order-value>, <DML-operation>
    
    Where,
    • triggername specifies the name of the trigger whose order of execution is to be modified.
    • order-value is the order in which the trigger needs to be executed
    • DML-operation is the operation for which the trigger is created.
    Consider the example for AFTER TRIGGER in SQL server.

    Code:
    CREATE TRIGGER trgDelete1 ON empholiday1
    AFTER DELETE
    AS
    PRINT ‘Assuming to Delete’
    
    The trigger is executed in the sequence in which they are created. To modify the sequence of the triggers, the following statement is executed.
    Code:
    sp_settriggerorder ‘trgDelete1’, ‘FIRST’, ‘DELETE’
    RETURN
    
    INSTEAD OF TRIGGER in SQL

    The INSTEAD OF triggers are executed when the events causes the trigger to fire. If user wants to create the INSTEAD OF UPDATE trigger on a table, the statements in the trigger will be executed instead of the UPDATE statement.

    The triggers are executed after the inserted and deleted tables reflecting the changes to the base tables are created. They are executed before the constraint is executed by the server.

    Consider the example of restricting the INSTEAD OF trigger on the deletion of records in the Employee table.

    Code:
    CREATE TRIGGER trgDelete2 ON Employee
    INSTEAD OF DELETE 
    AS
    PRINT ‘The employee records cannot be deleted’
    

    DDL Trigger



    The DDL Trigger is used to fire when the DDL statements are executed by the user. The administrative tasks are performed by the user for regulating the database operations.

    The following statement creates a DDL trigger.

    Code:
    CREATE TRIGGER trgperform1
    ON DATABASE
    FOR DROP_TABLE, ALTER_TABLE
    AS
    	PRINT ‘User must disable the trigger’
    	ROLLBACK
    
    The above trigger will be fired when the DROP_TABLE or ALTER_TABLE event occurs on the database.

    Managing Triggers

    User can manage the triggers created in the system. The following operations are performed on the triggers.

    Altering a Trigger

    There are requirements when user needs to modify the data added in the trigger. To modify the trigger, user needs to change the code of the trigger. The ALTER TRIGGER is used for modifying the trigger.

    The syntax for ALTER TRIGGER is as shown below.

    Code:
    ALTER TRIGGER trigger_name
    { FOR | AFTER | INSTEAD OF } { event_type [ ,…n ] |
    DDL_DATABASE_LEVEL_EVENTS )
    { AS
    	{ sql_statement [ ,…. n ] }
    }
    
    Consider an example for modifying the trigger trgdelete2 that was created to delete the records from the table. Execute the following statement to modify the trigger.
    Code:
    ALTER TRIGGER trgDelete2 ON Employee
    INSTEAD OF DELETE
    AS
    	PRINT ‘Deletion of records is not allowed’
    	ROLLBACK TRANSACTION
    RETURN
    
    Deleting a Trigger

    There might be certain requirements where user wants to delete some triggers. The DROP TRIGGER statement is used for deleting a trigger. The syntax for DROP TRIGGER is as shown below:
    Code:
    DROP TRIGGER { trigger }
    
    Where,
    • trigger is the name of the trigger
    The following statement is used to drop the trigger.
    Code:
    DROP TRIGGER trgDatavalues1
    
    Disabling a Trigger

    User needs to restrict the execution of a trigger to perform certain operations on a table. When the user disables a trigger, it will not execute until it is enabled again. The trigger is not deleted but it is present in the database.

    The DISABLE TRIGGER is used to disable the trigger on the table. The syntax for disabling the trigger is as shown below.
    Code:
    DISABLE TRIGGER { [ schema_name ]. trigger_name | ALL }
    ON { object_name | DATABASE }
    
    Where,
    • schema_name is the name of the schema
    • trigger_name is the name of the trigger
    • ALL is used to specify the triggers defined on the clause
    • object_name is the name of the table
    • DATABASE is the one used for the DDL trigger
    The following example is used to disable the trigger on the database.
    Code:
    DISABLE TRIGGER trg1 ON DATABASE
    
    Enabling a Trigger

    User can disable the trigger when user wants them to be restricted. If there is a requirement for the trigger, it is enabled again.

    The syntax for enabling a trigger is as shown below:
    Code:
    ENABLE TRIGGER { [ schema_name . ] trigger_name | ALL }
    ON { object_name | DATABASE }
    
    User can enable the trigger using the following statement.
    Code:
    ENABLE TRIGGER trg1 ON DATABASE
    
     
    Last edited by a moderator: Jan 21, 2017

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice