pae2
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!
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The response time was slow, but the response itself was EXCELLENT!
ASKER