SQL触发多次插入更新 [英] SQL trigger multiple insert update

问题描述

我一直在研究如何创建可以处理多个更新/插入的TSQL触发器。

I've been researching how to create a TSQL trigger that will handle multiple Update/Inserts.

我们有来自多个来源的数据，我的目标是验证/ p在更新/插入之前纠正该数据。

We have data coming from multiple sources and my goal is to verify/correct that data before updating/inserting.

我编写了一个适用于单行数据的触发器。

I wrote a trigger that works for single rows of data.

我正在努力弄清楚如何处理多行数据。

I'm struggling to figure out how to get it to handle multiple rows of data.

CREATE TRIGGER [dbo].[tr_GPTitleToGov]
ON [dbo].[GoverningPersons]
AFTER INSERT, UPDATE
AS 
BEGIN
SET NOCOUNT ON;

DECLARE @Title1 VARCHAR(15)
DECLARE @UBI VARCHAR(9)
DECLARE @ETPID CHAR(4)
DECLARE @Ident INT

SET @Title1 = (SELECT Title1 FROM INSERTED)
SET @UBI = (SELECT UBI FROM INSERTED)
SET @ETPID = (SELECT [ETPID] FROM [entity] WHERE @UBI = [entity].[UBI])
SET @Ident = (SELECT Ident FROM INSERTED)

IF ((@Title1 = 'Executor') OR  (@Title1 = 'Incorporator'))
    BEGIN
        IF @ETPID IN ('0143', '0147', '0148', '0150', '0152', '0154')
        UPDATE GoverningPersons
        SET [Title1] = 'Executor',
            [Title2] = NULL,
            [Title3] = NULL,
            [Title4] = NULL
        WHERE Ident = @Ident;

        ELSE
        UPDATE GoverningPersons
        SET [Title1] = 'Incorporator',
            [Title2] = NULL,
            [Title3] = NULL,
            [Title4] = NULL
        WHERE Ident = @Ident;
    END
ELSE
    UPDATE GoverningPersons
    SET [Title1] = 'Governor',
        [Title2] = NULL,
        [Title3] = NULL,
        [Title4] = NULL
    WHERE Ident = @Ident;
END

我想让我发疯的是在哪里加入该字段，因此我可以检查数据与另一个表中的数据。

I think what's throwing me is where to join the fields so I can check the data against data in a different table.

我以前从未编写过触发器，因此将不胜感激。

I've never written a trigger before, so any help would be appreciated.

推荐答案

CREATE TRIGGER [dbo].[tr_GPTitleToGov]
ON [dbo].[GoverningPersons]
AFTER UPDATE
AS 
BEGIN
SET NOCOUNT ON;

UPDATE gp
    SET Title1 = CASE
             WHEN i.Title1 IN ('Incorporator','Executor')
                AND e.ETPID IN ('0143', '0147', '0148', '0150', '0152', '0154') THEN 'Executor'
             WHEN i.Title1 IN ('Incorporator','Executor') THEN 'Incorporator'
             ELSE 'Governor'
          END
       ,Title2 = NULL
       ,Title3 = NULL
       ,Title4 = NULL
FROM
    GoverningPersons gp
    INNER JOIN inserted i
    ON gp.Ident  = i.Ident
    LEFT JOIN entity e
    ON i.UBI = e.UBI


END

触发器不每行执行一次，而是基于集合操作，因此整个DML操作仅只执行一次。因此，您需要使用join语句将其与其他任何更新日期一样对待。只有您可以使用特殊的插入的和删除的表。上面是更新触发器的示例，应该为您提供一些指导。

Triggers are NOT executed once per row but rather as a set based operation so executed only ONCE for the entire DML operation. So you need to treat it like any other update date with join statement. Only you can use the special inserted and deleted tables. The above is an example of an update trigger and should provide you with some direction.

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