MSSQL触发器与回滚

69 阅读 0 评论 46 点赞

我是靠谱客的博主高兴小懒虫，最近开发中收集的这篇文章主要介绍MSSQL触发器与回滚，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

--由于 CHECK 约束只能引用定义了列级或表级约束的列，表间的任何约束（在本例中是业务规则）都必须定义为触发器。

--以下示例将创建一个 DML 触发器。如果有人试图将一个新采购订单插入到 PurchaseOrderHeader 表中，此触发器将进行检查以确保供应商具有良好的信用等级。若要获取供应商的信用等级，必须引用 Vendor 表。如果信用等级太低，则显示信息，并且不执行该插入操作。

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).

CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
DECLARE @creditrating tinyint, @vendorid int;
IF EXISTS (SELECT *
FROM Purchasing.PurchaseOrderHeader p
JOIN inserted AS i
ON p.PurchaseOrderID = i.PurchaseOrderID
JOIN Purchasing.Vendor AS v
ON v.BusinessEntityID = p.VendorID
WHERE v.CreditRating = 5
)
BEGIN
RAISERROR ('This vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;

GO
-- This statement attempts to insert a row into the PurchaseOrderHeader table
-- for a vendor that has a below average credit rating.
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.

INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
VALUES(
2
,3
,261
,1652
,4
,GETDATE()
,GETDATE()
,44594.55
,3567.564
,1114.8638);
GO
------------------------------------------------------------------------------
--存储多行或单行插入的运行总计
-- Trigger is valid for multirow and single-row inserts.
USE AdventureWorks2008R2;
GO
CREATE TRIGGER NewPODetail2
ON Purchasing.PurchaseOrderDetail
AFTER INSERT AS
UPDATE PurchaseOrderHeader
SET SubTotal = SubTotal +
(SELECT SUM(LineTotal)
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID
= inserted.PurchaseOrderID)
WHERE PurchaseOrderHeader.PurchaseOrderID IN
(SELECT PurchaseOrderID FROM inserted);
--基于插入类型存储运行总计：在触发器逻辑中使用 @@ROWCOUNT 函数来区分单行插入和多行插入
-- Trigger valid for multirow and single row inserts
-- and optimal for single row inserts.
USE AdventureWorks2008R2;
GO
CREATE TRIGGER NewPODetail3
ON Purchasing.PurchaseOrderDetail
FOR INSERT AS
IF @@ROWCOUNT = 1
BEGIN
UPDATE PurchaseOrderHeader
SET SubTotal = SubTotal + LineTotal
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID

END
ELSE
BEGIN
UPDATE PurchaseOrderHeader
SET SubTotal = SubTotal +
(SELECT SUM(LineTotal)
FROM inserted
WHERE PurchaseOrderHeader.PurchaseOrderID
= inserted.PurchaseOrderID)
WHERE PurchaseOrderHeader.PurchaseOrderID IN
(SELECT PurchaseOrderID FROM inserted)
END;
---------------------------------------------------------------------------------------
--运用具有服务器范围的 DDL 触发器
IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT 'Database Created.'
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
--运用具有数据库范围的 DDL 触发器
USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class = 0 AND name = 'safety')
DROP TRIGGER safety
ON DATABASE;
GO
CREATE TRIGGER safety
ON DATABASE
FOR DROP_SYNONYM
AS
RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
ROLLBACK
GO
DROP TRIGGER safety
ON DATABASE;
GO
--拒绝了作为 login_test 登录名的成员登录 SQL Server 的尝试（如果在此登录名下已运行三个用户会话）。
USE master;
GO
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,
CHECK_EXPIRATION = ON;
GO
GRANT VIEW SERVER STATE TO login_test;
GO
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'login_test'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'login_test' AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = 'login_test') > 3
ROLLBACK;
END;

