Results 1 to 7 of 7

Thread: Triggers

  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: Triggers

    How can i write a trigger on table for insert,delete and update onto a different table, Means if i do any of the 3 DML on table aaa that should effect on table bbb.Can anyone provide me with some sample script.

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    -- =============================================
    -- Create trigger basic template(After trigger)
    -- =============================================
    IF EXISTS (SELECT name 
    	   FROM   sysobjects 
    	   WHERE  name = N'<trigger_name, sysname, trig_test>' 
    	   AND 	  type = 'TR')
        DROP TRIGGER <trigger_name, sysname, trig_test>
    GO
    
    CREATE TRIGGER <trigger_name, sysname, trig_test>
    ON <table_name, sysname, pubs.dbo.sales>
    FOR DELETE, INSERT, UPDATE 
    AS 
    BEGIN
    	RAISERROR (50009, 16, 10)
    END
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    drop table test
    create table test(id int,f1 char(1),f2 int,f3 char(2))
    create table test2(id int,f1 char(1),f2 int,f3 char(2),f4 varchar(25))
    go
    create trigger tr_test on test
    for insert, delete, update
    as
    insert test2
    select *,'insert' from inserted

    insert test2
    select *,'delete' from deleted
    /* or
    delete test
    where id not in(select id from test)

    insert test2
    select *,'new' from test
    where id not in(select id from test2)

    */

  4. #4
    Join Date
    Apr 2003
    Posts
    176
    Thanks for ur response,Still i am not clear with the solution.

    I want to add all the threee DMLs in one trigger.Should i write any condistional statement like IF INSERT
    BEGIN
    .....SOme code
    ENd
    ELSE IF UPdate
    BEGIN
    ...some code
    END
    ELSE delete
    BEGIN
    ..some code
    END

    PLease let me know how should i implement the trigger.

    Thanks.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Have you read about triggers in Books Online? That should be your first resource.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well...if you use the code I got from the template editor....

    And then do

    IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)

    -- Do Update logic

    IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)

    -- Do INSERT logic

    IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)

    -- Do DELETE logic
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    That should be your first resource.
    I thought that should be a bartender......
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •