updatetrigger.sql

来自「简单的SQL Server数据库安装」· SQL 代码 · 共 68 行

CREATE TRIGGER "tSaleOrderMst_UTrig" ON tSaleOrderMst FOR UPDATE AS
SET NOCOUNT ON
/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tCustoms' */
IF UPDATE(customID)
    BEGIN
        IF (SELECT COUNT(*) FROM inserted) !=
           (SELECT COUNT(*) FROM tCustoms, inserted WHERE (tCustoms.ID = inserted.customID))
            BEGIN
                RAISERROR 44446 '不能添加或更改该记录。参照完整性规则要求在表“tCustoms”中含有相关记录。'
                ROLLBACK TRANSACTION
            END
    END

/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tEmployees' */
IF UPDATE(employeeID)
    BEGIN
        IF (SELECT COUNT(*) FROM inserted) !=
           (SELECT COUNT(*) FROM tEmployees, inserted WHERE (tEmployees.ID = inserted.employeeID))
            BEGIN
                RAISERROR 44446 '不能添加或更改该记录。参照完整性规则要求在表“tEmployees”中含有相关记录。'
                ROLLBACK TRANSACTION
            END
    END

/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tEmployees' */
IF UPDATE(employeeID)
    BEGIN
        IF (SELECT COUNT(*) FROM inserted) !=
           (SELECT COUNT(*) FROM tEmployees, inserted WHERE (tEmployees.ID = inserted.employeeID))
            BEGIN
                RAISERROR 44446 '不能添加或更改该记录。参照完整性规则要求在表“tEmployees”中含有相关记录。'
                ROLLBACK TRANSACTION
            END
    END

/* * PREVENT UPDATES IF NO MATCHING KEY IN 'tOperators' */
IF UPDATE(buildID)
    BEGIN
        IF (SELECT COUNT(*) FROM inserted) !=
           (SELECT COUNT(*) FROM tOperators, inserted WHERE (tOperators.ID = inserted.buildID))
            BEGIN
                RAISERROR 44446 '不能添加或更改该记录。参照完整性规则要求在表“tOperators”中含有相关记录。'
                ROLLBACK TRANSACTION
            END
    END

/* * CASCADE UPDATES TO 'tReceiveDtl' */
IF UPDATE(ID)
    BEGIN
       UPDATE tReceiveDtl
       SET tReceiveDtl.SaleOutID = inserted.ID
       FROM tReceiveDtl, deleted, inserted
       WHERE deleted.ID = tReceiveDtl.SaleOutID
    END

/* * CASCADE UPDATES TO 'tSaleOrderDtl' */
IF UPDATE(ID)
    BEGIN
       UPDATE tSaleOrderDtl
       SET tSaleOrderDtl.ID = inserted.ID
       FROM tSaleOrderDtl, deleted, inserted
       WHERE deleted.ID = tSaleOrderDtl.ID
	
	UPDATE tSaleOrderDtl1
	SET tSaleOrderDtl1.ID = inserted.ID
	FROM tSaleOrderDtl1,deleted,inserted 
	WHERE deleted.ID = tSaleOrderDtl1.ID
    END