Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Create DML trigger for single table in SQL 2008 and send email alert to group Expand / Collapse
Author
Message
Posted Wednesday, April 10, 2013 2:48 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 80, Visits: 663
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
Post #1441032
Posted Wednesday, April 10, 2013 3:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 12,034, Visits: 11,062
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)
Post #1441041
Posted Wednesday, April 10, 2013 3:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:29 PM
Points: 32,834, Visits: 14,975
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
Post #1441044
Posted Thursday, April 11, 2013 9:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1441312
Posted Thursday, April 11, 2013 10:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:47 AM
Points: 80, Visits: 663
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
Post #1441370
Posted Thursday, April 11, 2013 11:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 12,034, Visits: 11,062
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)
Post #1441400
Posted Thursday, April 11, 2013 12:01 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:29 PM
Points: 32,834, Visits: 14,975
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
Post #1441407
Posted Thursday, April 11, 2013 12:23 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 7:47 AM
Points: 8,296, Visits: 8,750
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
Post #1441420
Posted Thursday, April 11, 2013 12:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:34 PM
Points: 12,755, Visits: 31,122
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1441424
Posted Thursday, April 11, 2013 12:48 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 7:47 AM
Points: 8,296, Visits: 8,750
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
Post #1441434
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse