CREATE trigger dbo.TR_CONSTIT_SECURITY_ATTRIBUTE_DELETE on dbo.CONSTIT_SECURITY_ATTRIBUTE after delete not for replication
as begin
set nocount on;
declare @ID nchar(36);
declare @OBJECT_ID int;
declare @NAME NVARCHAR(100);
declare DELETED_CURSOR cursor local fast_forward for
select
replace(convert(nchar(36),ID),'-','_') AS IDSTRING ,NAME
from
DELETED;
open DELETED_CURSOR;
fetch next from
DELETED_CURSOR
into
@ID,@NAME;
while @@fetch_status = 0 begin
set @OBJECT_ID = null;
select
@OBJECT_ID = [OBJECT_ID]
from
SYS.OBJECTS
where
[NAME] = 'V_CONSTIT_SECURITY_ATTR_' + @ID and
[TYPE] = 'V';
delete from dbo.IDSETREGISTER WHERE DBOBJECTNAME='V_CONSTIT_SECURITY_ATTR_' + @ID;
declare @SQL nvarchar(max);
if @OBJECT_ID is not null begin
set @SQL = 'drop view dbo.V_CONSTIT_SECURITY_ATTR_' + @ID;
exec (@SQL);
end
fetch next from
DELETED_CURSOR
into
@ID,@NAME;
end
close DELETED_CURSOR;
deallocate DELETED_CURSOR;
end
|
Trigger Definition
