Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

As the title says I would like to create a DDL trigger which in turn creates DML triggers for most new tables.

The trigger I have so far will create triggers on ALL tables, which is not what I'm after:

CREATE TRIGGER [CreateReplTriggers] ON DATABASE 
    FOR CREATE_TABLE 
AS 
BEGIN
    SET NOCOUNT ON
    SET ANSI_PADDING ON
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    DECLARE @TABLE_NAME SYSNAME

    SELECT @TABLE_NAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

    EXEC sp_CreateReplTrigger @TABLE_NAME
END

sp_CreateReplTrigger is a stored proc which creates DML trigger for table with passed name.

Unfortunately this fires off when some System Tables are created as well, e.g. dbo.MSreplication_subscriptions.

There doesn't seem to be anything useful in EVENT_DATA() which would help me differentiate user tables from system tables.

There's also many System Tables and their names don't have much in common: http://msdn.microsoft.com/en-us/library/aa260604(v=sql.80).aspx ... I can't exclude table that have 'MS' or 'sys' in name, because that would be hacky and bad, and would definitely cause issues 5 years later when someone forgets that user tables aren't supposed to have 'MS' or 'sys' in name.

Edit:

The final solution was a combination of the answer below + adding a whitelist array or table. So the stored proc was only called if the table name was in the whitelist. is_ms_shipped was not used.

share|improve this question
1  
I highly recommend not using the sp_ prefix in your procedure names (see sqlperformance.com/2012/10/t-sql-queries/sp_prefix) but adding the dbo. prefix when calling your stored procedure (see sqlblog.com/blogs/aaron_bertrand/archive/2009/10/11/…). –  Aaron Bertrand Nov 19 '12 at 15:10

2 Answers 2

up vote 3 down vote accepted

From SQL 2005 onwards I would use sys.tables which is scoped to user tables and includes the is_ms_shipped column, something like:

CREATE TRIGGER [CreateReplTriggers] ON DATABASE 
    FOR CREATE_TABLE 
AS 
BEGIN
    SET NOCOUNT ON
    SET ANSI_PADDING ON
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    DECLARE @table_name SYSNAME
    DECLARE @xml XML

    SET @xml = EVENTDATA() 

    SELECT 
        @table_name = QUOTENAME( t.c.value('(SchemaName/text())[1]', 'SYSNAME') ) + '.' + 
                      QUOTENAME( t.c.value('(ObjectName/text())[1]', 'SYSNAME') )
    FROM @xml.nodes( 'EVENT_INSTANCE') t(c)


    IF EXISTS ( SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(@TABLE_NAME) AND is_ms_shipped = 0 )
    BEGIN
        RAISERROR( 'Creating replication trigger for table %s.', 10, 1, @TABLE_NAME )
        EXEC sp_CreateReplTrigger @TABLE_NAME
    END
    ELSE
        -- Raise low-level error which won't cause trigger to fail, but will print message to indicate which table has been ignored
        RAISERROR( 'Table %s ignored for replication trigger proc.', 10, 1, @TABLE_NAME )
END

In a quick test on some of my servers, this excluded MS* and sys* tables including MSreplication_subscriptions etc

share|improve this answer
    
+1 sys.tables is a much better choice than sysobjects. sysobjects is only there for backward compatibility, and shouldn't be perpetuated unless a question is specifically about 2000. There is no reason to keep using it in 2005+. –  Aaron Bertrand Nov 19 '12 at 15:07
    
Thanks for RAISERROR bits too, didn't think of adding those. –  Mike Trusov Nov 19 '12 at 21:59
    
This seems to have the same issue as sysobjects-'category'... 'is_ms_shipped' is 0 for all tables at the time the trigger is fired :( –  Mike Trusov Nov 19 '12 at 22:53

Use sysobjects to check if the passed name is a system table:


CREATE TRIGGER [CreateReplTriggers] ON DATABASE 
    FOR CREATE_TABLE 
AS 
BEGIN
    SET NOCOUNT ON
    SET ANSI_PADDING ON
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON

    DECLARE @TABLE_NAME SYSNAME

    SELECT @TABLE_NAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

    if exists (select name from sysobjects where name=@table_name and xtype='U')
    begin
        EXEC sp_CreateReplTrigger @TABLE_NAME
    end
END

This is using the fact that a system table should have an xtype of S and user tables xtype of U.

share|improve this answer
    
This unfortunately doesn't work for tables which are prefixed with 'MS', e.g. MSreplication_subscriptions, there's a 'category' flag however, which is set to 2 for all system tables, but for some reason that gets set at a later stage, so it cannot be used in FOR or AFTER CREATE_TABLE triggers. –  Mike Trusov Nov 19 '12 at 21:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.