?
Solved

sql server 2008 DML triggers get eventdata() mail via sp_send_dbmail

Posted on 2012-09-04
4
Medium Priority
?
2,026 Views
Last Modified: 2012-09-16
I created a DML trigger for inserting, updating and/or deleting records for a table called TriggerTable. After the trigger is invoked, the script logs the database, schema, object, event type and the tsql that invoked the trigger etc. And then I am emailed via sp_send_dbmail that one of those statements has occurred.

The script successfully parses and executes. The problem is that the output is incomplete. Everything is logged properly in the TriggerDMLLog table except SchemaName, ObjectName, EventType and TSQL.

Please let me know what changes I need to make to get and store those four attribute values into TriggerDMLLog.

Thanks!

CREATE TRIGGER DMLChanged
ON dbo.TriggerTable
AFTER INSERT, UPDATE, DELETE
AS
DECLARE @EventData XML
SET @Eventdata = EVENTDATA()
INSERT dbo.TriggerDMLLog
(
DBName,
SchemaName,
ObjectName,
[EventType],
[TSQL],
ProgramName,
HostName,
SUserName,
[DateTime]
)
VALUES
(
DB_NAME(),
@EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
@EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),
@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(2000)'),
PROGRAM_NAME(),
HOST_NAME(),
SUSER_SNAME(),
GETDATE())

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL Agent',
@recipients = 'user@mail.com',
@subject = 'DML - Important!',
@body = 'DML Change: INSERT, UPDATE or DELETE occurred within dbo.TriggerTable.';

Open in new window

0
Comment
Question by:pae2
  • 3
4 Comments
 
LVL 20

Expert Comment

by:Marten Rune
ID: 38370297
I think it's supposed to look like:
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)')  
NOT the @sign, snd paranthesis() after EVENTDATA

This works for me

In your case it would be:
EventData().value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),
EventData().value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
EventData().value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),
EventData().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(2000)'),


Regards Marten
0
 

Author Comment

by:pae2
ID: 38370813
Marten, your solution did parse and execute (like mine did), but it did not fix the problem. The aforementioned four attributes are still NULL.

I just read on Microsoft's website that eventdata() works for DDL triggers, but it doesn't mention DML triggers. Were you able to make evendata() work within the DML trigger?

If anyone else has input, please let me know. Thanks!
0
 

Accepted Solution

by:
pae2 earned 0 total points
ID: 38389427
eventdata() was not built for DML triggers. It was built for DDL triggers, which is why it doesn't work for DML. In place of the DML trigger I created a database audit on the tables that I needed to log DML events.
0
 

Author Closing Comment

by:pae2
ID: 38402950
The expert answer was wrong. My answer was right.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

807 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