SQL Server Trigger to Send E-mail

First, if SQL Mail isn't enabled and a profile hasn't been created, we must do so.

   1:  --// First, enable SQL SMail

   2:  use master

   3:  go

   4:  sp_configure 'show advanced options',1

   5:  go

   6:  reconfigure with override

   7:  go

   8:  sp_configure 'Database Mail XPs',1

   9:  go

  10:  reconfigure

  11:  go

  12:   

  13:  --//Now create the mail profile. CHANGE @email_address,@display_name and @mailserver_name VALUES to support your environment

  14:  EXECUTE msdb.dbo.sysmail_add_account_sp

  15:  @account_name = 'DBMailAccount',

  16:  @email_address = 'sqlserver@domain.com',

  17:  @display_name = 'SQL Server Mailer',

  18:  @mailserver_name = 'exchangeServer'

  19:   

  20:  EXECUTE msdb.dbo.sysmail_add_profile_sp

  21:  @profile_name = 'DBMailProfile'

  22:   

  23:  EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

  24:  @profile_name = 'DBMailProfile',

  25:  @account_name = 'DBMailAccount',

  26:  @sequence_number = 1 ;

Now that SQL will support sending e-mails, let's create the sample table. This is not a useful or well designed table by any means -- it's just a simple example table:

   1:  CREATE TABLE dbo.inventory (

   2:  item varchar(50),

   3:  price money

   4:  )

   5:  GO

Now that SQL mail and the table are setup, we will create a trigger that does the following:

1. Creates an AFTER INSERT trigger named expensiveInventoryMailer on the inventory table. This means that the trigger will be executed after the data has been entered.
2. Checks for items being entered that have a price of $1000 or more
3. If there is a match, an email is sent using the SQL Mail profile we used above.

   1:  CREATE TRIGGER expensiveInventoryMailer ON dbo.inventory AFTER INSERT AS

   2:   

   3:  DECLARE @price money

   4:  DECLARE @item varchar(50)

   5:   

   6:  SET @price  = (SELECT price FROM inserted)

   7:  SET @item = (SELECT item FROM inserted)

   8:   

   9:  IF @price >= 1000

  10:  BEGIN

  11:  DECLARE @msg varchar(500)

  12:  SET @msg = 'Expensive item "' + @item + '" entered into inventory at $' + CAST(@price as varchar(10)) + '.'

  13:  --// CHANGE THE VALUE FOR @recipients

  14:  EXEC msdb.dbo.sp_send_dbmail @recipients=N'manager@domain.com', @body= @msg,  @subject = 'SQL Server Trigger Mail', @profile_name = 'DBMailProfile'

  15:  END

  16:  GO

The only way to test a trigger is to add actual data, so let's do that here:

insert into inventory (item,price) values ('Vase',100)
insert into inventory (item,price) values ('Oven',1000)

Your email should arrive very quickly.
If it doesn't, check the SQL Server mail log in SQL Management Studio by running

SELECT 'sysmail_sentitems' as TableName, * FROM msdb.dbo.sysmail_sentitems

SELECT 'sysmail_faileditems' as TableName, * FROM msdb.dbo.sysmail_faileditems

SELECT 'sysmail_allitems' as TableName, * FROM msdb.dbo.sysmail_allitems

SELECT 'sysmail_unsentitems' as TableName, * FROM msdb.dbo.sysmail_unsentitems

SELECT 'sysmail_mailattachments' as TableName, * FROM msdb.dbo.sysmail_mailattachments

Notes:

1. We can find the triggered values by selecting from trigger table. Eg. (SELECT price FROM inserted)
2. Trigger only applicable for INSERTED and DELETED.
3. For UPDATED, use this

   1:  create TRIGGER expensiveInventoryMailer ON dbo.a_inventory after UPDATE AS

   2:   

   3:  if (update (price) or update (item))

   4:  BEGIN

   5:  --Your works here

   6:  END