SQL Clone
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
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3367 Visits: 1290
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
SSC Guru
SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)

Group: General Forum Members
Points: 111976 Visits: 18273
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 Modens 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 Guru
SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)

Group: Administrators
Points: 247341 Visits: 19791
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
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77722 Visits: 14499
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
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3367 Visits: 1290
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
SSC Guru
SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)

Group: General Forum Members
Points: 111976 Visits: 18273
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 Modens 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 Guru
SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)SSC Guru (247K reputation)

Group: Administrators
Points: 247341 Visits: 19791
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
Tom Thomson
Tom Thomson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39153 Visits: 12880
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
SSC Guru
SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)

Group: General Forum Members
Points: 137194 Visits: 41515
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!
Tom Thomson
Tom Thomson
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39153 Visits: 12880
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