我的一个库中很多表上都有update的触发器,请问我如何写个语句来禁用或启动库里的所有update触发器。写一个循环?
select all trigger form sys.
disable all trigger
update all table
enable all trigger ????
select all trigger form sys.
disable all trigger
update all table
enable all trigger ????
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+' ALTER TABLE '+NAME+' DISABLE TRIGGER ALL' from sysobjects tb
where type='u'
and exists (select 1 from sysobjects tr
where tb.id=tr.parent_obj
and tr.type='tr'
)exec( @sql)
--开启所有表所有触发器declare @sql varchar(8000)
set @sql=''
select @sql=@sql+' ALTER TABLE '+NAME+' ENABLE TRIGGER ALL' from sysobjects tb
where type='u'
and exists (select 1 from sysobjects tr
where tb.id=tr.parent_obj
and tr.type='tr'
)exec( @sql)
from sysobjects a,sysobjects b
where b.parent_obj = a.id
and a.xtype = 'U'
and b.xtype = 'Tr'然后批量之行
--禁用所有表所有触发器
exec sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'--开启所有表所有触发器
exec sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'