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
FOR DROP_TABLE, ALTER_TABLE
@profile_name = 'SQL Agent',
@recipients = 'firstname.lastname@example.org',
@query = '
SET QUOTED_IDENTIFIER ON;
DECLARE @EventData XML = EVENTDATA()
@subject = 'The Subject',
@body = 'The Body.'
-- invoke trigger
ALTER TABLE db.schema.TriggerTable ADD TwentyFourthColumn VARCHAR(5) NULL;