Tech Blog

Since 2004

Read Article

SQL Server Triggers To Mirror a Table


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

Rest of the Story:

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)

sql sqlserver
Xamarin Android - Use Toolbar in place of ActionBar Generate Insert, Update, Delete Trigger for Table Audit