Click here to Skip to main content
14,638,192 members
Home » Discussions » Database
   

Database

 
AnswerRe: Question Regarding SQL Parameter Query Pin
ZurdoDev31-Mar-20 9:49
mveZurdoDev31-Mar-20 9:49 
AnswerRe: Question Regarding SQL Parameter Query Pin
Victor Nijegorodov1-Apr-20 1:35
MemberVictor Nijegorodov1-Apr-20 1:35 
AnswerRe: Question Regarding SQL Parameter Query Pin
Richard Deeming1-Apr-20 2:32
mveRichard Deeming1-Apr-20 2:32 
QuestionTrigger keeps inserting duplicates Pin
greatwhite131-Mar-20 7:48
Membergreatwhite131-Mar-20 7:48 
AnswerRe: Trigger keeps inserting duplicates Pin
ZurdoDev31-Mar-20 9:51
mveZurdoDev31-Mar-20 9:51 
GeneralRe: Trigger keeps inserting duplicates Pin
greatwhite131-Mar-20 13:51
Membergreatwhite131-Mar-20 13:51 
GeneralRe: Trigger keeps inserting duplicates Pin
ZurdoDev31-Mar-20 15:44
mveZurdoDev31-Mar-20 15:44 
AnswerRe: Trigger keeps inserting duplicates Pin
Richard Deeming1-Apr-20 0:44
mveRichard Deeming1-Apr-20 0:44 
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 DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    DELETE
    FROM
        L
    FROM
        dbo.EE_SO_MAILING_LISTS As L
    WHERE
        Exists
        (
            SELECT 1
            FROM dbo.import_new_serviceorganization As SO
            INNER JOIN deleted As D
            ON D.new_agentId = SO.ID
            WHERE SO.new_agentId = L.PARTNER
            And D.new_email = L.EMAIL_ADDRESS
        )
    ;
END
GO




"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

RantRe: Trigger keeps inserting duplicates Pin
Mycroft Holmes1-Apr-20 12:45
professionalMycroft Holmes1-Apr-20 12:45 
QuestionHow to calculate the daily and weekly averages using sql server or alternate sql softwares Pin
Barath835730-Mar-20 1:57
MemberBarath835730-Mar-20 1:57 
RantRe: How to calculate the daily and weekly averages using sql server or alternate sql softwares Pin
ZurdoDev30-Mar-20 2:12
mveZurdoDev30-Mar-20 2:12 
AnswerRe: How to calculate the daily and weekly averages using sql server or alternate sql softwares Pin
ZurdoDev31-Mar-20 9:52
mveZurdoDev31-Mar-20 9:52 
AnswerRe: How to calculate the daily and weekly averages using sql server or alternate sql softwares Pin
Mycroft Holmes31-Mar-20 12:50
professionalMycroft Holmes31-Mar-20 12:50 
QuestionCreate a new Primary Key on survey table Pin
learning_new25-Feb-20 12:01
Memberlearning_new25-Feb-20 12:01 
AnswerRe: Create a new Primary Key on survey table Pin
Nathan Minier28-Feb-20 6:38
professionalNathan Minier28-Feb-20 6:38 
AnswerRe: Create a new Primary Key on survey table Pin
Eddy Vluggen28-Feb-20 9:48
professionalEddy Vluggen28-Feb-20 9:48 
AnswerRe: Create a new Primary Key on survey table Pin
David Mujica2-Mar-20 3:56
MemberDavid Mujica2-Mar-20 3:56 
GeneralRe: Create a new Primary Key on survey table Pin
learning_new3-Mar-20 4:28
Memberlearning_new3-Mar-20 4:28 
QuestionIndexing question Pin
Super Lloyd5-Feb-20 13:35
MemberSuper Lloyd5-Feb-20 13:35 
AnswerRe: Indexing question Pin
Eddy Vluggen5-Feb-20 15:09
professionalEddy Vluggen5-Feb-20 15:09 
GeneralRe: Indexing question Pin
Super Lloyd5-Feb-20 17:38
MemberSuper Lloyd5-Feb-20 17:38 
GeneralRe: Indexing question Pin
Eddy Vluggen6-Feb-20 2:50
professionalEddy Vluggen6-Feb-20 2:50 
AnswerRe: Indexing question Pin
Jörgen Andersson5-Feb-20 22:28
professionalJörgen Andersson5-Feb-20 22:28 
AnswerRe: Indexing question Pin
Richard Deeming6-Feb-20 0:17
mveRichard Deeming6-Feb-20 0:17 
Questionintegrity constraint Pin
Tara112-Feb-20 6:23
MemberTara112-Feb-20 6:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.