locked
Update trigger RRS feed

  • Question

  • I am looking for a way to catch any changes that get made to a table. A trigger looks to be the way to go, but I am struggling to get it to work. the idea is that any changes that get made to the 'Place' table will be reflected in the Place2 table. (Both Place and Place2 tables have the same structure)

     

    So far I have:

    CREATE TRIGGER Trig_place_Upd ON dbo.Place AFTER UPDATE AS
    BEGIN
    UPDATE OU_Test.dbo.Place2
    SET Place_id = inserted
    ,name = inserted
    ,address = inserted
    ,second_address = inserted
    ,city = inserted
    ,zippost = inserted
    ,phone = inserted
    END
    GO

    CREATE TRIGGER Trig_Place_Ins ON dbo.Place AFTER INSERT AS
    BEGIN
    INSERT INTO Place2 SELECT * FROM Inserted
    END

    GO

    CREATE TRIGGER Trig_Place_Del ON dbo.Place AFTER DELETE AS
    BEGIN
    DELETE FROM Place2 WHERE place2.place_id=deleted.place_id
    END

    GO

    But of these only the Insert seems to work. What am I missing.

     

    As an aside, what sort of performance hit will there be if I have to run 3 triggers like this on 15 tables?

    Wednesday, March 31, 2010 4:22 PM

Answers

  • CREATE TRIGGER Trig_place_Upd ON dbo.Place AFTER UPDATE AS
    BEGIN
    UPDATE OU_Test.dbo.Place2
       SET Place_id = inserted.Place_id
          ,name = inserted.name
          ,address = inserted.address
          ,second_address = inserted.second_address
          ,city = inserted.city
          ,zippost = inserted.zippost
          ,phone = inserted.phone
    FROM OU_Test.dbo.Place2
    INNER JOIN inserted ON OU_Test.dbo.Place2.Place_id = inserted.Place_id
    END
    GO
    
    CREATE TRIGGER Trig_Place_Ins ON dbo.Place AFTER INSERT AS
    BEGIN
    INSERT INTO Place2 SELECT * FROM Inserted
    END
    
    GO
    
    CREATE TRIGGER Trig_Place_Del ON dbo.Place AFTER DELETE AS
    BEGIN
    DELETE FROM Place2 WHERE place2.place_id
      IN (SELECT deleted.place_id FROM deleted)
    END 
    
    GO
    


    Since with every insert, you are doing another insert into a table, and with every delete you are doing another delete, etc, you will approximately double the cost of changes to the dbo.Place table.

     

    Tom

    • Proposed as answer by Naomi N Thursday, April 1, 2010 1:45 AM
    • Marked as answer by KJian_ Wednesday, April 7, 2010 6:01 AM
    Wednesday, March 31, 2010 4:37 PM

All replies

  • CREATE TRIGGER Trig_place_Upd ON dbo.Place AFTER UPDATE AS
    BEGIN
    UPDATE OU_Test.dbo.Place2
       SET Place_id = inserted.Place_id
          ,name = inserted.name
          ,address = inserted.address
          ,second_address = inserted.second_address
          ,city = inserted.city
          ,zippost = inserted.zippost
          ,phone = inserted.phone
    FROM OU_Test.dbo.Place2
    INNER JOIN inserted ON OU_Test.dbo.Place2.Place_id = inserted.Place_id
    END
    GO
    
    CREATE TRIGGER Trig_Place_Ins ON dbo.Place AFTER INSERT AS
    BEGIN
    INSERT INTO Place2 SELECT * FROM Inserted
    END
    
    GO
    
    CREATE TRIGGER Trig_Place_Del ON dbo.Place AFTER DELETE AS
    BEGIN
    DELETE FROM Place2 WHERE place2.place_id
      IN (SELECT deleted.place_id FROM deleted)
    END 
    
    GO
    


    Since with every insert, you are doing another insert into a table, and with every delete you are doing another delete, etc, you will approximately double the cost of changes to the dbo.Place table.

     

    Tom

    • Proposed as answer by Naomi N Thursday, April 1, 2010 1:45 AM
    • Marked as answer by KJian_ Wednesday, April 7, 2010 6:01 AM
    Wednesday, March 31, 2010 4:37 PM
  • Hi there,

    If you are doing this in order to be able to check changes across time, it would be a good idea to add a time stamp so that you can check changes ordered by time. Another isue would be the delete action, if you remove a record on the main table that record will also be removed from the copy table. The way you're doing it both tables will have the same data.

    José Cruz

    Wednesday, March 31, 2010 11:07 PM
  • cheers guys,

     

    that was useful.

    Saturday, April 10, 2010 9:22 PM