--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--触发器安全漏洞：
--在有权执行 GRANT CONTROL SERVER 语句的用户（如 sysadmin 固定服务器角色的成员）执行 ALTER TABLE 语句时，为 JohnDoe 授予 CONTROL SERVER 权限
CREATE TRIGGER DDL_trigJohnDoe
ON DATABASE
FOR ALTER_TABLE
AS
GRANT CONTROL SERVER TO JohnDoe ;
GO
--查询数据库上的触发器
SELECT type, name, parent_class_desc FROM sys.triggers
UNION
SELECT type, name, parent_class_desc FROM sys.server_triggers ;
--查询哪个 Transact-SQL 语言事件导致触发了触发器 safety
SELECT TE.*
FROM sys.trigger_events AS TE
JOIN sys.triggers AS T
ON T.object_id = TE.object_id
WHERE T.parent_class = 0
AND T.name = 'safety'
GO

--禁用当前数据库中所有数据库级别的 DDL 触发器
DISABLE TRIGGER ALL ON DATABASE

--禁用服务器实例中所有服务器级别的 DDL 触发器：

DISABLE TRIGGER ALL ON ALL SERVER

--下面的语句可以禁用当前数据库中的所有 DML 触发器：

DECLARE @schema_name sysname, @trigger_name sysname, @object_name sysname ;
DECLARE @sql nvarchar(max) ;
DECLARE trig_cur CURSOR FORWARD_ONLY READ_ONLY FOR
SELECT SCHEMA_NAME(schema_id) AS schema_name,
name AS trigger_name,
OBJECT_NAME(parent_object_id) as object_name
FROM sys.objects WHERE type in ('TR', 'TA') ;

OPEN trig_cur ;
FETCH NEXT FROM trig_cur INTO @schema_name, @trigger_name, @object_name ;

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'DISABLE TRIGGER ' + QUOTENAME(@schema_name) + '.'
+ QUOTENAME(@trigger_name) +
' ON ' + QUOTENAME(@schema_name) + '.'
+ QUOTENAME(@object_name) + ' ; ' ;
print (@sql);
EXEC (@sql) ;
FETCH NEXT FROM trig_cur INTO @schema_name, @trigger_name, @object_name ;
END
GO

-- Verify triggers are disabled. Should return an empty result set.
SELECT * FROM sys.triggers WHERE is_disabled = 0 ;
GO

CLOSE trig_cur ;
DEALLOCATE trig_cur;

-----------------------------------------------------------------------------------
--如果 Customer 表发生更改，以下示例将向指定人员 (MaryM) 发送电子邮件
USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
DROP TRIGGER Sales.reminder2;
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorks2008R2 Administrator',
@recipients = 'danw@Adventure-Works.com',
@body = 'Don''t forget to print a report for the sales force.',
@subject = 'Reminder';
GO

--================================================================================
EXEC sp_helptrigger 'dbo.Atype'
-- 测试某个特定触发器的嵌套级
IF ( (SELECT trigger_nestlevel( object_ID('trg_atype_insert') ) ) > 1 )
RAISERROR('Trigger xyz nested more than 5 levels.',16,-1)

--建立INSERT触发器
IF OBJECT_ID (N'trg_atype_insert') IS NOT NULL
DROP TRIGGER trg_atype_insert;
go
CREATE TRIGGER [trg_atype_insert] ON [dbo].[Atype]
FOR INSERT
AS
IF OBJECT_ID (N'dbo.temp_atype') IS NOT NULL
DROP TABLE dbo.temp_atype;
select 1 from inserted
IF @@ROWCOUNT = 1 --判断是否多项插入
BEGIN
select typeid,fullname,usercode into temp_atype from inserted
end
else
begin
select typeid,fullname+'多项同时插入' as fullname,usercode into temp_atype from inserted
end
go
--建立DELETE触发器
CREATE TRIGGER [TRg_atype_del] ON [dbo].[Atype]
FOR DELETE
AS
--delete from dbo.temp_atype where typeid in (select typeid from deleted)
update dbo.temp_atype
set usercode='已删除···'
where typeid in (select typeid from deleted)
go
--建立UPDATE触发器
-------------------------------------------------
-------------------------------------------------
CREATE TRIGGER [TRg_atype_up] ON [dbo].[Atype]
FOR UPDATE
AS
IF UPDATE(fullname)
BEGIN
--IF OBJECT_ID (N'dbo.temp_atype') IS NOT NULL
--DROP TABLE dbo.temp_atype;
--create table dbo.temp_atype (typeid varchar(50),fullname varchar(100),usercode varchar(100))
insert into dbo.temp_atype (typeid,fullname,usercode)
select typeid,fullname,usercode from INSERTED
END
go
-------------------------------------------------
-------------------------------------------------

