Trigger keeps inserting duplicates

greatwhite1

I cannot figure out why my trigger keeps inserting duplicates. Do anyone see what might might be causing it.

/\*\*\*\*\*\* Object:  Trigger \[dbo\].\[update\_ee\_so\_email\]    Script Date: 3/31/2020 10:34:21 AM \*\*\*\*\*\*/
SET ANSI\_NULLS ON
GO
SET QUOTED\_IDENTIFIER ON
GO


ALTER TRIGGER \[dbo\].\[update\_ee\_so\_email\]
   ON  \[dbo\].\[import\_new\_soemailsubmission\]

   AFTER INSERT , UPDATE, Delete
AS 

BEGIN

Declare @new\_agentId as uniqueidentifier --varchar(10)
Declare @new\_email as varchar(50)
Declare @agentid as varchar(10)

Select  @agentid = SO.new\_agentId, @new\_email = new\_email
FROM inserted SE join import\_new\_serviceorganization SO ON SE.new\_agentId = SO.ID

 join app\_users a on a.user\_name = 'SO-' + SO.new\_agentid

IF @agentid is not null 
Begin

INSERT INTO EE\_SO\_MAILING\_LISTS (\[PARTNER\], EMAIL\_ADDRESS,STATEMENT\_FLAG, REFRESH\_DATE)
Values(@agentid, @new\_email, 'N', GetDate())


END

Else if exists (Select \* from deleted) 
Begin

Select  @new\_agentId = new\_agentId, @new\_email = new\_email
		FROM deleted

	 Select @agentid =new\_agentId From import\_new\_serviceorganization Where id  = @new\_agentId
	  
	

	  Delete from EE\_SO\_MAILING\_LISTS where PARTNER = @agentid and EMAIL\_ADDRESS  = @new\_email
End


END

ZurdoDev

You can't treat triggers like this. Triggers will often have multiple records in the inserted and deleted tables so you can't use variables.

Social Media - A platform that makes it easier for the crazies to find each other. Everyone is born right handed. Only the strongest overcome it. Fight for left-handed rights and hand equality.

greatwhite1

How are you suppose to do them?

ZurdoDev

Depends on what you were trying to do but using variables will never work when there’s more than one record in the inserted or deleted tables.

Social Media - A platform that makes it easier for the crazies to find each other. Everyone is born right handed. Only the strongest overcome it. Fight for left-handed rights and hand equality.

Richard Deeming

As well as the issue of multiple affected records, your trigger is trying to handle all three DML operations (INSERT, UPDATE, and DELETE). But the code only seems to be handling the INSERT and DELETE cases.

For INSERT, there will be records in the inserted virtual table, but none in the deleted virtual table;
For DELETE, there will records in deleted, but none in inserted;
For UPDATE, there will be records in both;

Use the inserted and deleted Tables - SQL Server | Microsoft Docs[^] It would almost certainly be simpler to have separate queries for each operation. Try something like this:

CREATE OR ALTER TRIGGER [dbo].[insert_ee_so_email]
ON [dbo].[import_new_soemailsubmission]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO dbo.EE\_SO\_MAILING\_LISTS 
(
    \[PARTNER\], 
    EMAIL\_ADDRESS,
    STATEMENT\_FLAG, 
    REFRESH\_DATE
)
SELECT
    SO.new\_agentId,
    I.new\_email,
    'N',
    GetDate()
FROM
    inserted As I
    INNER JOIN dbo.import\_new\_serviceorganization As SO
    ON SO.ID = I.new\_agentId
WHERE
    Exists
    (
        SELECT 1
        FROM dbo.app\_users As A
        WHERE A.user\_name = 'SO-' + SO.new\_agentId
    )
;

END
GO
CREATE OR ALTER TRIGGER [dbo].[update_ee_so_email]
ON [dbo].[import_new_soemailsubmission]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

If Updated(new\_email)
BEGIN
    UPDATE
        L
    SET
        EMAIL\_ADDRESS = I.new\_email
    FROM
        dbo.import\_new\_serviceorganization As SO
        INNER JOIN inserted As I
        ON I.new\_agentId = SO.ID
        INNER JOIN deleted As D
        ON D.new\_agentId = SO.ID
        INNER JOIN dbo.EE\_SO\_MAILING\_LISTS As L
        ON L.PARTNER = SO.new\_agentId
        And L.EMAIL\_ADDRESS = D.new\_email
    ;
END;

END
GO
CREATE OR ALTER TRIGGER [dbo].[delete_ee_so_email]
ON [dbo].[import_new_soemailsubmission]
AFTER D

Mycroft Holmes

IMHO you are creating a nightmare for your successor by implementing business rules in a trigger. He/she is going to ask where the hell are all these emails coming from? And will spend the next #n hours/days searching for the code that generates the emails. When he/she does eventually think to look into triggers they will heap curses on your name. Triggers should only be used for audit purposes not business rules.

Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP