April 10, 2013 at 2:48 pm
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
April 10, 2013 at 3:27 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 10, 2013 at 3:39 pm
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.
April 11, 2013 at 9:05 am
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.
+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
April 11, 2013 at 10:24 am
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
April 11, 2013 at 11:51 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2013 at 12:01 pm
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())
April 11, 2013 at 12:23 pm
krishnavenkat16 (4/10/2013)
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()
[/QUOTE]
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
April 11, 2013 at 12:29 pm
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
April 11, 2013 at 12:48 pm
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
April 11, 2013 at 3:08 pm
Thanks you very much guys...i will give a try with Lowell code and let you know the status...
April 11, 2013 at 5:05 pm
I guess I'd just put and INSTEAD OF trigger on the table that returned the error "Table is Read Only" and which group of people to contact for modifications and then exit.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 12, 2013 at 11:27 am
I'm not able to get this done....here is my table structure...
CREATE TABLE xxxxx (
[ConfigSetID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Version] [smallint] NOT NULL,
[OverrideID] [int] NOT NULL,
[Timestamp] [binary](8) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[xxxxx] ADD DEFAULT ('') FOR [Name]
GO
ALTER TABLE [dbo].[xxxxx] ADD DEFAULT ((0)) FOR [Version]
GO
ALTER TABLE [dbo].[xxxxx] ADD DEFAULT ((0)) FOR [OverrideID]
GO
ALTER TABLE [dbo].[xxxxx] ADD DEFAULT ((0)) FOR [Timestamp]
GO
i need to create a trigger that tracks any changes on this table and send email to team when there is any update.
i'm getting rid of EVENTDATA, trying to implment inserted and deleted tables ...
Please help me on this...
Thanks in advance....
April 12, 2013 at 11:33 am
krishnavenkat16 (4/12/2013)
I'm not able to get this done....here is my table structure...
Look at the example Lowell posted. About all you need to do is change it from a delete trigger to an update. Maybe a few minor modifications here and there but what he posted is 99.9% what you need.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 12, 2013 at 12:10 pm
Yes i have updated it and create trigger in my table. Deleted, inserted the record ...same issue....getting email but it has nothing in it except the subject
Viewing 15 posts - 1 through 15 (of 45 total)
You must be logged in to reply to this topic. Login to reply