--第一个测试INSERT回滚与触发器的关系，回滚触发器执行
--允许将显式值插入表的标识列中
SET IDENTITY_INSERT Atype ON
--测试插入重复间时的错误情况
--delete from dbo.temp_atype
--delete from dbo.Atype where typeid='01000' and branchid='0000100001'

begin tran
select * from dbo.temp_atype where typeid='01000'
select * from dbo.Atype where typeid='01000' and branchid='0000100001'

insert into dbo.Atype (TypeID, Rec, ParID, Leveal, SonNum, SonCount, FullName, Name, UserCode, UFuserCode, NamePY, Comment, Total, Total00, Sysrow, Deleted, TTL00, TTL01, TTL02, TTL03, TTL04, TTL05, TTL06, TTL07, TTL08, TTL09, TTL10, TTL11, TTL12, UpdateTag, ACustom1, ACustom2, ACustom3, ACustom4, ACustom5, BranchID )
select '01000', Rec, ParID, Leveal, SonNum, SonCount, FullName, Name, UserCode, UFuserCode, NamePY, Comment, Total, Total00, Sysrow, Deleted, TTL00, TTL01, TTL02, TTL03, TTL04, TTL05, TTL06, TTL07, TTL08, TTL09, TTL10, TTL11, TTL12, UpdateTag, ACustom1, ACustom2, ACustom3, ACustom4, ACustom5, '0000100001' from dbo.Atype where typeid='00000' and branchid='0000100001'

select * from dbo.temp_atype with(nolock) where typeid='01000'
select * from dbo.Atype with(nolock) where typeid='01000' and branchid='0000100001'

--测试错误时
--select * from dbo.temp_atype11 with(nolock) where typeid='01000'

WAITFOR delay '00:01';
--WAITFOR time '09:30';
--commit tran
rollback tran

select * from dbo.temp_atype where typeid='01000'
select * from dbo.Atype where typeid='01000' and branchid='0000100001'

SET IDENTITY_INSERT Atype OFF

--第二测试UPDATE回滚与触发器的关系，回滚触发器执行
--允许将显式值插入表的标识列中
SET IDENTITY_INSERT Atype ON
--2000下
ALTER TABLE Atype DISABLE TRIGGER TRg_atype_del
alter table atype disable trigger trg_atype_insert

