SQL trigger multiple insert update

Question

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

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.

score 5 · Accepted Answer · answered Nov 29 '16 at 23:09

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

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.

I had no idea there was such a thing as a case statement. This is super! — Elaine Powell, Nov 29 '16 at 23:50
@E.Powell A `CASE` _expression_ results in a value. There is no `CASE` _statement_. Sadly, Microsoft doesn't comprehend that and uses terminology at random in their "documentation". — HABO, Nov 30 '16 at 01:33

SQL trigger multiple insert update

1 Answers1

Linked