I had a need to mirror any changes to one Sql Server table to another Sql Server table of a different name. Read on.
- February 13, 2016
I had a need to mirror any changes to one Sql Server table to another Sql Server table of a different name. Both tables had similar columns and types. In this sample my table is called Dave_Test and my mirror table was Dave_Test_Audit.
I will provide the create table structures in the event you want to replicate my code.
CREATE TABLE [Dave_Test]( [Emp_ID] [int] IDENTITY(1,1) NOT NULL, [Emp_name] [varchar](100) NULL, [Emp_Sal] [decimal](10, 2) NULL ) ON [PRIMARY] CREATE TABLE [Dave_Test_Audit]( [Emp_ID] [int] NULL, [Emp_name] [varchar](100) NULL, [Emp_Sal] [decimal](18, 0) NULL ) ON [PRIMARY] GO
Here are my insert, update and delete triggers.
Create TRIGGER [Dave_Test_Trigger_Delete] ON [Dave_Test] AFTER DELETE AS BEGIN SET NOCOUNT ON; DELETE FROM Dave_Test_Audit WHERE emp_id IN (SELECT emp_id FROM deleted) END Create TRIGGER [Dave_Test_Trigger_Insert] ON [Dave_Test] AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO Dave_Test_Audit SELECT * FROM inserted END END CREATE TRIGGER [[Dave_Test_Trigger_Update] ON [Dave_Test] AFTER UPDATE AS BEGIN SET NOCOUNT ON; IF EXISTS(SELECT * FROM Dave_Test_Audit a JOIN inserted AS i ON a.emp_id=i.emp_id) BEGIN UPDATE Dave_Test_Audit SET emp_id = i.emp_id, emp_name = i.emp_name, emp_sal = i.emp_sal FROM inserted i WHERE Dave_Test_Audit.emp_id=i.emp_id END ELSE BEGIN INSERT INTO Dave_Test_Audit SELECT * FROM inserted END END
So why does my Update trigger deal with inserts? The master table (Dave_Test) already had rows existing before the triggers were created. With the update trigger managing inserting and updating records if the record is updated and does yet belong in the master audit table then the recently updated row will be inserted into the audit table for us.
For those old enough – this is Trigger (the smartest horse in the movies)