Link to home
Start Free TrialLog in
Avatar of M.L. Martin
M.L. MartinFlag for United States of America

asked on

Combine two triggers to avoid double notification

I have two triggers that send a notification via sp_send_dbmail (SQL Server 2016). The triggers are sending a notification based on a change to two specific fields. My issue is both triggers need to do a 'for insert' action. The triggers are firing based on a form submit in an ASP.Net/C# page. Both triggers update the database but I believe the second trigger causes a validation error on the asp.net page. Additionally, the notification is not going out for the second trigger though the database is being updated. The error message displayed in the ASPC page is: At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients". Mail (Id: 52) queued. If I change the second trigger from this:

ALTER TRIGGER [dbo].[test_finaldraft_notify_HRBP_RESOURCE_REQUEST_SUBMITTED] on
[dbo].[Req_submitted]
for insert

TO this:
ALTER TRIGGER [dbo].[test_finaldraft_notify_HRBP_RESOURCE_REQUEST_SUBMITTED] on
[dbo].[Req_submitted]
for update

I don't get the error message in the aspx page. But what I do get is a double notification that goes out to one email address. If I remember correctly this happens because the database engine says that an update is also a type of insert. So it sends notification two times but the message and record ID number are identical. With that said, is there a way I can combine these into one trigger?

Trigger 1
--------------
ALTER TRIGGER [dbo].[Notify_submitted] on
[dbo].[Req_submitted]
for insert
as
 
 Begin
     declare 
        @req_submitted_key int,
      @Submitted_by_email varchar(50),
      @submitted_to_email varchar(50),
      @submitted_by_comment varchar(300),
      @Payband_compensation varchar(300),
      @Pay_band_minimum float,
      @Pay_band_medium float,
      @Pay_band_maximum float,

        @Submitted_to_finance_approver_email varchar(50),
        @approved_denied  varchar(100),
      @Denied_approval varchar(50),
      @approved_finance varchar(100),
      @denied_finance varchar(50),
      @finance_comments varchar(50),
      @Job_posted      varchar(3),
        --@TaskAsction_A1_completed   varchar(100),
      @Job_postedby_email     varchar(50),
         @Message                    nvarchar(4000);

      select @req_submitted_key = s.req_submitted_key,
            @Submitted_by_email = S.Submitted_by_email,
           @submitted_to_email = S.submitted_to_email,
          @submitted_by_comment = s.submitted_by_comment,
          @Payband_compensation = s.Payband_compensation,
          @Pay_band_minimum = s.Pay_band_minimum,
          @Pay_band_medium = s.Pay_band_medium,
          @Pay_band_maximum = s.Pay_band_maximum,
             @Submitted_to_finance_approver_email = s.Submitted_to_finance_approver_email,
          @approved_denied = s.approved_denied,
          @Denied_approval = s.Denied_approval,
          @approved_finance  = s.approved_finance, 
          @denied_finance = denied_finance,
          @finance_comments = s.finance_comments,
             @Job_posted  = s.Job_posted,
          @Job_postedby_email = s.Job_postedby_email
             --@TaskAsction_A1_completed = s.TaskAsction_A1_completed
      from inserted s;

    ----  select @Message=concat(cast(@submitted_to_email as nvarchar(100)), NCHAR(13) + NCHAR(10), @req_submitted_key, NCHAR(13) + NCHAR(10), @submitted_by_comment, NCHAR(13) + NCHAR(10), @Payband_compensation, NCHAR(13) + NCHAR(10), N'  A Potential Opening-Role Requisition request has been submitted to you. Use this link and the request number included to review the request. https://echopar.azurewebsites.net/Manage_request.aspx');
     
   select @Message=concat('Email: ', cast(@submitted_by_email as nvarchar(100)), NCHAR(13) + NCHAR(10), 'Request Number: ', @req_submitted_key, NCHAR(13) + NCHAR(10), 'Opinion on Role: ',@submitted_by_comment, NCHAR(13) + NCHAR(10), 'Payband Thoughts: ', @Payband_compensation, NCHAR(13) + NCHAR(10), N'  Notification: You have successfully submitted a Potential New Hire Request. Use this link and the request number included to check the staus of the request. https://sampletest.net/xxxxxx_yyyy.aspx');

 ------Send notification 
          if update(submitted_to_email)
      
     EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'parecho',
      @recipients = @Submitted_by_email,
      @subject = 'Echo System Notification',
      @body = @Message;
          

        
        
        ;
End

