MSSQL 触发器笔记
SqlServer
2015-07-13
2622
0
DML Trigger 函数
UPDATE()
IF UPDATE(qty) PRINT 'Column qty affected';COLUMNS _UP DATE D()
一、在删除,插入,更新执行触发器
USE TSQL2012; GO IF OBJECT_ID('Sales.tr_SalesOrderDetailsDML', 'TR') IS NOT NULL DROP TRIGGER Sales.tr_SalesOrderDetailsDML; GO CREATE TRIGGER Sales.tr_SalesOrderDetailsDML ON Sales.OrderDetails AFTER DELETE, INSERT, UPDATE AS BEGIN IF @@ROWCOUNT = 0 RETURN; SET NOCOUNT ON; SELECT COUNT(*) AS InsertedCount FROM Inserted; SELECT COUNT(*) AS DeletedCount FROM Deleted; END;
二、使用INSTEAD OF trigger来强制检验业务规则
IF OBJECT_ID('Production.tr_ProductionCategories_categoryname', 'TR') IS NOT NULL DROP TRIGGER Production.tr_ProductionCategories_categoryname; GO CREATE TRIGGER Production.tr_ProductionCategories_categoryname ON Production.Categories INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; IF EXISTS ( SELECT COUNT(*) FROM Inserted AS I JOIN Production.Categories AS C ON I.categoryname = C.categoryname GROUP BY I.categoryname HAVING COUNT(*) > 1 ) BEGIN THROW 50000, 'Duplicate category names not allowed', 0; END; ELSE INSERT Production.Categories ( categoryname , description ) SELECT categoryname , description FROM Inserted; END; GO -- Cleanup IF OBJECT_ID('Production.tr_ProductionCategories_categoryname', 'TR') IS NOT NULL DROP TRIGGER Production.tr_ProductionCategories_categoryname;
0条评论