有关SQL Server触发器的问题 [英] Question about SQL server triggers

问题描述

我有两张桌子，如下图所示，



Table1

ID（INT，PK）

Param1（NVARCHAR（50））



表2

ID（INT，PK）

t1_ID（INT，FK）



表1通过Table1.ID-Table2.t1_ID连接到Table2 。并且关系在删除时级联。



我想要做的是当用户删除Table1中的一行时，Table2中的所有相关行也会删除（这就是我打开的原因）在删除时级联），但是当Table2中删除的行在Table1获取参数时，我也希望这样。



我该怎么做？



提前致谢。



我想我需要更具体地说明我想要完成的事情。



想想3张桌子。像这样; 表



当用户在付款表中删除付款时，金额在PaymentID关联的PaymentItems表中，我们将在PaymentFinanceInformations表的Balance列中添加或减去PaymentType。



我尝试过：

Hi,

I have two tables as shown in below,

Table1
ID (INT, PK)
Param1 (NVARCHAR(50))

Table2
ID (INT, PK)
t1_ID (INT, FK)

Table1 is connected to Table2 via Table1.ID-Table2.t1_ID. And relationship has cascade at delete.

What I want to do is when user deletes a row in Table1 all associated rows in Table2 also deletes(which is why I open the cascade at delete), but I also want that when the rows deleting in Table2 get a Parameter at Table1.

How can I do that?

Thanks in advance.

I think I need to be more specific of what I'm trying to accomplish.

Think about 3 tables. Like this; Tables

When user deletes a payment in Payments table, the amounts in PaymentItems table associated with PaymentID will be added or substracted in Balance column in CompanyFinanceInformations table by PaymentType.

What I have tried:

CREATE TRIGGER DeleteTrigger
ON dbo.Table2
FOR DELETE
AS
BEGIN
     DECLARE @Param1 AS NVARCHAR(50)
     DECLARE @t1_ID AS INT
     SELECT @t1_ID = t1_ID FROM deleted
     SELECT @Param1 = Param1 FROM dbo.Table1 WHERE dbo.Table1.ID = @t1_ID
     -- In here @Param1 always returns NULL
END

我想为Table1写一个触发器，并在这个触发器循环中通过Table2上所有相关联的行。但是我担心这个过程的性能和效率。

I think about write a trigger for Table1, and in this trigger loop through all asscociated rows on Table2. But I have concerns about the performance and efficiency of this proccess.

推荐答案

阅读：如何在SQL Server中使用级联删除？ - 堆栈溢出 [ ^ ]

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