Trigger 2
--------------
ALTER TRIGGER [dbo].[test_finaldraft_notify_HRBP_RESOURCE_REQUEST_SUBMITTED] on
[dbo].[Req_submitted]
for update
as
 
 Begin
     declare 
        @req_submitted_key int,
      @Submitted_by_email varchar(50),
      @submitted_to_email varchar(50),
      @submitted_by_comment varchar(300),
      @Payband_compensation varchar(300),
      @Pay_band_minimum float,
      @Pay_band_medium float,
      @Pay_band_maximum float,

        @Submitted_to_finance_approver_email varchar(50),
        @approved_denied  varchar(100),
      @Denied_approval varchar(50),
      @approved_finance varchar(100),
      @denied_finance varchar(50),
      @finance_comments varchar(50),
      @Job_posted      varchar(3),
        --@TaskAsction_A1_completed   varchar(100),
      @Job_postedby_email     varchar(50),
         @Message                    nvarchar(4000);

      select @req_submitted_key = s.req_submitted_key,
            @Submitted_by_email = S.Submitted_by_email,
           @submitted_to_email = S.submitted_to_email,
          @submitted_by_comment = s.submitted_by_comment,
          @Payband_compensation = s.Payband_compensation,
          @Pay_band_minimum = s.Pay_band_minimum,
          @Pay_band_medium = s.Pay_band_medium,
          @Pay_band_maximum = s.Pay_band_maximum,
             @Submitted_to_finance_approver_email = s.Submitted_to_finance_approver_email,
          @approved_denied = s.approved_denied,
          @Denied_approval = s.Denied_approval,
          @approved_finance  = s.approved_finance, 
          @denied_finance = denied_finance,
          @finance_comments = s.finance_comments,
             @Job_posted  = s.Job_posted,
          @Job_postedby_email = s.Job_postedby_email
             --@TaskAsction_A1_completed = s.TaskAsction_A1_completed
      from inserted s;

    ----  select @Message=concat(cast(@submitted_to_email as nvarchar(100)), NCHAR(13) + NCHAR(10), @req_submitted_key, NCHAR(13) + NCHAR(10), @submitted_by_comment, NCHAR(13) + NCHAR(10), @Payband_compensation, NCHAR(13) + NCHAR(10), N'  A Potential Opening-Role Requisition request has been submitted to you. Use this link and the request number included to review the request. https://');
     
   select @Message=concat('Email: ', cast(@submitted_to_email as nvarchar(100)), NCHAR(13) + NCHAR(10), 'Request Number: ', @req_submitted_key, NCHAR(13) + NCHAR(10), 'Opinion on Role: ',@submitted_by_comment, NCHAR(13) + NCHAR(10), 'Payband Thoughts: ', @Payband_compensation, NCHAR(13) + NCHAR(10), N'  Notification: A Potential Opening-Role Requisition request has been submitted to you. Use this link and the request number included to review the request. https://sampletest.net/aaaaaar_xxxxxx_request.aspx?req_submitted_key=',@req_submitted_key);

 ------Send notification 
          if update(submitted_to_email)
      
     EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'parecho',
      @recipients = @submitted_to_email,
      @subject = 'Echo System Notification',
      @body = @Message;
          

        
        
        ;
End


Open in new window

Avatar of M.L. Martin
M.L. Martin
Flag of United States of America image

ASKER

*Type: Meant to type ASPX page.
triggers are for separate events
one for insert, one for update

when you insert a record, only one should fire
when you update a record, only one should fire

not both...

