CODEWITHSUNDEEP
sql-servert-sqltriggers
Tom Possemiers
Tom Possemiers

Reputation: 21

This trigger works for one row, how to make it work on multiple rows?

/* This trigger works for one row, how to make it work on multiple rows ?*/

CREATE TRIGGER SendMail ON contract
AFTER INSERT, UPDATE, DELETE 
AS

SET NOCOUNT ON
IF ( UPDATE(Contract_Status))
Begin

/* This comparison works for one row, how to make it work on multiple rows ?*/

IF ((SELECT Contract_Status FROM INSERTED) <> (select Contract_Status FROM DELETED))
Begin 


DECLARE @body NVARCHAR(MAX) = N'';
SELECT @body += CHAR(13) + CHAR(10) + RTRIM(Contract_Ref_1) FROM inserted;


  EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'AdminProfile',
    @recipients = '[email protected]',
    @body = @body,
    @subject = 'Status Update BELUX_OEM';

End
End

Upvotes: 2

Views: 221

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56725

This is how I would do it:

CREATE TRIGGER SendMail ON contract
AFTER INSERT, UPDATE, DELETE 
AS

SET NOCOUNT ON
IF ( UPDATE(Contract_Status))
Begin

    IF EXISTS(SELECT *
              FROM   INSERTED i
              JOIN   DELETED d ON i.PrimaryKey = d.PrimaryKey
              WHERE  d.Contract_Status <> i.Contract_Status)
    Begin 


    DECLARE @body NVARCHAR(MAX) = N'';
    SELECT @body += CHAR(13) + CHAR(10) + RTRIM(Contract_Ref_1) 
    FROM inserted i
    WHERE NOT EXISTS(SELECT * FROM deleted d
                     WHERE  i.PrimaryKey = d.PrimaryKey
                       AND  i.Constract_Status <> d.Contract_Status)

      EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AdminProfile',
        @recipients = '[email protected]',
        @body = @body,
        @subject = 'Status Update BELUX_OEM';

    End
End

Upvotes: 1

Related Questions