Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create DML trigger for single table in SQL 2008 and send email alert to group


Create DML trigger for single table in SQL 2008 and send email alert to group

Author
Message
Robin35
Robin35
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 1060
Hi,

i have a requirement to Create DML trigger for single table ( check for any updates ) in SQL 2008 and send email alert to group if there is any activity in the table. I have the following the script . This trigger is sending email when there is any activity in table but the output has nothing in it. I guess problem is with @Message statement.
Any help would be greatly appreciated.

Thanks in advance

CREATE TRIGGER [dbo].[triggername]
ON [dbo].[tablename]
AFTER INSERT, UPDATE, DELETE
AS
DECLARE @EventData XML
DECLARE @Message nvarchar(max);
DECLARE @DDLStatement nvarchar(max);
DECLARE @UserName nvarchar(max); -- To hold the user that execute the command
DECLARE @DatabaseName nvarchar(max);

SET @EventData = EVENTDATA()
SET @UserName = @EventData.value( '(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)' );

-- Get the database that the change was to affect
SET @DatabaseName = @EventData.value( '(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(max)' );
SET @DDLStatement = @EventData.value( '(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)' );
SELECT @Message = 'DATABASE: ' + @DatabaseName + CHAR(13)
+ 'EXECUTED BY: ' + @UserName + CHAR(13)
+ '----- BEGIN DDL Statement --------------' + CHAR(13) + CHAR(13)
+ @DDLStatement + CHAR(13) + CHAR(13)
+ '----- END DDL Statement ----------------' + CHAR(13) + CHAR(13)
+ CAST(@EventData as nvarchar(max));
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'sqlserver',
@recipients = 'XXX@.com',
@subject = 'DML change on server name',
@body = @Message
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16525 Visits: 16992
More than likely one of those variables is ending up NULL so when you concatenate them all together the @Message is NULL.

I would recommend NOT sending an email directly from your trigger like this. It makes all DML calls slower because they have to wait on sending an email. It is generally considered a better practice to instead populate a holding table and use service broker to send the emails. This keeps your DML quicker.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35976 Visits: 18726
In 2008, you should have queueing. DB MAil uses Service Broker under the covers, so I wouldn't worry about that.

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

I do think Sean could be right that you have a problem with your @message. I would say build a table, use a column for each part of @message, adn insert there. Then look at that to debug.

Be careful with this type of auditing. You may end up with very, very large amounts of data and emails.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8219 Visits: 14368
Steve Jones - SSC Editor (4/10/2013)
In 2008, you should have queueing. DB MAil uses Service Broker under the covers, so I wouldn't worry about that.

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

+1 Database Mail in 2005+ uses Service Broker so sending an email is analog to inserting a row into a table. The actually sending occurs later, asynchronous to the request to send the email.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Robin35
Robin35
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 1060
the table that we are creating trigger wont be having more updates..i'm not much into coding..please help me on this...any idea and code will be appreciated.

Thanks
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16525 Visits: 16992
krishnavenkat16 (4/11/2013)
the table that we are creating trigger wont be having more updates..i'm not much into coding..please help me on this...any idea and code will be appreciated.

Thanks


So you are building a trigger to handle updates for a table that isn't going to be updated? ;-)

Steve's idea is how I would go about debugging this.
I would say build a table, use a column for each part of @message, adn insert there. Then look at that to debug.


If you need some specific help you will need to post the ddl for the table (create table statement).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: Administrators
Points: 35976 Visits: 18726
To expand, here's what I'd do:

Create table Logger
( myMessage nvarchar(max)
, DDLStatement nvarchar(max)
, UserName nvarchar(max)
, DatabaseName nvarchar(max)
, Timestamp datetime default (getdate())
)
;



Then I'd alter the trigger to insert data into there.

I'd then write a job that executed once an hour or so and if there was new data, email me.

The job/procedure would query the table

select ddlstatement
from logger
where timestamp > dateadd( hh, -1, getdate())



Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10656 Visits: 11989
krishnavenkat16 (4/10/2013)
Hi,

i have a requirement to Create DML trigger for single table ( check for any updates ) in SQL 2008 and send email alert to group if there is any activity in the table. I have the following the script . This trigger is sending email when there is any activity in table but the output has nothing in it. I guess problem is with @Message statement.
Any help would be greatly appreciated.

Thanks in advance

CREATE TRIGGER [dbo].[triggername]
ON [dbo].[tablename]
AFTER INSERT, UPDATE, DELETE
AS
DECLARE @EventData XML
DECLARE @Message nvarchar(max);
DECLARE @DDLStatement nvarchar(max);
DECLARE @UserName nvarchar(max); -- To hold the user that execute the command
DECLARE @DatabaseName nvarchar(max);

SET @EventData = EVENTDATA()

ANd therein lies the problem: This is a DML trigger (AFTER INSERT,UPDATE,DELETE).

EVENTDATA returms data only when called directly from a DDL trigger or a LOGON trigger. Not when called in a DML trigger. So @EventDate will be NULL after that call.