and in both triggers, you can use "inserted" table that holds new/updated data...
Avatar of Máté Farkas
Just put a sign into the subject of notification email to make difference between insert and update trigger:
select @Message=concat('INSERT Email: ', cast(@submitted_to_email as nvarchar(100)) ...

Open in new window

select @Message=concat('UPDATE Email: ', cast(@submitted_to_email as nvarchar(100)) ...

Open in new window

So you will see that the same trigger fired twice or each trigger fired.
INSERT trigger may work in case of UPDATE only when you update the primary key of the table.
Thanks guys. Very helpful @Máté FarkasUser generated image 
@Mate

what does this mean?

INSERT trigger may work in case of UPDATE only when you update the primary key of the table

if we have a table X(id, val) where id is PK
and update it like

update X set id=id+1, val=10 where value=0

or

update X set id=id+1 where id between 10 and 20

why insert trigger fire here?
I expect only update trigger will run...
do you mean auto identity or something like that here?
INSERT trigger may work in case of UPDATE only when you update the primary key of the table.
Quite correct.  SQL handles an UPDATE of a PK column(s) by doing a DELETE of the old key and an INSERT of the new key. 
why I dont see this

Quite correct.  SQL handles an UPDATE of a PK column(s) by doing a DELETE of the old key and an INSERT of the new key.  
User generated image
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4be6d3e68a54f2ca9bfa132726cfb011

why I dont see this
I don't know which trigger(s) SQL does or doesn't fire for an update of a pk column(s), I just know that it does a delete followed by an insert.  You can see that if you look at the log records that are generated by such an update.
You can see that if you look at the log records 

I am not a dba and I never looked at what's happening behind the scenes :)

is there any article related to this behaviour?

looks very strange to me, for example if we have calculated fields or some clustered indexes
or if we have huge records, deleting and inserting does not make sense to me when there is an update...
I don't see how it could work any other way.

You're changing index keys.  For the physical index to reflect that change, one way or another you have to remove the old value and add the new value.

I wouldn't be surprised if SQL does the same thing for non-clus indexes when a key value changes, but I haven't actually looked at the log records of those in the past to verify that that happens.
You're changing index keys.  For the physical index to reflect that change, one way or another you have to remove the old value and add the new value

we are changing the data, FK or PK or IX or any other column...
it is up to DB Engine what to do behind the scenes, we should not care about those algorithms...
it can delete insert or re-build from scratch, or re allocate records, we should not care...

what we do is, we update record and we should expect only update trigger...
not an delete/insert!

and I dont see any article/documentation about such a weird behaviour...
Evening guys. I had another fire to put out. I will dive into the suggestions this evening and see how far I can get based on your feedback. Thanks for the replies.
The way I'm doing this is the following trigger:

Notify_submitted (User John Smith)

Updates the user that they submitted the record successfully. That trigger uses For insert logic and works fine.

          if update(req_submitted_key)
     
     EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Echo System',
      @recipients = @Submitted_by_email, <-----------------this guy is notified 
      @subject = 'Echo System Notification',
      @body = @Message;

This trigger needs to notify the Manager of John Smith that John Smith has submitted a request. I was trying for update logic.

Notify_Manager

          if update(submitted_date)
     
     EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Echo System',
      @recipients = @submitted_to_email, ,<-----John Smith's Manager
      @subject = 'Echo System Notification',
      @body = @Message;

The issue I have is trigger 2 (Notify_Manager ) sends two identical email notifications. This led me to believe that the SQL Engine handles an update as a Insert and an Update. I have an idea I can try.
This led me to believe that the SQL Engine handles an update as a Insert and an Update. I have an idea I can try. 

see my fiddle...

one update fires one update trigger only, nothing else
maybe you are updating the records twice...

just combine in 1 trigger for update,insert  ( you may need to check After/instead of )

ALTER TRIGGER [dbo].[test_finaldraft_...] on
[dbo].[Req_submitted]
for update,insert


more
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15
see example

How do SQL Server Triggers Work for Insert, Update, Delete and Truncate


https://www.mssqltips.com/sqlservertip/5950/how-do-sql-server-triggers-work-for-insert-update-delete-and-truncate/
Thanks! I do believe combining is the best approach to this. 
The second Trigger is firing before the email recipient can be written to the database so the trigger is failing because of a null value. I've tried to use sp_settriggerorder with no success. The triggers will have to be combined into one trigger. There is no other option. 
The field for both triggers are identical so I'm hoping to use the ELSE condition to make both email actions take place.

 select @Message=concat('Email: ', cast(@submitted_by_email as nvarchar(100)), NCHAR(13) + NCHAR(10), 'Request Number: ', @req_submitted_key, NCHAR(13) + NCHAR(10), 'Opinion on Role: ',@submitted_by_comment, NCHAR(13) + NCHAR(10), 'Payband Thoughts: ', @Payband_compensation, NCHAR(13) + NCHAR(10), N'  Notification: You have successfully submitted a Potential New Hire Request. Use this link and the request number included to check the staus of the request. https://xxxxxxxxxxxxxxxx.net/Manage_request.aspx');

 ------Send notification
          if update(req_submitted_key)
     
     EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Echo System',
      @recipients = @Submitted_by_email,
      @subject = 'Echo System Notification',
      @body = @Message;
         
        ELSE

         select @Message=concat('Email: ', cast(@submitted_to_email as nvarchar(100)), NCHAR(13) + NCHAR(10), 'Request Number: ', @req_submitted_key, NCHAR(13) + NCHAR(10), 'Opinion on Role: ',@submitted_by_comment, NCHAR(13) + NCHAR(10), 'Payband Thoughts: ', @Payband_compensation, NCHAR(13) + NCHAR(10), N'  Notification: A Potential Opening-Role Requisition request has been submitted to you. Use this link and the request number included to review the request. https://xxxxxxxxxxxxxxxx.net/Approveor_deny_request.aspx?req_submitted_key=',@req_submitted_key);

 ------Send notification
          if update(submitvalue)
     
     EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Echo System',
      @recipients = @submitted_to_email,
      @subject = 'Echo System Notification',
      @body = @Message;
Still getting this message but I feel I'm on the right path. Just got to figure out the code to make it one trigger instead of two.

"At lease one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients".
Final Solution needing to combine the actions of two triggers that need to send email notification:

ALTER TRIGGER [dbo].[trg_Notify_submitted_and_Approver] on
[dbo].[Req_submitted]
AFTER INSERT,UPDATE,DELETE
as
 
 Begin

     /*-----------------------------------------------
      Defines local variables to hold the data from the inserted table */
     -----------------------------------------------*/
     declare 
        @req_submitted_key int,
      @Submitted_by_email varchar(50),
      @submitted_to_email varchar(50),
      @submitted_by_comment varchar(300),
      @Payband_compensation varchar(300),
      @Pay_band_minimum float,
      @Pay_band_medium float,
      @Pay_band_maximum float,

        @Submitted_to_finance_approver_email varchar(50),
        @approved_denied  varchar(100),
      @Denied_approval varchar(50),
      @approved_finance varchar(100),
      @denied_finance varchar(50),
      @finance_comments varchar(50),
      @Job_posted      varchar(3),
        --@TaskAsction_A1_completed   varchar(100),
      @Job_postedby_email     varchar(50),
         @Message                    nvarchar(4000);         
       
      /*-----------------------------------------------
       Determine if the action is an Insert, Update. 
       -----------------------------------------------*/
      DECLARE @action char(1);
   
      IF COLUMNS_UPDATED() > 0 -- insert or update
         BEGIN
            IF EXISTS (SELECT * FROM DELETED) 
            SET @action = 'U'; -- Update
         ELSE
          SET @action = 'I'; -- Insert
      END
      ELSE
         SET @action = 'D';  -- Delete ( We will not do anything with the delete
      
      /*-----------------------------------------------
      Get the data from the Inserted data for Both inserts and Updates.
      -----------------------------------------------*/
      select @req_submitted_key = s.req_submitted_key,
            @Submitted_by_email = S.Submitted_by_email,
           @submitted_to_email = S.submitted_to_email,
          @submitted_by_comment = s.submitted_by_comment,
          @Payband_compensation = s.Payband_compensation,
          @Pay_band_minimum = s.Pay_band_minimum,
          @Pay_band_medium = s.Pay_band_medium,
          @Pay_band_maximum = s.Pay_band_maximum,
             @Submitted_to_finance_approver_email = s.Submitted_to_finance_approver_email,
          @approved_denied = s.approved_denied,
          @Denied_approval = s.Denied_approval,
          @approved_finance  = s.approved_finance, 
          @denied_finance = denied_finance,
          @finance_comments = s.finance_comments,
             @Job_posted  = s.Job_posted,
          @Job_postedby_email = s.Job_postedby_email
      from inserted s;

     /*-----------------------------------------------
     Build the message body based on if the action was insert or update
     -----------------------------------------------*/
     IF ( @action = 'I')
     BEGIN
        select @Message=concat('Email: ', cast(@submitted_by_email as nvarchar(100)), NCHAR(13) + NCHAR(10), 
        'Request Number: ', @req_submitted_key, NCHAR(13) + NCHAR(10), 'Opinion on Role: ',
        @submitted_by_comment, NCHAR(13) + NCHAR(10), 'Payband Thoughts: ', 
        @Payband_compensation, NCHAR(13) + NCHAR(10), 
        N'  Notification: You have successfully submitted a Potential New Hire Request. Use this link and the request number included to check the staus of the request. https://xxxxxxxx.oooooo.net/Manage_request.aspx');
      END
      ELSE IF ( @action = 'U')
      BEGIN
          select @Message=concat('Email: ', cast(@submitted_to_email as nvarchar(100)), NCHAR(13) + NCHAR(10), 
         'Request Number: ', @req_submitted_key, NCHAR(13) + NCHAR(10), 'Opinion on Role: ',
         @submitted_by_comment, NCHAR(13) + NCHAR(10), 
         'Payband Thoughts: ', @Payband_compensation, NCHAR(13) + NCHAR(10), 
         N'  Notification: A Potential Opening-Role Requisition request has been submitted to you. Use this link and the request number included to review the request. https://xxxxxxx.ooooooo.net/Approveor_deny_request.aspx?req_submitted_key=',@req_submitted_key);
      END

      /*-----------------------------------------------
      Send notification - If the action is an insert or update and the @req_submitted_key is not null.
      -----------------------------------------------*/
      IF (( @action = 'I' OR  @action = 'u') AND (@req_submitted_key <> NULL)) 
     BEGIN       
        EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Echo System',
        @recipients = @Submitted_by_email,
        @subject = 'Echo System Notification',
        @body = @Message;
     END
          
End

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of M.L. Martin
M.L. Martin
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
what is the accepted solution?

@derrekdeveloper, can you edit your post and format the code...