0

How do I write a DDL DROP_TABLE trigger that will only drop the table if there are no records?

0

I posted the question as I had just spent 2 days working this out and have found the answer:

    /****** Object:  DdlTrigger [TR_ProtectCriticalTables]    Script Date: 07/12/2015 09:02:05 ******/
    DROP TRIGGER [TR_ProtectCriticalTables] ON DATABASE
    GO

    /****** Object:  DdlTrigger [TR_ProtectCriticalTables]    Script Date: 07/12/2015 09:02:05 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO



    CREATE TRIGGER [TR_ProtectCriticalTables]
    ON DATABASE
    FOR 
     DROP_TABLE

    AS
    DECLARE @eventData XML,
            @uname NVARCHAR(50),
            @oname NVARCHAR(255),
            @sname nvarchar(255),
            @TABLEName nvarchar(255),
            @otext VARCHAR(MAX),
            @etype NVARCHAR(100),
            @edate DATETIME,
            @sql varchar(max),
            @RowCount bigint
    SET @eventData = eventdata()
    SELECT
            @edate=GETDATE(),
            @uname=@eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
            @oname=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
            @otext=@eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 
                    'VARCHAR(MAX)'),
            @etype=@eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
            @sname=@eventdata.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME')


    ROLLBACK -- have to undo the drop to be able to get row count

    set @RowCount = (SELECT i.rowcnt 
                        FROM sysindexes AS i
                            INNER JOIN sysobjects AS o ON i.id = o.id 
                            inner join sys.tables as t on o.id = t.object_id
                            inner join sys.schemas as s on t.schema_id = s.schema_id
                        WHERE i.indid < 2  AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
                        and o.name = @oname
                        and s.name = @sname)
    IF @RowCount>0
    BEGIN
        DECLARE @err varchar(1000)
        SET @err = 'Table ' + @sname +'.' + @oname  + ' has rows and cannot be dropped. Rows: ' + convert(varchar(255),isnull(@RowCount,-1))+ '. '
        RAISERROR (@err, 16, 1)
        return
    END
    else
    begin

        set @sql = '
        disable trigger [TR_ProtectCriticalTables] on database
        drop table [' + @sname + '].[' + @oname + ']'
        exec(@sql)
        set @sql = '
        enable trigger [TR_ProtectCriticalTables] on database'
        exec(@sql)
    exec(@sql)
    end


    GO

    SET ANSI_NULLS OFF
    GO

    SET QUOTED_IDENTIFIER OFF
    GO

    ENABLE TRIGGER [TR_ProtectCriticalTables] ON DATABASE
    GO
  • I have since found a problem with the above code. If you have the option "Prevent saving changes that require table re-creation" switched off and you alter an empty table, then the DROP element gets rolled back (see top of code) and the recreate fails. If you there are rows, then this will prevent the action. So it is only an issue if you make a new table, then change a data type or drop a column, you lose the table. So, ideas anyone? – F_Face Jan 12 '16 at 14:03
  • Meantime, just leave "Prevent saving changes that require table re-creation" switched on and, if you want to alter a table, use T-SQL instead of the designer. – F_Face Jan 12 '16 at 14:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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