• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1127
  • Last Modified:

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

0
pae2
Asked:
pae2
  • 2
1 Solution
 
sachitjainCommented:
You are trying to fetch EventData() values within a dynamic query so that could be the issue. Could you try following this approach? To follow this approach, you may need to have one staging table ddl_log that would be filled in with Eventdata parameters and flushed once your email is sent within trigger.

CREATE TRIGGER DDLChanged
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
      DECLARE @Eventdata XML
      SET @Eventdata = EVENTDATA()
      INSERT ddl_log
         ([TSQL], SchemaName, ObjectName, [Event], ProgramName, SUSERSName, HostName, DBName)
         VALUES
            (  
            @Eventdata.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
            @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()) ;
      GO
      EXEC msdb.dbo.sp_send_dbmail

      @profile_name = 'SQL Agent',
      @recipients = 'username@domain.com',
      @query = 'Select * from ddl_log',
      @subject = 'The Subject',
      @body = 'The Body.'

      truncate table ddl_log
-- invoke trigger
ALTER TABLE db.schema.TriggerTable ADD TwentyFourthColumn VARCHAR(5) NULL;
0
 
pae2Author Commented:
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 :-)
0
 
pae2Author Commented:
The response time was slow, but the response itself was EXCELLENT!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now