SQL Server Performance

Based on Triggers

Discussion in 'T-SQL Performance Tuning for Developers' started by push, Sep 5, 2007.

  1. push New Member

    HI to All,
    I want to create a trigger for update and insert both and for which i want that if user make any update or new insert statement in the table_1 the same result that is inserted or updated for table_1 should be reflect in the table_2. Table_1 and Table_2 both have same fields and same data types.
    Please send me Reply.
    Thanks in Advance...........
  2. Adriaan New Member

    You should be able to create the trigger code based on the examples in Books Online (installed as part of the SQL Server software) - just look for CREATE TRIGGER in the index.
    You can join the "magic table" inserted to Table_2 to do an UPDATE and an INSERT against Table_2.
    UPDATE t
    SET t.col1 = i.col1, T.col2 = i.col2
    FROM Table_2 t INNER JOIN inserted i ON t.key = i.key
    INSERT INTO Table_2 (key, col1, col2)
    SELECT i.key, i.col1, i.col2
    FROM inserted i LEFT JOIN Table_2 t ON i.key = t.key
    WHERE t.key IS NULL
    If you do the insert before the update, the update will also update the newly inserted rows, which is not necessary and only costs time (and log space). So always do the update before the insert.

Share This Page