edit: spelling of EVENTDATA

Tom

Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14929 Visits: 38913
here's a DML delete trigger example that is catching a ton of whodunnit information and sending an email;
maybe you can use that as a model?

CREATE TABLE WHATEVER(
WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
DESCRIP VARCHAR(30)
)
GO
CREATE TRIGGER TR_WHATEVER_NOTIFICATIONS
ON WHATEVER FOR DELETE
AS
BEGIN
SET NOCOUNT ON
--gather the information, making sure you get it from the DELETED virtual table, and not the full table
DECLARE @CAPTUREDSTRING VARCHAR(max)

--In this example i want a comma delimited list of important facts about what was inserted.
--using the xml technique to make my comma delimited string.
SELECT @CAPTUREDSTRING = [DeletedItems]
FROM (
SELECT TOP 1 GETDATE()As TheDate,stuff(( SELECT ',' + DESCRIP
FROM INSERTED s2
--WHERE s2.WHATEVERID= s1.WHATEVERID --- must match GROUP BY below
WHERE 1 = 1
ORDER BY DESCRIP
FOR XML PATH('')
),1,1,'') as [DeletedItems]
FROM DELETED s1
GROUP BY s1.WHATEVERID --- without GROUP BY multiple rows are returned
ORDER BY s1.WHATEVERID) myAlias

--now email the results.
declare @htmlbody varchar(max)
set @htmlbody = 'Deleted Item Notification on the Whatever Table '
+ CONVERT( VARCHAR( 20 ), GETDATE(), 113 )
+ '
<P> The following new items were deleted from the table:<P>'
+ @CAPTUREDSTRING
+ '
'

SELECT @htmlbody =
'Deleted Item Notification on the Whatever Table '
+ CONVERT( VARCHAR( 20 ), GETDATE(), 113 )
+ '
<P> Auudit Information On The Event:<P>'
+ 'EventDate :' + CONVERT(VARCHAR(30),getdate() ,112) + '
' + CHAR(13) + CHAR(10)
+ 'DBName :' + DB_NAME() + '
' + CHAR(13) + CHAR(10)
+ 'CurrentUser :' + CURRENT_USER + '
' + CHAR(13) + CHAR(10)
+ 'HostName :' + HOST_NAME() + '
' + CHAR(13) + CHAR(10)
+ 'ApplicationName :' + APP_NAME() + '
' + CHAR(13) + CHAR(10)
+ 'ProcedureName :' + ISNULL(OBJECT_NAME(@@PROCID) ,'') + '
' + CHAR(13) + CHAR(10)
+ 'Userid :' + CONVERT(VARCHAR(30),USER_ID() ) + '
' + CHAR(13) + CHAR(10)
+ 'UserName :' + USER_NAME() + '
' + CHAR(13) + CHAR(10)
+ 'sUserid :' + CONVERT(VARCHAR(30),SUSER_ID() ) + '
' + CHAR(13) + CHAR(10)
+ 'sUserName :' + SUSER_SNAME() + '
' + CHAR(13) + CHAR(10)
+ '[ORIGINAL_LOGIN] :' + ORIGINAL_LOGIN() + '
' + CHAR(13) + CHAR(10)
+ 'net_transport :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('net_transport'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'protocol_type :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('protocol_type'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'auth_scheme :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('auth_scheme'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'local_net_address :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('local_net_address'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'local_tcp_port :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('local_tcp_port'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'client_net_address :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('client_net_address'),'') ) + '
' + CHAR(13) + CHAR(10)
+ 'physical_net_transport :' + CONVERT(VARCHAR(30),ISNULL(ConnectionProperty('physical_net_transport'),'') ) + '
' + CHAR(13) + CHAR(10)
+ '
<P> The following new items were deleted from the table:<P>'
+ @CAPTUREDSTRING --this has the list of deleted values
+ '
'

EXEC msdb.dbo.sp_send_dbmail
@profile_name='Default Mail Account',
@recipients='lowell@somedomain.com',
@subject = 'New Item Notification',
@body = @htmlbody,
@body_format = 'HTML'
END --TRIGGER
GO
INSERT INTO WHATEVER(DESCRIP)
SELECT 'APPLES' UNION
SELECT 'ORANGES' UNION
SELECT 'BANANAS' UNION
SELECT 'GRAPES' UNION
SELECT 'CHERRIES' UNION
SELECT 'KIWI'

--now delete something:
DELETE FROM WHATEVER WHERE DESCRIP IN('CHERRIES','KIWI','STEAK SANDWICH')




Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10656 Visits: 11989
I should have said that you can get much of what you want from the SYSTEM_USER built in function and by using DBCC INPUTBUFFER to populate a table with details of the call made to the server. But you can't get anything with EVENTDATA in a DML trigger.

edit: Also, you probably want to include both CURRENT_USER and SYSTEM_USER in case they are different. SYSTEM_USER is the original login that caused this call; CURRENT_USER is the current security context, which may be ueless if you want the original login but is very useful if you want the current context; often, but not always, they are the same but sometimes they are different.

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search