How to insert data into another table when the condition is met with trigger-CodePudding

I have these 4 tables:

CREATE TABLE dbo.person
(
    personId INT IDENTITY(1,1) NOT NULL,
    firstName NVARCHAR(30) NOT NULL,
    lastName NVARCHAR(30) NOT NULL,

    CONSTRAINT pkPerson PRIMARY KEY (personId),
);

CREATE TABLE dbo.personRegistration  
(
    person_registrationId INT IDENTITY(1,1) NOT NULL, 
    personId INT,                                   
    firstName NVARCHAR(30) NOT NULL,                
    lastName NVARCHAR(30) NOT NULL, 
    confirmed NCHAR(1) DEFAULT 'N' NOT NULL,                 

    CONSTRAINT pkpersonRegistration PRIMARY KEY (person_registrationId),
    CONSTRAINT fkpersonRegistration FOREIGN KEY (personId) REFERENCES dbo.person (personId)
    CONSTRAINT personConfirmed CHECK (confirmed IN ('Y', 'N'))
);

CREATE TABLE dbo.person_organizationalUnit
(
    personId INT NOT NULL,
    organizationalUnitId INT NOT NULL,

    CONSTRAINT pkorganizationalUnit PRIMARY KEY (personId, organizationalUnitId),
    CONSTRAINT fkperson FOREIGN KEY (personId) REFERENCES dbo.person (personId),
    CONSTRAINT fkorganizationalUnit FOREIGN KEY (organizationalUnitId) REFERENCES dbo.organizatinalUnit(unicOrgUnitId),
);

CREATE TABLE dbo.organizatinalUnit
(
    organizationalUnitId INT IDENTITY(1,1) NOT NULL,
    organizationalUnitName NVARCHAR(130) NOT NULL, 

    CONSTRAINT pkorganizationalUnit PRIMARY KEY (organizationalUnitId)
);

I need to create a trigger which will do that when I add new person in table personRegistration (his personId is set to NULL, and initial value for confirmed is 'N') and when I update personRegistration and set confirmed to 'Y', that person is going to be inserted into table person (value for personId is generated because the personId is an identity column) and the confirmed is going to change it's value to 'Y' and is going to be inserted in table person_organizationalUnit. I have written the trigger but the problem is when I update the personRegistration for more than one person my data double with each update.

CREATE TRIGGER personConfirmed
ON dbo.personRegistration
AFTER UPDATE 
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO dbo.person (firstName, lastName) 
        SELECT 
            firstName, lastName
        FROM 
            dbo.personRegistration

    SET NOCOUNT ON

    DECLARE @idPerson int

    SELECT @idPerson = personId 
    FROM dbo.person

    INSERT INTO dbo.person_organizationalUnit (personId, organizationalUnitId)
        SELECT @idPerson, I.organizationalUnitId
        FROM Inserted AS I
        JOIN dbo.person p ON p.personId = @idPerson
        WHERE confirmed  = 'Y';
END

Data for insert:

INSERT INTO dbo.personRegistration (personId, firstName, lastName, confirmed)
VALUES (NULL, 'John', 'Smith', 'N');

Data for update:

UPDATE dbo.personRegistration
SET confirmed = 'Y'
WHERE personRegistrationId = 1;

CodePudding user response：

Your trigger has a fatal flaw: it does not deeal properly with multiple rows. It is also not using the inserted table in the first INSERT, and instead selecting from the whole original table.

So you need to OUTPUT the identity column from the first insert in order to use it in the second.

Because you don't have the identity column yet, you need to join onto firstName and lastName, which I need not say isn't a very good primary key

CREATE OR ALTER TRIGGER personConfirmed
ON dbo.personRegistration
AFTER UPDATE 
AS

SET NOCOUNT ON

IF NOT UPDATE(confirmed) OR NOT EXISTS (SELECT 1 FROM inserted)
    RETURN;  --early bailout

DECLARE @ids TABLE (personId int PRIMARY KEY, firstName nvarchar(100), lastName nvarchar(100));

INSERT INTO dbo.person (firstName, lastName)
OUTPUT inserted.personId, inserted.firstName, inserted.lastName 
SELECT 
    i.firstName,
    i.lastName
FROM 
    inserted i
WHERE i.confirmed  = 'Y';

INSERT INTO dbo.person_organizationalUnit (personId, organizationalUnitId)
SELECT ids.personId, i.organizationalUnitId
FROM inserted AS i
JOIN @ids ids ON i.firstName = ids.firstName AND i.lastName = ids.lastName;

Ideally, you have some kind of unique primary key on personRegistration. then your trigger would look like this:

CREATE OR ALTER TRIGGER personConfirmed
ON dbo.personRegistration
AFTER UPDATE 
AS

SET NOCOUNT ON

IF NOT UPDATE(confirmed) OR NOT EXISTS (SELECT 1 FROM inserted)
    RETURN;  --early bailout

DECLARE @ids TABLE (personId int PRIMARY KEY, registrationId int);

MERGE dbo.person p
USING (
    SELECT *
    FROM inserted i
    WHERE i.confirmed  = 'Y'
) i
  ON 1 = 0    -- never match
WHEN NOT MATCHED THEN
  INSERT (firstName, lastName)
  VALUES (i.firstName, i.lastName)
OUTPUT inserted.personId, i.organizationalUnitId
INTO @ids (personId, organizationalUnitId)
;

INSERT INTO dbo.person_organizationalUnit (personId, organizationalUnitId)
SELECT ids.personId, i.organizationalUnitId
FROM @ids ids;

We need that funny MERGE because we want to OUTPUT columns that we are not inserting. You can only do this using MERGE.

CodePudding user response：

SQL Server triggers works with sets not single register i do some small changes in your trigger

create TRIGGER dbo.usp_PersonConfirmed ON dbo.personRegistration
AFTER UPDATE 
AS
  BEGIN

   -- create person if not exists
   INSERT INTO dbo.person (firstName, lastName) 
   SELECT firstName, lastName
   FROM dbo.personRegistration p
   where not exists(select * from dbo.Person where firstName = p.firstName 
    and lastName = p.lastName)


   -- create orgonization unit if person dont exist and confirmed is Y
   INSERT INTO dbo.person_organizationalUnit (personId, organizationalUnitId)
   SELECT i.personId, I.organizationalUnitId
   FROM Inserted AS I
   where not exists(select * from dbo.person_organizationalUnit where 
      personId = i.personId)
      and confirmed  = 'Y';

   -- update orgonization unit if person exist and confirmed is Y
   update pou set organizationalUnitId = I.organizationalUnitId
   from dbo.person_organizationalUnit pou
     inner join Inserted AS I on i.personID = pou.personId
   where  i.confirmed  = 'Y';
END