delete from dbo.Atype where typeid='11001' and branchid='0000100011'
delete from dbo.Atype where typeid='11001' and branchid='0000100012'
insert into dbo.Atype (TypeID, Rec, ParID, Leveal, SonNum, SonCount, FullName, Name, UserCode, UFuserCode, NamePY, Comment, Total, Total00, Sysrow, Deleted, TTL00, TTL01, TTL02, TTL03, TTL04, TTL05, TTL06, TTL07, TTL08, TTL09, TTL10, TTL11, TTL12, UpdateTag, ACustom1, ACustom2, ACustom3, ACustom4, ACustom5, BranchID )
select '11001', Rec, ParID, Leveal, SonNum, SonCount, FullName, Name, '01', UFuserCode, NamePY, Comment, Total, Total00, Sysrow, Deleted, TTL00, TTL01, TTL02, TTL03, TTL04, TTL05, TTL06, TTL07, TTL08, TTL09, TTL10, TTL11, TTL12, UpdateTag, ACustom1, ACustom2, ACustom3, ACustom4, ACustom5, '0000100011' from dbo.Atype where typeid='00000' and branchid='0000100001'
insert into dbo.Atype (TypeID, Rec, ParID, Leveal, SonNum, SonCount, FullName, Name, UserCode, UFuserCode, NamePY, Comment, Total, Total00, Sysrow, Deleted, TTL00, TTL01, TTL02, TTL03, TTL04, TTL05, TTL06, TTL07, TTL08, TTL09, TTL10, TTL11, TTL12, UpdateTag, ACustom1, ACustom2, ACustom3, ACustom4, ACustom5, BranchID )
select '11001', Rec, ParID, Leveal, SonNum, SonCount, FullName, Name, '02', UFuserCode, NamePY, Comment, Total, Total00, Sysrow, Deleted, TTL00, TTL01, TTL02, TTL03, TTL04, TTL05, TTL06, TTL07, TTL08, TTL09, TTL10, TTL11, TTL12, UpdateTag, ACustom1, ACustom2, ACustom3, ACustom4, ACustom5, '0000100012' from dbo.Atype where typeid='00000' and branchid='0000100002'
--2000下
ALTER TABLE Atype enABLE TRIGGER TRg_atype_del
alter table atype enable trigger trg_atype_insert

begin tran
select * from dbo.temp_atype --where typeid='11000'
select * from dbo.Atype where TypeID='11001' and branchid not in ('0000100001','0000100002')

update dbo.Atype
set FullName='会计科目_修改后',
Total=21
where TypeID='11001' and branchid not in ('0000100001','0000100002')

select * from dbo.temp_atype with(nolock) --where typeid='11001'
select * from dbo.Atype with(nolock) where typeid='11001' -- and branchid='0000100001'

WAITFOR delay '00:01';
--WAITFOR time '09:30';
--commit tran
rollback tran

select * from dbo.temp_atype --where typeid='11000'
select * from dbo.Atype where typeid='11001' --and branchid='0000100001'

SET IDENTITY_INSERT Atype OFF

--第三测试DELETE回滚与触发器的关系，回滚触发器执行
--允许将显式值插入表的标识列中
SET IDENTITY_INSERT Atype ON
--禁用当前数据库中所有数据库级别的 DDL 触发器
--DISABLE TRIGGER ALL ON DATABASE
--或
--DISABLE TRIGGER dbo.TRg_atype_del ON dbo.Atype
--2000下
ALTER TABLE Atype DISABLE TRIGGER TRg_atype_del

delete from dbo.Atype where typeid='11001' and branchid='0000100011'
delete from dbo.Atype where typeid='11001' and branchid='0000100012'
insert into dbo.Atype (TypeID, Rec, ParID, Leveal, SonNum, SonCount, FullName, Name, UserCode, UFuserCode, NamePY, Comment, Total, Total00, Sysrow, Deleted, TTL00, TTL01, TTL02, TTL03, TTL04, TTL05, TTL06, TTL07, TTL08, TTL09, TTL10, TTL11, TTL12, UpdateTag, ACustom1, ACustom2, ACustom3, ACustom4, ACustom5, BranchID )
select '11001', Rec, ParID, Leveal, SonNum, SonCount, FullName, Name, '01', UFuserCode, NamePY, Comment, Total, Total00, Sysrow, Deleted, TTL00, TTL01, TTL02, TTL03, TTL04, TTL05, TTL06, TTL07, TTL08, TTL09, TTL10, TTL11, TTL12, UpdateTag, ACustom1, ACustom2, ACustom3, ACustom4, ACustom5, '0000100011' from dbo.Atype where typeid='00000' and branchid='0000100001'
insert into dbo.Atype (TypeID, Rec, ParID, Leveal, SonNum, SonCount, FullName, Name, UserCode, UFuserCode, NamePY, Comment, Total, Total00, Sysrow, Deleted, TTL00, TTL01, TTL02, TTL03, TTL04, TTL05, TTL06, TTL07, TTL08, TTL09, TTL10, TTL11, TTL12, UpdateTag, ACustom1, ACustom2, ACustom3, ACustom4, ACustom5, BranchID )
select '11001', Rec, ParID, Leveal, SonNum, SonCount, FullName, Name, '02', UFuserCode, NamePY, Comment, Total, Total00, Sysrow, Deleted, TTL00, TTL01, TTL02, TTL03, TTL04, TTL05, TTL06, TTL07, TTL08, TTL09, TTL10, TTL11, TTL12, UpdateTag, ACustom1, ACustom2, ACustom3, ACustom4, ACustom5, '0000100012' from dbo.Atype where typeid='00000' and branchid='0000100002'
--启用
ALTER TABLE Atype ENABLE TRIGGER TRg_atype_del
---------------------------------------------------------------------
begin tran
select * from dbo.temp_atype --where typeid='11000'
select * from dbo.Atype where TypeID='11001' and branchid not in ('0000100001','0000100002')

