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

Retweeted by @dyardy Hey C# peeps, we’re trying C# in browser with reference content. Try it here: docs.microsoft.com/dotnet/csharp/… reply with feedback /cc @LadyNaggaga
23 Amazing Vintage Photographs Taken Inside WWII Tank Factories ~ vintage everyday vintag.es/2017/10/23-ama…
When is AI NOT Search? Artificiality intelligence==Search Engine (there cases when this is not true)
@projectedxyz Large data vs big data? Industry has so misused buzz words to sell products, and so you end up nothi… twitter.com/i/web/status/9…