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;