SQL Server使用触发器并摆脱ON DELETE CASCADE [英] SQL Server using triggers and geting rid of ON DELETE CASCADE

问题描述

我有2个表，A和B。

表B的外键指向表A的主键。表B的外键具有ON DELETE CASCADE，因此从A删除记录时，也会删除B的对应记录。

Table B has a foreign key pointing to primary key of table A. The foreign key on table B has ON DELETE CASCADE, so the corresponding records from B is deleted when a record from A is deleted.

我的要求是跟踪历史表中所有添加/更新/删除的记录。我在每个表上都有触发器，将记录插入到历史表（AHistories和BHistories表）中。

My requirement is to track all added/updated/deleted records in history tables. I have trigger on each table to insert the records into history tables(AHistories and BHistories tables).

我不喜欢ON DELETE CASCADE删除记录的命令。触发器A在触发器B之后执行，所以我必须变通才能将AHistory的ID放入BHistory记录中。

I do not like the order ON DELETE CASCADE deletes the records. Trigger A is executed after trigger B, so I have to work around to get the ID of AHistory into BHistory record.

我想摆脱ON DELETE CASCADE和在触发器A中对B的记录执行Delete，然后将已删除的B的记录插入那里的BHistories。

I am wanting to get rid of ON DELETE CASCADE and perform Delete on the records of B in trigger A then insert the deleted record of B into BHistories there.

为了演示这个想法，我简化了案例，但是我有还有一些表具有指向表A中主键的外键。个人而言，我想指定顺序以及删除级联时的操作。

To demonstrate the idea, I made the case simple, but I have a few more tables that have a foreign key pointing to the primary key in table A. Personally, I would like if I can specify the order and what I do on delete cascade.

这是不是很臭？谢谢任何意见。

Does this stink as an approach? Any comments are appreciated.

推荐答案

与触发器一样糟糕，但有时它们是实现复杂业务需求的唯一方法。在下面的示例中，我将做以下操作： PK_ID 引用主键列。

As bad as triggers are but sometimes they are the only way to implement complex business requirements. I would do something as follows in the following example PK_ID refers to Primary Key Column.

CREATE TRIGGER tr_Table_A_InsteadOfDelete
ON dbo.TableA
INSTEAD OF DELETE
AS
BEGIN
  SET NOCOUNT ON;

BEGIN TRANSACTION;

    -- Insert into History Table from TableB

    INSERT INTO TABLE_B_History
    SELECT B.*
    FROM TableB B INNER JOIN deleted d
    ON B.PK_ID = d.PK_ID

    -- Delete rows from TableB

      DELETE FROM TableB
        WHERE EXISTS (SELECT 1 
                      FROM deleted 
                      WHERE PK_ID = TableB.PK_ID) 

    -- Insert into History Table from TableA

    INSERT INTO TABLE_A_History
    SELECT A.*
    FROM TableA A INNER JOIN deleted d
    ON A.PK_ID = d.PK_ID

    -- Delete rows from TableA

      DELETE FROM TableA
        WHERE EXISTS (SELECT 1 
                      FROM deleted 
                      WHERE PK_ID = TableA.PK_ID) 

COMMIT TRANSACTION;

END

这篇关于SQL Server使用触发器并摆脱ON DELETE CASCADE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！