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.  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)

Author

david

comments powered by Disqus

Categories

Recent Tweets

"Setting up and Getting Started with Power BI Embedded | Data Exposed" channel9.msdn.com/Shows/Data-Exp… #webcast #feedly
Retweeted by @dyardy @scottgu @victoriabeckham Worlds collide, double take on that photo!
@scottgu @victoriabeckham Worlds collide, double take on that photo!
@projectedxyz It is really easy, what are you doing (isn't this data which on the web)