-1

I have a database trigger called ddl_refresh_views that provides dynamic binding for views (if you alter view V, it refreshes all the views dependent on V, and all the views dependent on those views etc). This triggers functions as intended, but for some reason:

  • It shows up in select * from sys.triggers
  • It does not show up in sys.objects or sys.sysobjects
  • I can delete it through GUI (so I doubt it's a permissions or locking issue)
  • the instruction 'drop trigger ddl_refresh_views' gives me the message "Cannot drop the trigger 'ddl_refresh_views', because it does not exist or you do not have permission"

trigger code below:

create trigger [ddl_refresh_views] on database
for alter_table as
    declare @EventData xml = EVENTDATA()
    declare @TableName varchar(max) = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
    --declare @StartTime datetime = getdate();

    if @TableName not like 'TMP%'
    begin
        set nocount on;
        declare @SequenceID int = 1;
        declare @TableCount int=1;
        declare @TableCountPrevious int=0;
        if object_id('tempdb..#Views') is not null drop table #Views
        create table #Views (ObjectID int, ViewName varchar(max), SequenceID int)
        insert into #Views (ObjectID, ViewName, SequenceID) values (0, @TableName, 0);
        -- print '1. Time (ms): '+convert(varchar(max),datediff(ms, @StartTime, getdate()));

        while @TableCount > @TableCountPrevious
        begin
            set @TableCountPrevious = @TableCount
            declare @ViewName varchar(max);
            declare ViewCursor cursor for (select ViewName from #Views where SequenceID = @SequenceID-1)
            open ViewCursor
            set @TableCountPrevious = (select count(*) from #Views)
            fetch next from ViewCursor into @ViewName;
            while @@FETCH_STATUS = 0
            begin
                insert into #Views (ObjectID, ViewName, SequenceID)
                select v.object_id, v.name, @SequenceID
                from sys.dm_sql_referencing_entities('dbo.'+@ViewName, 'OBJECT') r
                join sys.views v on r.referencing_id = v.object_id
                where v.object_id not in (select ObjectID from #Views)

                update #Views set SequenceID = @SequenceID
                from #Views v
                join sys.dm_sql_referencing_entities('dbo.'+@ViewName, 'OBJECT') r on v.ObjectID = r.referencing_id
                where v.ObjectID in (select ObjectID from #Views)

                fetch next from ViewCursor into @ViewName;
            end;
            close ViewCursor
            deallocate ViewCursor
            set @SequenceID = @SequenceID + 1
            set @TableCount = (select count(*) from #Views)
        end;
        -- print '2. Time (ms): '+convert(varchar(max),datediff(ms, @StartTime, getdate()));
        delete from #Views where SequenceID = 0

        -- loop through the views in SequenceID order
        declare ViewCursor cursor for
            select ViewName from #Views order by SequenceID;
        open ViewCursor
        fetch next from ViewCursor into @ViewName;
        while @@FETCH_STATUS = 0
        begin
            ---- print 'Refreshing view:'+@ViewName
            exec sp_refreshview @ViewName;
            fetch next from ViewCursor into @ViewName;
        end;
        close ViewCursor
        deallocate ViewCursor

        drop table #Views
    end;
    -- print 'Time (ms): '+convert(varchar(max),datediff(ms, @StartTime, getdate()));

go
0

It was a database scoped trigger which may or may not explain why I couldn't see it in sys.objects. This didn't work:

drop trigger ddl_refresh_views

But if I added scope, it did:

drop trigger ddl_refresh_views on database

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.