Trigger to send an email when ever a table is updated

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dopydb SSCertifiable Points: 6687 More actions · Answer 5

sure i can 😀

create trigger TestStock on dbo.mal_test After insert as

declare @StockCode varchar(10)

declare @Description varchar(10)

set @StockCode =(select StockCode from Inserted)

set @Description =(select Description from Inserted)

begin

declare @msg varchar(500)

set @msg = 'New Item created with a Product Code"' + rtrim(@StockCode) +'"and a description"' + rtrim(@Description) +'".'

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'email@address.com',

@subject = 'Inventory Delete/Create',

@body = @msg

end

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

malachyrafferty (3/30/2010)
sure i can 😀
create trigger TestStock on dbo.mal_test After insert as
declare @StockCode varchar(10)
declare @Description varchar(10)
set @StockCode =(select StockCode from Inserted)
set @Description =(select Description from Inserted)
begin
declare @msg varchar(500)
set @msg = 'New Item created with a Product Code"' + rtrim(@StockCode) +'"and a description"' + rtrim(@Description) +'".'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'email@address.com',
@subject = 'Inventory Delete/Create',
@body = @msg
end

Heh... that's what I thought. If you ever do have more than one entry in the same insert, you'll be the last to know with the code above.

Oddly enough, it actually takes less code to return all of the rows...

CREATE TRIGGER TestStock ON dbo.mal_test AFTER INSERT

AS

DECLARE @msg VARCHAR(MAX)

SELECT @msg = ISNULL(@msg+CHAR(10),'')

+ 'New Item created with a Product Code "' + RTRIM(StockCode) +'" and a description "' + RTRIM(Description) +'".'

FROM Inserted

EXEC msdb.dbo.sp_send_dbmail

@recipients = 'email@address.com',

@subject = 'Inventory Delete/Create',

@body = @msg

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dopydb SSCertifiable Points: 6687 More actions · Answer 7

dopydb

SSCertifiable

Points: 6687

March 30, 2010 at 3:46 pm

#1142766

cheers for that jeff! will come in useful im sure

dopydb SSCertifiable Points: 6687 More actions · Answer 8

Hi jeff

could you tell why this will wait until all updates are notified? i know it does i just dont understand why :w00t:

what is the SELECT @msg = ISNULL purpose?

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

malachyrafferty (3/31/2010)
Hi jeff
could you tell why this will wait until all updates are notified? i know it does i just dont understand why :w00t:
what is the SELECT @msg = ISNULL purpose?

Not sure what you mean about "until all updates are notified". It will include all updates (INSERTS) in the single notification, if that's what you mean.

The ISNULL just keeps the last line from having a linefeed character in it. It's a classic way of doing concatenation.

SQL Server completes the insertion of all rows for a give INSERT statement and then fires the trigger only once for that insert. The trigger I wrote reads all of those rows from the INSERTED table and concatenates them into a single variable with CHAR(10) (a linefeed character) at the end of each "row". Then it sends that whole thing (hundreds of rows if they are inserted in the same INSERT) as a single email.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dopydb SSCertifiable Points: 6687 More actions · Answer 10

thats fine, i understand now!

thanks for getting back to me very much appreciated

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 11

Thank you for the feedback, as well. It's actually a bit of a rare thing compared to the norm.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)