触发器相关
1.查看所有的触发器
select * from sysobjects where xtype = 'tr'
2.查询某张表的所有触发器
select * from sysobjects where xtype = 'tr' and parent_obj = object_id('table_name')
3.怎么判断这个触发器存在呢?判断存在后删除
if exists (select * from sysobjects where name = 'test_trigger ' and type= 'TR ')
drop trigger dbo.test_trigger
4. 以下是删除一个数据库中所有的触发器
--触发器
DECLARE @STRING VARCHAR(8000)
WHILE EXISTS(SELECT NAME FROM SYSOBJECTS WHERE XTYPE='TR')
BEGIN
SELECT @STRING='DROP TRIGGER '+NAME+ CHAR(13) FROM SYSOBJECTS WHERE XTYPE='TR'
EXEC(@STRING)
END
GO
5.删除一个表的所有触发器
稍微修改一下即可
--触发器
DECLARE @STRING VARCHAR(8000)
WHILE EXISTS(SELECT NAME FROM SYSOBJECTS WHERE XTYPE='TR' and name=你的表)
BEGIN
SELECT @STRING='DROP TRIGGER '+NAME+ CHAR(13) FROM SYSOBJECTS WHERE XTYPE='TR' and name=你的表
EXEC(@STRING)
END
GO