Triggers in SQL SERVER.

Discussion in 'SQL Server' started by coderzone, Dec 16, 2008.

  1. coderzone

    coderzone Super Moderator

    Joined:
    Jul 25, 2004
    Messages:
    736
    Likes Received:
    38
    Trophy Points:
    28
    A trigger is a special kind of stored procedure that is invoked whenever an attempt is made to modify the data in the table it protects. Modifications to the table are made ussing INSERT,UPDATE,OR DELETE statements.Triggers are used to enforce data integrity and business rules such as automatically updating summary data. It allows to perform cascading delete or update operations. If constraints exist on the trigger table,they are checked prior to the trigger execution. If constraints are violated statement will not be executed and trigger will not run.Triggers are associated with tables and they are automatic . Triggers are automatically invoked by SQL SERVER. Triggers prevent incorrect , unauthorized,or inconsistent changes to data.

    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:
    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:
    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:
    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:
    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
    
     
  2. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
  3. phone00x

    phone00x New Member

    Joined:
    Jan 8, 2009
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    0
    dear friend'sSQL Server is easy to create trigger, so think.
    than'k friend's
     
  4. LenoxFinlay

    LenoxFinlay Banned

    Joined:
    Apr 15, 2009
    Messages:
    46
    Likes Received:
    0
    Trophy Points:
    0
    Hi,

    Thanks for your useful information. For more information about this topic please visit <<snip>>. I think its more useful for you.
     
  5. itsys

    itsys Banned

    Joined:
    May 2, 2009
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    0
    Nice Information for triger
     
  6. technica

    technica New Member

    Joined:
    Dec 15, 2007
    Messages:
    107
    Likes Received:
    0
    Trophy Points:
    0
    Home Page:
    http://www.technicaltalk.net
    A multi row-Trigger is useful part for me. Thanks for sharing the information with us all.
     
  7. kumarmannu

    kumarmannu Banned

    Joined:
    Feb 2, 2011
    Messages:
    51
    Likes Received:
    0
    Trophy Points:
    0
    Great information please tell something about view.
     
  8. TPrice

    TPrice New Member

    Joined:
    Aug 24, 2010
    Messages:
    9
    Likes Received:
    1
    Trophy Points:
    0
    Thanks for this information on triggers. I have been a little bit confused in this area as to their purpose and function, but I think that I have a better grasp of them now. The only question now is, do I understand this well enough to actually put it to work? I guess I will find out soon enough.
     
    1 person likes this.
  9. rkjarvis

    rkjarvis New Member

    Joined:
    Jun 30, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Location:
    Illinois
    We have an application where rows are mysteriously deleted off a single table. Vendor claims it isn't their code and application folks claim they didn't do it. I used a delete trigger to insert the data being deleted to another table. When the delete inadvertantly happens, the deleted records are at least logged somewhere. The application team has a quick way to restore the missing data back into the original table. Is there a way to capture what ID is actually performing the delete in the trigger?
     
  10. gsuryaprasath

    gsuryaprasath New Member

    Joined:
    Jul 12, 2011
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    Very nice explanation about ..its really superb..keep posting like these..
     
    Last edited by a moderator: Jul 12, 2011
  11. upendraets

    upendraets New Member

    Joined:
    Mar 19, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Your answer is awesome.


    Trigger is a one type of stored procedure that is automatically execute.
    Types of Trigger :
    Insert Trigger
    Delete Trigger
    Update Trigger.
     
  12. odehfehr

    odehfehr New Member

    Joined:
    Sep 5, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    A Trigger is fired when a block of code, data in the underlying table is affected by one of the statements of the manipulation language DML (Data)-INSERT, UPDATE, OR DELETE.
     
  13. watson88

    watson88 New Member

    Joined:
    Jan 11, 2012
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Thanks a lot i was searching for this for very long time, actually i recently join this forum and i hope got lot of such useful information in future.
     

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