Triggers

Next Post
By Admin at 8 Jan 2010, 17:15 PM
  • Triggers are the type of stored procedures and will be attached to the tables. We can not execute the triggers directory. Triggers will be fired only in repose to Delete, Insert and Update event on the table. Sql Server 2005 has two different types of transaction triggers. They are instead of triggers and after triggers

    Creating Triggers

    Triggers can be created and modified with the help of DDL Commands

    1. CREATETRIGGER TriggerName on TableName
    2. AFTERInsertupdateDelete
    3. As
    4. Trigger Code
    5. After Triggers

    Table can have more than one after trigger for each of the three Insert, Delete and Update events. After triggers are useful for the following

    • Complex data validation
    • Enforcing complex business rules
    • Maintaining modified date columns

    Example

    1. CREATETRIGGER TestTrigger ON BOOKS
    2. AFTERInsert
    3. AS
    4. PRINT 'After Trigger Executed'
    5. GO

    Here we have created a Trigger ‘TestTrigger’ and it will be executed when you insert a row.

    1. INSERTINTO BOOKS(NAME,PRICE) VALUES('ASP.NET',150.00)

    Result is

    After Trigger Executed

    (1 row(s) affected)

    Instead of Triggers

    Instead of triggers will be executed instead of the submitted transaction. Hence the submitted transaction will not execute. Each table is limited to use only one Instead of trigger. But instead of triggers can be applied to views.

    You can use the Instead of Triggers for the following situation

    • When DML statement tried to update the non updatable views, then the Instated of trigger updates the underlying tables.
    • When the DML statement attempts to delete a row, an instead of trigger moves the row to an archive table instead.

    Example

    1. CREATETRIGGER TestTriggerTwo ON BOOKS
    2. INSTEADOFInsert
    3. AS
    4. PRINT 'Instead of Trigger'
    5. GO
    6. INSERTINTO BOOKS(NAME,PRICE) VALUES('VB.NET',150.00)

    Result

    Instead of Trigger

    (1 row(s) affected)

    Insert statement worked as if one row is affected but the effect of the insert statement was blocked by the instead of trigger. So the print statement is executed and prints the message ‘Instead of Trigger’

    Trigger Limitations

    The following SQL commands are not permitted within the trigger

    Restore Database

    • Create, Alter or drop database
    • Disk Resize
    • Disk Init
    • Reconfigure

    Disabling Triggers

    The DML Statement can never bypass the trigger. But system administrator can disable the trigger

    Syntax

    1. ALTERTABLE TableName ENABLE or DISABLE TriggerName

    Example

    1. ALTERTABLE BOOKS
    2. DISABLE TRIGGER TestTriggerTwo

    The above example will disable the trigger “TestTriggerTwo”. In our previous example this triger will be executed instead of the insert statement. After the execution of the Disable Trigger query now you will allowed to insert a row in the Books table.


    Comments

     

    Post a comment

    Please correct the following: