?
Solved

Trigger to email all changes on a table to an email address

Posted on 2011-05-06
46
Medium Priority
?
504 Views
Last Modified: 2012-05-11
Hi there, I need to create a trigger that would email all changes in a table (update, insert, drop etc.) to a specified email account so that we can sort of keep an audit trail of who is doing what on a db. is this possible? thanks.
0
Comment
Question by:eggster34
  • 13
  • 12
  • 9
  • +3
46 Comments
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35705669
Yes, you can do it.
Configure DB Mail.
Create trigger on table for all operation.
In trigger get row by from table inserted/updated/deleted.
send mail using sp_senddbmail
0
 

Author Comment

by:eggster34
ID: 35706365
@PatelAlpesh it would be great if you could elaborate or show me step by step how to create and run the trigger, especially how to include a datestamp if possible.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 35707966
The first link provided is a detailed walk-through of setting this up - it goes through all the steps Patel lists, and shows you how to do each one. Have you taken a look at that link?
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35711183
0
 

Author Comment

by:eggster34
ID: 35730495
thanks, but the part I needed help with is the command syntax for emailing the trigger info to myself.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35731474
What part are you having trouble with. The example in the link
http://www.datasprings.com/resources/articles-information/creating-email-triggers-in-sql-server-2005
is quite detailed. You want to find the code that start with:
-- Create Trigger with name 'CustomerUpdateMail'
What exactly do you want to accomplish. What have you tried so far. What were the result and/or error messages.
0
 

Author Comment

by:eggster34
ID: 35733202
to start with , the example is for an update, however I need a trigger that would email me whenever a new record is added to the table and email me all the fields in the added records (there are over 40). I'm new to SQL, so I don't know almost anything and that's why I need some hand holding. simply pasting an insufficient link therefore is not enough.
0
 

Author Comment

by:eggster34
ID: 35734666
I created this trigger on a table that is updated by another trigger on the original table; mirroring any updates, inserts or deletions on the original table to this table: however whenever I activate the below trigger, it does not allow the original table to be updated by my site!

-- Create Trigger with name 'tr.Changes'
CREATE TRIGGER Changes

ON dbo.tbAD_Change_History

FOR INSERT
AS

 
   -- holds the body of the email
   declare @message varchar(2000)
 
   -- holds the old customer name which has been changed
   declare @ChangeID varchar(10)
   declare @ChangeAddress varchar (50)
   declare @PeopleID varchar (10)
   declare @PostingDate varchar (10)
   declare @TransactionStatus varchar (10)
   declare @TransactionDate varchar (10)
   declare @TransactionChangeID varchar (10)

   SET @ChangeID = (SELECT ChangeId FROM INSERTED)
   SET @ChangeAddress = (SELECT Line1 FROM INSERTED)
   SET @PeopleID = (SELECT iID FROM INSERTED)
   SET @PostingDate = (SELECT dPostedDate FROM INSERTED)
   SET @TransactionStatus = (SELECT sTransStatus FROM INSERTED)
   SET @TransactionDate   = (SELECT dTransDate FROM INSERTED)
   SET @TransactionChangeID = (SELECT iTransChangeId FROM INSERTED)
 
          SET @message = 'Change ID= ' + @ChangeID +
          ' Address= ' + @ChangeAddress +
          ' ID= ' + @PeopleID +
          ' Posting Date= ' + @PostingDate +
          ' Type= ' + @TransactionStatus +
          ' Date= ' + @TransactionDate +
              ' Initiating Change= ' + @TransactionChangeID


EXEC msdb.dbo.sp_send_dbmail
@recipients=N'eggster@eggster.com',
@body= @message,
@importance ='High',
@subject ='Change Notification',
@profile_name ='eggster-smtp';

GO
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35734683
You really should not be sending email from within a TRIGGER, that is very bad practice.
0
 

Author Comment

by:eggster34
ID: 35734707
this is what the other "experts" have been suggesting so far. (by the way that link does NOT work, its syntax is not correct, at least for sql 2005 and cause a myriad of errors, I had to spend time on google to find the correct syntax for a trigger).
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35735352
<< it does not allow the original table to be updated by my site>>
Can you still update the tbAD_Change_History when the trigger is active. Did you test the sp_send_dbmail procedure. Are there any error messages you are getting.

<<by the way that link does NOT work, its syntax is not correct>>
That's true. The correct insert statements are:
INSERT INTO [CustomerInfo]([FIRST_NAME],[MIDDLE_NAME],[LAST_NAME]) VALUES ('Amjad','Ali','Leghari')
 INSERT INTO [CustomerInfo]([FIRST_NAME],[MIDDLE_NAME],[LAST_NAME]) VALUES ('Ali','Raza','Shaikh')

Open in new window

and the "set body" line in the trigger should be this:
SET @body = 'Customer with ID=' + @CustomerID + ' has been updated with previous First Name is ' + @CustomerName + ' and the new First Name is ' + @CustomerNewName

Open in new window



<<this is what the other "experts" have been suggesting so far.>>
Sorry, but you specifically asked "I need to create a trigger that would email all changes in a table". You will get a delay in your updates, and might not be able to update the data when there is a problem with the mail.
An alternative is to add an "emailedat" column to the tbAD_Change_History table. This field in null initially. Then you run a procedure every hour (for example) that loops through all the records where emailedat = null, and mails the changes. This way you won't have the delay at the insert, and you can still update when there is a problem with sending the mail.
0
 

Author Comment

by:eggster34
ID: 35736302
There are 2 tables, table 1 named tbAD_Change is the original table , tbAD_Change_History is the table I created which gets updated by 3 triggers (one for update, one for insert and one for delete) on the tbAD_Change table.

When I activate this trigger I created above, it is the original table that stops updating for some very strange reason.

If I insert rows manually via sql manager I do receive the emails and neither xp sendmail nor db mail cause any problems.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35736383
So when you update tbAD_Change manually in sql manager, it doesn't update the table, and doesn't return an error message?
Is your nested trigger option on? See:
http://www.devx.com/tips/Tip/30031
Only set the nested trigger option, not the recursive.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35737317
>>this is what the other "experts" have been suggesting so far<<
It that is really the case and I doubt it, then they are wrong.  It is a very bad idea to send emails from a TRIGGER, however you want to explain it away.

But as always the best way to learn is by doing it once.  And hopefully you will only have to do it once.

Good luck.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35737381
<<It is a very bad idea to send emails from a TRIGGER>>
I suppose that depends on the situation. Suppose it is a table that is only updated a few times a week, is it still a very bad idea? There are other options, but they probably require more coding and configuration. So the trigger might be the optimal choice between effort and stability/performance.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35737548
Absolutely.  If the TRIGGER is to send out emails for your kid's birthday party once a year, then that is fine, anything else and you are simply asking for trouble.  But again the best way to find out is to do it once.  It will leave an indelible mark on your brain that should help you decide the correct approach next time.
0
 

Author Comment

by:eggster34
ID: 35737872
@Nicobo , I can manually update the table through SQL manager, no problems there, it's just that I cannot update it using my scripts, which work when the trigger is disabled.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35737939
Well sorry to keep reminding you about this dark period in your life :-)
But when you use database mail it is asynchronous. So I'm curious what kind of problems we can expect when we use this from a trigger.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35737975
My previous comment was @acperkins.
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35738019
<<I cannot update it using my scripts>>
What kind of scripts are those. I've had triggers messing up my Access frontend once, because the History table also had an identity field. Access tried to find the record it had just inserted by checking for the last identity value inserted. But that was the identity on the history table, not the original table. Can you provide the create statements for both tables and both triggers?
0
 

Author Comment

by:eggster34
ID: 35739019
my nested triggers are on.
0
 

Author Comment

by:eggster34
ID: 35739282
hi, I nested this portion of my email trigger into my original trigger that was set to populated the history table, however I receive a blank email whenever there's a new insertion into the table. I'm sure my syntax is wrong, what is the correct syntax to use when creating the @message ?

 -- holds the body of the email
   declare @message varchar(2000)
 
   -- holds the old customer name which has been changed
   declare @ChangeID varchar(10)
   declare @ChangeAddress varchar (50)
   declare @PeopleID varchar (10)
   declare @PostingDate varchar (10)
   declare @TransactionStatus varchar (10)
   declare @TransactionDate varchar (10)
   declare @TransactionChangeID varchar (10)

   SET @ChangeID = (SELECT ChangeId FROM INSERTED)
   SET @ChangeAddress = (SELECT Line1 FROM INSERTED)
   SET @PeopleID = (SELECT iID FROM INSERTED)
   SET @PostingDate = (SELECT dPostedDate FROM INSERTED)
   SET @TransactionStatus = (SELECT sTransStatus FROM INSERTED)
   SET @TransactionDate   = (SELECT dTransDate FROM INSERTED)
   SET @TransactionChangeID = (SELECT iTransChangeId FROM INSERTED)
 
          SET @message = 'Change ID= ' + @ChangeID +
          ' Address= ' + @ChangeAddress +
          ' ID= ' + @PeopleID +
          ' Posting Date= ' + @PostingDate +
          ' Type= ' + @TransactionStatus +
          ' Date= ' + @TransactionDate +
              ' Initiating Change= ' + @TransactionChangeID


EXEC msdb.dbo.sp_send_dbmail
@recipients=N'eggster@eggster.com',
@body= @message,
@importance ='High',
@subject ='Change Notification',
@profile_name ='eggster-smtp';

GO
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35740114
>> I'm sure my syntax is wrong<<
It will only support an INSERT/UPDATE/DELETE that affects one row.  In other words, if the INSERT/UPDATE/DELETE affects more than one row, you will only pick up the data from the "last" row.

Contrary to popular belief TRIGGERs are fired once per statement and not once per row.  This is a big difference and a trap that most inexperienced SQL developers fall into every time.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35740156
Also, if this TRIGGER is fired by a DELETE than the INSERTED table will be empty.

>>so that we can sort of keep an audit trail of who is doing what on a db<<
How were you planning on getting the "who" ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35740476
For a cheesy solution to a problem that should not exist, consider doing something like this (correct the obvious typo of xCreate):
xCREATE TRIGGER dbo.trg_YourTriggerNameGoesHere ON dbo.YourTableNameGoesHere

FOR INSERT, UPDATE, DELETE

AS

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'eggster@eggster.com',
-- @body= @message, 
@importance ='High', 
@subject ='Change Notification',
@profile_name ='eggster-smtp',
@query = 'SELECT ChangeId, Line1, iID, dPostedDate, sTransStatus, dTransDate, iTransChangeId FROM INSERTED',
@execute_query_database = 'YourDatabaseNameGoesHere',
@exclude_query_output = 1

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 35741579
>>so that we can sort of keep an audit trail of who is doing what on a db<<

To me an audit trail is generally something more permanent.  It usually involves saving information to a db table.  So that information can be used for reporting/mining or in some cases to reconstruct data at a given point in time.  Email isn't really suitable for that purpose.  

Plus I'm a little curious why you need instant notification of every single action that takes place?

... Just my $0.02

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35742911
>>To me an audit trail is generally something more permanent.  It usually involves saving information to a db table. <<
Absolutely and I am glad you brought that up.  Email is such a lame and over-used method.
0
 

Author Comment

by:eggster34
ID: 35743031
my updates, deletions and inserts are already being recorded in a separate, _history table, so there IS an already present, solid audit trail.

all I need is a way to email myself the inserts, deletes and updates in a readable format. if I use xp sendmail my script I posted above works somewhat ok, but if I use dbmail it doesn't. please correct my version of the script rather than adding stuff yourselves since I intend to have an email that shows something like:

"Hello, here are the changes on the db:

Change ID=
Address=
ID=
Posting Date=
Type=
Date=           "

after the = or : signs, the data I queried from the db must show up, and I'm sure this could be done with dbmail but I just need you experts to point me to how. many thanks again for all your responses and attention :)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35743543
>> however whenever I activate the below trigger, it does not allow the original table <<
>> to be updated by my site! <<
Sounds suspiciously like an error occurred somewhere.  According to the docs that might result in canceling the update:

Because triggers execute within a transaction, a failure at any level of a set of nested triggers cancels the entire transaction, and all data modifications are rolled back...

>> I receive a blank email whenever there's a new insertion into the table. <<
My guess would be null values.  A non null string + NULL usually produces a null. Since your sql isn't not anticipating nulls, if any of the @variables are null -  @message could easily end up being NULL too. That'd would probably result in a blank message.

ie SELECT 'foo'+ NULL AS ThisProducesNull, 'foo'+ ISNULL(NULL, '') ThisIsNotNull


>> Contrary to popular belief TRIGGERs are fired once per statement and not once per row.  
>>  In other words, if the INSERT/UPDATE/DELETE affects more than one row, you will only pick up >> the data from the "last" row.

That's the bigger problem.  If there are multiple changes in a single statement - you'll only be emailed about one of them.   I really can't think of a good way to handle all rows. You'd either need a big loop with string concatenation,  or worse, a cursor.  Neither option is pretty or recommended.  So the less I say about it the better.

Again, just my $0.02
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35744254
@acperkins: Did you test that trigger. The help says this about the @query parameter:
Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.
So I would be surprised if you could use 'from inserted' there.
0
 

Author Comment

by:eggster34
ID: 35745264
so how can I prevent the null items messing up my email?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35745315
>> ie SELECT 'foo'+ NULL AS ThisProducesNull, 'foo'+ ISNULL(NULL, '') ThisIsNotNull <<
IF nulls are the cause, use the isnull() or coalesce() function to replace nulls with something else, such as an empty string.

0
 
LVL 52

Expert Comment

by:_agx_
ID: 35745321
ie   isnull(@variable, 'use this string instead when its null')

http://msdn.microsoft.com/en-us/library/ms184325.aspx

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35746018
_agx_,

>>I really can't think of a good way to handle all rows.<<
I gave one approach, but I don't believe it ws understood.


Nicobo,

>>@acperkins: Did you test that trigger. <<
Of course I did.  Did you?

>> so local variables in the script calling sp_send_dbmail are not available to the query.<<
You do not need to use local variables.

>>So I would be surprised if you could use 'from inserted' there. <<
Why not test it, you may be amazed.

But it is still by no means a perfect solution.  It just avoids the need to loop or use a CURSOR.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35746978
>> I gave one approach, but I don't believe it ws understood. <<
Oh, yeah (@query...). It definitely missed by me ;-)
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35747183
When I execute this:
use EE --your db here!
CREATE TABLE test(ID int IDENTITY(1,1) NOT NULL, Name varchar(10) NOT NULL)

go
create TRIGGER trg_test ON test
FOR INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'test@ee.nl', --your mail here!
-- @body= @message, 
@importance ='High', 
@subject ='Change Notification',
@profile_name ='test',
@query = 'SELECT * from INSERTED',
@execute_query_database = 'EE', --your db here!
@exclude_query_output = 1

go
insert into test(Name) select 'one' union select 'two'

go
drop trigger trg_test
drop table test

Open in new window

I don't get any mail. When I change
@query = 'SELECT * from INSERTED',

Open in new window

to
@query = 'SELECT 1',

Open in new window

I do get an email with this text in it:
-----------
          1

