禁用与启用触发器 收藏 --禁用所有 alter table [tablename] disable trigger all --恢复所有 alter table [tablename] enable trigger all --禁用指定 alter table [tablename] disable trigger [triggername] --恢复指定 alter table [tablename] enable trigger [triggername]
要让本表的所有触发器都不工作 ALTER TABLE [Table] DISABLE TRIGGER All --开启用enable
DISABLE TRIGGER 触发器名 ON 表名; GO
ALTER TABLE trig_example DISABLE TRIGGER trig1
alter table 表名 disable trigger all
查看联机帮助的 alter tableH. 禁用并重新启用触发器 下例使用 ALTER TABLE 的 DISABLE TRIGGER 选项来禁用触发器,以使正常情况下会违反触发器条件的插入操作得以执行。然后下例使用 ENABLE TRIGGER 重新启用触发器。CREATE TABLE trig_example (id INT, name VARCHAR(10), salary MONEY) go -- Create the trigger. CREATE TRIGGER trig1 ON trig_example FOR INSERT as IF (SELECT COUNT(*) FROM INSERTED WHERE salary > 100000) > 0 BEGIN print "TRIG1 Error: you attempted to insert a salary > $100,000" ROLLBACK TRANSACTION END GO -- Attempt an insert that violates the trigger. INSERT INTO trig_example VALUES (1,"Pat Smith",100001) GO -- Disable the trigger. ALTER TABLE trig_example DISABLE TRIGGER trig1 GO -- Attempt an insert that would normally violate the trigger INSERT INTO trig_example VALUES (2,"Chuck Jones",100001) GO -- Re-enable the trigger. ALTER TABLE trig_example ENABLE TRIGGER trig1 GO -- Attempt an insert that violates the trigger. INSERT INTO trig_example VALUES (3,"Mary Booth",100001) GO
--禁用所有
alter table [tablename]
disable trigger all --恢复所有
alter table [tablename]
enable trigger all --禁用指定
alter table [tablename]
disable trigger [triggername] --恢复指定
alter table [tablename]
enable trigger [triggername]
ALTER TABLE [Table] DISABLE TRIGGER All --开启用enable
GO
下例使用 ALTER TABLE 的 DISABLE TRIGGER 选项来禁用触发器,以使正常情况下会违反触发器条件的插入操作得以执行。然后下例使用 ENABLE TRIGGER 重新启用触发器。CREATE TABLE trig_example
(id INT,
name VARCHAR(10),
salary MONEY)
go
-- Create the trigger.
CREATE TRIGGER trig1 ON trig_example FOR INSERT
as
IF (SELECT COUNT(*) FROM INSERTED
WHERE salary > 100000) > 0
BEGIN
print "TRIG1 Error: you attempted to insert a salary > $100,000"
ROLLBACK TRANSACTION
END
GO
-- Attempt an insert that violates the trigger.
INSERT INTO trig_example VALUES (1,"Pat Smith",100001)
GO
-- Disable the trigger.
ALTER TABLE trig_example DISABLE TRIGGER trig1
GO
-- Attempt an insert that would normally violate the trigger
INSERT INTO trig_example VALUES (2,"Chuck Jones",100001)
GO
-- Re-enable the trigger.
ALTER TABLE trig_example ENABLE TRIGGER trig1
GO
-- Attempt an insert that violates the trigger.
INSERT INTO trig_example VALUES (3,"Mary Booth",100001)
GO