You must Sign In to post a response.
  • Category: SQL Server

    Triggers

    Can we create multiple triggers for the same table ijn a single querry?
  • #593065
    not getting clearly.
    Thanks,
    Senthilnathan

  • #593068
    Sorry for unclearity...... I am trying to ask is there any possibility of creating more than one triggers?
    Sunita Hiremath

  • #593069
    Ya Sure. We can create more than one trigger for a table like create trigger, update trigger and delete trigger..
    Thanks,
    Senthilnathan

  • #593073
    Can you please explain through an example.......?
    Sunita Hiremath

  • #593075
    Hi,
    Here is the code for multiple trigger for the single Table

    USE pubs
    IF EXISTS (SELECT name FROM sysobjects
    WHERE name = 'trig1' AND type = 'TR')
    DROP TRIGGER trig1
    GO
    -- Creating a trigger on a nonexistent table.
    CREATE TRIGGER trig1
    on authors
    FOR INSERT, UPDATE, DELETE
    AS
    SELECT a.au_lname, a.au_fname, x.info
    FROM authors a INNER JOIN does_not_exist x
    ON a.au_id = x.au_id
    GO
    -- Here is the statement to actually see the text of the trigger.
    SELECT o.id, c.text
    FROM sysobjects o INNER JOIN syscomments c
    ON o.id = c.id
    WHERE o.type = 'TR' and o.name = 'trig1'

    -- Creating a trigger on an existing table, but with a nonexistent
    -- column.
    USE pubs
    IF EXISTS (SELECT name FROM sysobjects
    WHERE name = 'trig2' AND type = 'TR')
    DROP TRIGGER trig2
    GO
    CREATE TRIGGER trig2
    ON authors
    FOR INSERT, UPDATE
    AS
    DECLARE @fax varchar(12)
    SELECT @fax = phone
    FROM authors
    GO
    -- Here is the statement to actually see the text of the trigger.
    SELECT o.id, c.text
    FROM sysobjects o INNER JOIN syscomments c
    ON o.id = c.id
    WHERE o.type = 'TR' and o.name = 'trig2'





    Regards,
    Angad Yadav
    --------------------------
    Work Hard, Be Smart

  • #593076
    I am not sure about that but you can try in one way,

    declare @query varchar(8000)

    Set @query = /* write your 1st trigger */ then append the 'GO' keyword after every separate trigger you write and then finally execute the query using

    EXEC sp_executequery @query

    Lets try this and please let me know if this work.


    Thanks & Regards


    Bunty

  • #593077
    Fine.

    We can create three types of trigger for a table like insert trigger, update trigger, delete trigger.
    We no need to call a trigger, it will be fired automatically. That means insert trigger will be fired, whenever we insert a data to the table.
    update trgger will be executed whenever data is updated in that table.

    like that, Delete trigger will be executed , when the data is deleted from that table.

    syntax:

    create trigger trigger_name
    On table_name
    for Insert or update or delete ---> Type of trigger
    As
    Begin
    -----
    -----
    End

    Thanks,
    Senthilnathan

  • #593081
    Thank you for your responses.
    Sunita Hiremath


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.