(1 rows affected)
I'm afraid this is because sp_send_dbmail executes the query in a seperate session, and INSERTED is only valid in the trigger, not in this separate session.

I did this in SQL 2008 R2 because I have only an Express version of 2005 running. So I'm not sure about the results in 2005.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35747745
and INSERTED is only valid in the trigger, not in this separate session.
wouldn't some sort of error occur? I'm not near a sql box...  but what happens if you @query from a permanent table - any change?

@query = 'SELECT * from somePermanentTable', ... 

Open in new window

0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35747864
<<wouldn't some sort of error occur?>>
Good question. Nothing in the "Database Mail Log", nothing in the "Event log". I'm curious too where this error is.

<<but what happens if you @query from a permanent table >>
I tried some clever code that constructs a in clause from the INSERTED table in the trigger and uses this to query the new records from the test table. But that gave me a dead lock. Even with an AFTER INSERT trigger. So I'm afraid the best option is to process the records from the INSERTED table in the trigger and add that to the mail.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35747915
>> I tried some clever code that constructs a in clause from the INSERTED table <<

well I was curious if it worked from _any_ table.  so I was trying to eliminate INSERTED from the equation entirely. My thinking was: if it works with a permanent table that suggests the issue *is* with session/scoping. But if it fails too, the problem is something else ..
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35747970
I still think it's a bad idea ;-) ... but I'm curious where the problem lies
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 35749020
Since this works (although completely silly):
use EE --your db here!
CREATE TABLE test1(ID int IDENTITY(1,1) NOT NULL, Name varchar(10) NOT NULL)
CREATE TABLE test2(ID int IDENTITY(1,1) NOT NULL, Name varchar(10) NOT NULL)

insert into test2(Name) select 'one' union select 'two'

go
create TRIGGER trg_test1 ON test1
FOR INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'test@ee.com', --your mail here!
@importance ='High', 
@subject ='Change Notification',
@profile_name ='test', --your profile here!
@query = 'SELECT * from test2',
@execute_query_database = 'EE', --your db here!
@exclude_query_output = 1

go
insert into test1(Name) select 'three' union select 'four'

go
drop trigger trg_test1
drop table test1
drop table test2

Open in new window

I 'm quite positive the session/scoping is reason the "...FROM INSERTED" doesn't work.

But this works:
use EE --your db here!
CREATE TABLE test(ID int IDENTITY(1,1) NOT NULL, Name varchar(10) NOT NULL)

go
create TRIGGER trg_test ON test
FOR INSERT, UPDATE, DELETE
AS
declare @message as varchar(8000) = ''
select @message = @message + CHAR(13) + CHAR(10)+ convert(varchar(10),ID) + '/' + Name from inserted order by id
set @message = SUBSTRING(@message,3,len(@message))

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'test@ee.com', --your mail here!
@importance ='High', 
@subject ='Change Notification',
@body = @message,
@profile_name ='test' --your profile here!

go
insert into test(Name) select 'one' union select 'two'

go
drop trigger trg_test
drop table test

Open in new window

Sends me a mail with this body:
2/two
1/one
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35751513
My apologies and I stand very much corrected.  When I tested it, I did it with a permanent table and not the INSERTED table.  Tremendous egg on face and why this site is such a great place.

Sorry for the confusion.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35774599
@Nicobo - Yeah, that seems to prove it.

So.. are we back to looping/cursors as the only option for multiple records then?
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 35775198
<<So.. are we back to looping/cursors as the only option for multiple records then?>>
I used this line in my example:
select @message = @message + CHAR(13) + CHAR(10)+ convert(varchar(10),ID) + '/' + Name from inserted order by id

Open in new window

instead of a cursor. This puts all the data in one string. It should have isnull() added to it to prevent null values from messing up the result. When used with @body_format= 'HTML' you could create an email that has a table with the data.
0
 

Author Closing Comment

by:eggster34
ID: 35891134
thanks.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question