delete from dbo.Atype where typeid='11001' and branchid='0000100011'
delete from dbo.Atype where typeid='11001' and branchid='0000100012'

select * from dbo.temp_atype with(nolock) --where typeid='11001'
select * from dbo.Atype with(nolock) where typeid='11001' -- and branchid='0000100001'

WAITFOR delay '00:01';
--WAITFOR time '09:30';
--commit tran
rollback tran

select * from dbo.temp_atype --where typeid='11000'
select * from dbo.Atype where typeid='11001' --and branchid='0000100001'
--------------------------------------------------------------------------
--或
--------------------------------------------------------------------------
begin tran
select * from dbo.temp_atype --where typeid='11000'
select * from dbo.Atype where TypeID='11001' and branchid not in ('0000100001','0000100002')

delete from dbo.Atype where typeid='11001'

select * from dbo.temp_atype with(nolock) --where typeid='11001'
select * from dbo.Atype with(nolock) where typeid='11001' -- and branchid='0000100001'

WAITFOR delay '00:01';
--WAITFOR time '09:30';
--commit tran
rollback tran
select * from dbo.temp_atype --where typeid='11000'
select * from dbo.Atype where typeid='11001' --and branchid='0000100001'
------------------------------------------------------------------------------
SET IDENTITY_INSERT Atype OFF

--====================================================================================
------------------------------------------------------------------------------
IF OBJECT_ID (N'test_hg') IS NOT NULL
DROP proc test_hg;
go
create proc test_hg
as
begin
--第一个测试INSERT回滚与触发器的关系，回滚触发器执行
--允许将显式值插入表的标识列中
SET IDENTITY_INSERT Atype ON
delete from dbo.temp_atype
delete from dbo.Atype where typeid='01000' and branchid='0000100001'

begin tran
select * from dbo.temp_atype where typeid='01000'
select * from dbo.Atype where typeid='01000' and branchid='0000100001'

select * from dbo.temp_atype with(nolock) where typeid='01000'
select * from dbo.Atype with(nolock) where typeid='01000' and branchid='0000100001'

--测试错误时
select * from dbo.temp_atype11 with(nolock) where typeid='01000'

WAITFOR delay '00:01';
--WAITFOR time '09:30';
--commit tran
if @@error<>0 rollback tran
else commit tran

select * from dbo.temp_atype where typeid='01000'
select * from dbo.Atype where typeid='01000' and branchid='0000100001'

SET IDENTITY_INSERT Atype OFF

end
go
--delete from dbo.temp_atype
exec test_hg

--这样直接引用INSERTED和DELETED是错误的
insert into dbo.PtypeSize (PtypeId, SizeID)
values ('0000100005',1)
go
select * from inserted
go
delete from dbo.PtypeSize
where PtypeId='0000100005' and SizeID=1
go
select * from deleted
go