-- Disable the trigger. ALTER TABLE trig_example DISABLE TRIGGER trig1GO -- 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
禁用触发器前使用 TABLOCKX 锁begin tran select top 1 * from ttt with (TABLOCKX) 禁用触发器 更新操作 激活触发器 commit
禁用并重新启用触发器 下例使用 ALTER TABLE 的 DISABLE TRIGGER 选项来禁用触发器, 以使正常情况下会违反触发器条件的插入操作得以执行。 然后下例使用 ENABLE TRIGGER 重新启用触发器。 CREATE TABLE trig_example (id INT, name VARCHAR(20), 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 trig_example DISABLE TRIGGER trig1GO
-- 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
select top 1 * from ttt with (TABLOCKX)
禁用触发器
更新操作
激活触发器
commit
禁用并重新启用触发器
下例使用 ALTER TABLE 的 DISABLE TRIGGER 选项来禁用触发器,
以使正常情况下会违反触发器条件的插入操作得以执行。
然后下例使用 ENABLE TRIGGER 重新启用触发器。 CREATE TABLE trig_example
(id INT,
name VARCHAR(20),
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