Link to home
Start Free TrialLog in
Avatar of pae2
pae2Flag for United States of America

asked on

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

I created a DDL trigger for altering and/or dropping tables within a database. The results are emailed via sp_send_dbmail. The script successfully parses and executes. The problem is that the output is incomplete. Everything is working except the five @EvenData values. NULL is returned for each of them.

My goal was to get the TSQL command that the user entered to invoke the trigger. For example, if the user tried to alter a table by adding a column, I would get that exact TSQL command plus the schema, object and eventtype, as listed below.

I grabbed part of this script from some site. So my understanding might not be matching the intention of the script. Please take a look and let me know if I'm missing something. Thanks!

-- DDL trigger
CREATE TRIGGER DDLChanged
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SQL Agent',
@recipients = 'username@domain.com',
@query = '
SET QUOTED_IDENTIFIER ON;

DECLARE @EventData XML = EVENTDATA()

SELECT
@EventData.value(''(/EVENT_INSTANCE/TSQLCommand)[1]'', ''NVARCHAR(MAX)''),
@EventData,
@EventData.value(''(/EVENT_INSTANCE/SchemaName)[1]'',  ''NVARCHAR(255)''),
@EventData.value(''(/EVENT_INSTANCE/ObjectName)[1]'',  ''NVARCHAR(255)''),
@EventData.value(''(/EVENT_INSTANCE/EventType)[1]'', ''NVARCHAR(100)''),
PROGRAM_NAME(),
SUSER_SNAME(),
HOST_NAME(),
DB_NAME()',

@subject = 'The Subject', 
@body = 'The Body.'

-- invoke trigger
ALTER TABLE db.schema.TriggerTable ADD TwentyFourthColumn VARCHAR(5) NULL;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of sachitjain
sachitjain
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of pae2

ASKER

sachitjain, everything worked except this line: @query = 'Select * from ddl_log'. And that's okay. Putting the results into a table is better anyway. I can troubleshoot why the @query variable isn't working at a later time and/or post the question on EE, if needed. So thanks so much for the help and now I'll know when our devs modify MY db structures :-)
Avatar of pae2

ASKER

The response time was slow, but the response itself was EXCELLENT!