Trigger to drop table only if there are no records How do I write a DDL DROP_TABLE trigger that will only drop the table if there are no records?
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 &lt; 2  AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
                        and o.name = @oname
                        and s.name = @sname)
    IF @RowCount&gt;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