CODEWITHSUNDEEP
sqlemailtriggers
sthety
sthety

Reputation: 1

SQL Server Trigger to Send Email with Where Clause

So I am able to easily create a trigger that will send an email after a record is inserted into my LOGS table like below but I can't seem to find anywhere that you can use a where clause. I only want to send out this email when say a new line has column MESSAGE_CODE = 999 is inserted in the LOGS table.

 USE Customers; 

 GO 
 CREATE TRIGGER [dbo].[IMPORT_COMPLETE] ON [dbo].[LOGS] 
 AFTER INSERT 

 AS  
  EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'DB Admin Profile',  
  @recipients = '[email protected]',  
  @body = 'Import Code Inserted',  
  @subject = 'Import Complete';
 END

Upvotes: 0

Views: 443

Answers (1)

nbk
nbk

Reputation: 49375

You can check, if one of the row has messagecode 999

 USE Customers; 
 GO 
 CREATE TRIGGER [dbo].[IMPORT_COMPLETE] ON [dbo].[LOGS] 
 AFTER INSERT 

 AS
 BEGIN
     IF EXISTS (SELECT 1 FROM inserted WHERE MESSAGE_CODE = 999)
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'DB Admin Profile',  
        @recipients = '[email protected]',  
        @body = 'Import Code Inserted',  
        @subject = 'Import Complete';
    END
 END

Upvotes: 0

Related Questions