none
Creating DDL trigger to keep two identical tables on two separate databases, keeping their columns in same order and in sync

    Question

  • I have two databses, tempdblog and testdblog. I'm trying to figure out how, when i alter a table on tempdblog, that exact same command will be executed on testdblog, i don't want the rows transfered i strictly want the columns.

    Below is what i have atm from a site, i've tried to add a "USE testdblog" but it errors back at me about "a USE statement is not allowed..." as well as "must declare the scalar variable @test".

    The new column names could be anything, all i know is that it's not a "add this column to the end of the table", it's more like "add this column just before userdef0 column".

    I store the SQL query it did on the main database and try to re-execute it on the other table, it's just a matter of finding out how to change databases.

    USE tempdblog
    GO
    ALTER TRIGGER [db_LOG]
     
    ON DATABASE
    FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE
    AS
          SET NOCOUNT ON
          DECLARE @xEvent XML
          DECLARE @test varchar(5000)
          SET @xEvent = eventdata()
          SET @test = CONVERT(VARCHAR(MAX), @xEvent.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
          INSERT INTO dbLOG VALUES(
                REPLACE(CONVERT(VARCHAR(50), @xEvent.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
                CONVERT(VARCHAR(25), @xEvent.query('data(/EVENT_INSTANCE/ServerName)')),
                CONVERT(VARCHAR(15), @xEvent.query('data(/EVENT_INSTANCE/UserName)')),
                CONVERT(VARCHAR(MAX), @xEvent.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
                )   
          USE testdblog
          GO
          exec sp_executesql @test
          
    GO


    Wednesday, February 08, 2012 3:08 PM

Answers

  • Below is what i have atm from a site, i've tried to add a "USE testdblog" but it errors back at me about "a USE statement is not allowed..." as well as "must declare the scalar variable @test". 

     Iarp,

     I did some correction in your given script and now it creates, alter and drop the table in both DBs

      1. First I created 2 databases

          
    Create database tempdblog
    Go
    Create database testdblog
    Go


      2.  Then I created  a table called DBLOG in TEMPDBLOG DB

     
    USE TEMPDBLOG
    CREATE TABLE DBLOG( A XML,B XML, C XML, D XML)

       3. Created the DDL Trigger in TEMPDBLOG DB

    USE tempdblog
    GO
    CREATE TRIGGER [db_LOG] 
    ON DATABASE
    FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE
    AS
          SET NOCOUNT ON
          DECLARE @xEvent XML
          DECLARE @test nvarchar(4000)
          SET @xEvent = eventdata()
          SET @test = CONVERT(VARCHAR(MAX), @xEvent.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
          INSERT INTO dbLOG VALUES(
                REPLACE(CONVERT(VARCHAR(50), @xEvent.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
                CONVERT(VARCHAR(25), @xEvent.query('data(/EVENT_INSTANCE/ServerName)')),
                CONVERT(VARCHAR(15), @xEvent.query('data(/EVENT_INSTANCE/UserName)')),
                CONVERT(VARCHAR(MAX), @xEvent.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
                )   
          --USE testdblog      
          --GO
          --exec sp_executesql @test
          -- Above 3 lines of code commented and following 2 lines added by Manish Feb-09-2012 
          set @test='USE testdblog; '+ @test       
          exec sp_executesql @test      
    GO

       4. Now create , alter and drop some table in TEMPDBLOG  db.

     
    Create Table Test1(a int)
    Create Table Test2(a int)
    
    Alter table Test1 add b int
    
    Drop table test2

       5. Now need to confirm in the testdblog DB, we can see the newly created and altered tables.

    USE TESTDBLOG
    SELECT * FROM SYS.TABLES

     


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by iarp Thursday, February 09, 2012 4:46 PM
    Thursday, February 09, 2012 3:23 AM

All replies

  • Below is what i have atm from a site, i've tried to add a "USE testdblog" but it errors back at me about "a USE statement is not allowed..." as well as "must declare the scalar variable @test". 

     Iarp,

     I did some correction in your given script and now it creates, alter and drop the table in both DBs

      1. First I created 2 databases

          
    Create database tempdblog
    Go
    Create database testdblog
    Go


      2.  Then I created  a table called DBLOG in TEMPDBLOG DB

     
    USE TEMPDBLOG
    CREATE TABLE DBLOG( A XML,B XML, C XML, D XML)

       3. Created the DDL Trigger in TEMPDBLOG DB

    USE tempdblog
    GO
    CREATE TRIGGER [db_LOG] 
    ON DATABASE
    FOR CREATE_TABLE,DROP_TABLE,ALTER_TABLE
    AS
          SET NOCOUNT ON
          DECLARE @xEvent XML
          DECLARE @test nvarchar(4000)
          SET @xEvent = eventdata()
          SET @test = CONVERT(VARCHAR(MAX), @xEvent.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
          INSERT INTO dbLOG VALUES(
                REPLACE(CONVERT(VARCHAR(50), @xEvent.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
                CONVERT(VARCHAR(25), @xEvent.query('data(/EVENT_INSTANCE/ServerName)')),
                CONVERT(VARCHAR(15), @xEvent.query('data(/EVENT_INSTANCE/UserName)')),
                CONVERT(VARCHAR(MAX), @xEvent.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
                )   
          --USE testdblog      
          --GO
          --exec sp_executesql @test
          -- Above 3 lines of code commented and following 2 lines added by Manish Feb-09-2012 
          set @test='USE testdblog; '+ @test       
          exec sp_executesql @test      
    GO

       4. Now create , alter and drop some table in TEMPDBLOG  db.

     
    Create Table Test1(a int)
    Create Table Test2(a int)
    
    Alter table Test1 add b int
    
    Drop table test2

       5. Now need to confirm in the testdblog DB, we can see the newly created and altered tables.

    USE TESTDBLOG
    SELECT * FROM SYS.TABLES

     


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by iarp Thursday, February 09, 2012 4:46 PM
    Thursday, February 09, 2012 3:23 AM
  • Hi Manish,

    How could I modify your trigger to do the same thing with two identical tables on the 'same' database?


    -Sham

    Wednesday, December 11, 2013 8:51 PM
  • You would need to do:

    SELECT @test = replace(@test, 'thattable', 'thistable')

    And make sure that the table names are significant enough, so that you don't replace other parts of the command.

    You should also take out the USE command, obviously.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, December 11, 2013 10:36 PM
  • Hey thank you Erland!!!

    However I think I screwed something up without completely understanding how this trigger was working.  I now realize it's only meant to sync the columns (which is also needed), but not the records.

    I have two tables that are identical (UserData1 and UserData2). I need to synchronize everything (one way sync) from UserData1 to UserData2. I've looked all over the net for examples and some examples are equivalent to something like:

    Field1 = a.Field1

    Field2 = a.Field2

    Etc..etc... However I have over 100 fields. Do I really need to put all fields in the examples? Is there a way to modify this script so that it not only updates the columns from UserData1 to UserData2, but also all records?

    Thanks




    • Edited by Shamunda Thursday, December 12, 2013 6:33 AM
    Thursday, December 12, 2013 4:45 AM
  • A DDL trigger is a DDL trigger and fires on statements like CREATE TRIGGER, ALTER TABLE etc. For reacting on INSERT, DELETE and UPDATE you need a different trigger. And yes, that trigger needs to list all columns. But your DDL trigger could re-generate triggers when you add columns.

    But a better question is: why do you want to do this in the first place? Maybe there is a better soluition than having two tables at all?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 12, 2013 8:56 AM
  • "But a better question is: why do you want to do this in the first place? Maybe there is a better soluition than having two tables at all?"

    Good question and the answer isn't as straight forward as I'd it to be. I'm learning sql with a database and just for the sake of simplicity so that I have current data I'd like to keep my second table in sync with the first table.

    It's strictly for academics so I don't accidentally screw something up.

    I can run something like this manually:

    UPDATE
        UserData2
    SET
        UserData2.Approved = UserData1.Approved
    FROM
        UserData2
    INNER JOIN
        UserData1
    ON 
        UserData2.TimeSheetID = UserData1.TimeSheetID

    And that works (for one field), but alas I'm a newbie and would like to know how to create a trigger that would update all 100 Fields (if needed).

    Being a newbie I'm far from knowing if there is a better way :)  Maybe a utility could handle that a bit better?


    -Sham

    Thursday, December 12, 2013 3:34 PM
  • The better way is to simply not duplicate data at all - this is a violation of the founding principles of relational theory.  Relational databases were intended to solve many of the problems of prior designs - one of which was duplication of data.  Here you are intentionally do that.  Why?  Learning is one thing, but this practice is rare in a production system in my experience.

    Beyond that, I would argue that the original poster was trying to solve a management issue - maintaining (blindly, I might add) consistency in schema between 2 different databases.  In a nutshell, I call this LPS - lazy programmer syndrome.  Unfortunately this development approach appears to be rather common here - change things whenever and however without regard to their impact and without coordination. 

    Thursday, December 12, 2013 4:32 PM
  • Good question and the answer isn't as straight forward as I'd it to be. I'm learning sql with a database and just for the sake of simplicity so that I have current data I'd like to keep my second table in sync with the first table.

    OK, since you are a learner, here is the lesson for the day: don't do that.

    It is not uncommon that we learn new things that we want to things that seems reasonable, but which the people who are experienced in the trade would never do or even consider.

    There are certainly sync scenarios in the world of databases, but rarely in the same database. More commonly, people want to replicate data (and schema) from one server to another, maybe because they are geographically far apart. And SQL Server offers three means of replication: Snapshot Replication (only replicate once, but don't stay in sync), Transaction Replication (basically what you are asking for) and Merge Replication (which is two-way). I don't know if there is any restriction that prevents transactional replication to be set up from one table to another in the same database. In any case, you will still need distribution database. You can certainly replicate between two databases on the same server, and it can be a worthwhile learning experience to set this up. Particularly if you aim more at administration than development.

    You may argue that writing this trigger could be a good learning exercise, even if it's useless. No it isn't. You would have to learn to use the system catalog and dynamic SQL. And, certainly at one point, you need to master both. But particulary dynamic SQL is something you should learn much later. You first need to master regular plain SQL, because this is so much more important.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 12, 2013 10:54 PM