有关SQL Server触发器的帮助 [英] Help on SQL Server trigger

问题描述

假设我有3张桌子

t1

Nid   name
1    aaa
2    bbb
3    ccc

delT1

Nid    name

t2

Sid    Nid     value
1      1       AAA
2      1       BAC
3      2       CSA

在表 t1 Nid 是主键，它是 t2

现在我想要的是当我从 t1 删除值时，它会自动删除 t2 其中 t1.Nid = t2.Nid 和已删除值 t1 插入 delT1

Now what I want is when I delete value from t1 it automatically deletes all values from t2 where t1.Nid=t2.Nid and a value of deleted t1 get inserted into delT1

如何为此类任务创建触发器？

How can I create a trigger for this type of task?

由于我是sql的新手，请帮助我

Please help me since I am new to sql

推荐答案

在T2将<删除code级联：

Modify the FK in T2 to be ON DELETE CASCADE:

ALTER TABLE T2 DROP CONSTRAINT FK_T1_Nid; <-- your constraint name here
ALTER TABLE T2 ADD CONSTRAINT FK_T1_Nid FOREIGN KEY (Nid)
   REFERENCES T1 (Nid) ON DELETE CASCADE;

然后在T1上创建触发器以将信息推送到delT1：

Then create a trigger on T1 to push the information to delT1:

CREATE TRIGGER TR_T1_D ON T1 FOR DELETE
AS
SET NOCOUNT ON;
INSERT delT1
SELECT Nid, Name
FROM Deleted;

请注意，此触发器阻止您在针对T1的DELETE上使用OUTPUT子句。 BOL说：

Note this trigger prevents you from using an OUTPUT clause on DELETEs against T1. BOL says:

如果在未指定OUTPUT子句的情况下也未指定INTO关键字，则DML操作的目标不能为给定的DML操作定义任何启用的触发器。例如，如果在UPDATE语句中定义OUTPUT子句，则目标表不能具有任何已启用的UPDATE触发器。

If the OUTPUT clause is specified without also specifying the INTO keyword, the target of the DML operation cannot have any enabled trigger defined on it for the given DML action. For example, if the OUTPUT clause is defined in an UPDATE statement, the target table cannot have any enabled UPDATE triggers.

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