krishnavenkat16 (4/10/2013)

i have a requirement to Create DML trigger for single table ( check for any updates ) in SQL 2008 and send email alert to group if there is any activity in the table. I have the following the script . This trigger is sending email when there is any activity in table but the output has nothing in it. I guess problem is with @Message statement.

Any help would be greatly appreciated.

Thanks in advance

CREATE TRIGGER [dbo].[triggername]

ON [dbo].[tablename]

AFTER INSERT, UPDATE, DELETE

AS

DECLARE @EventData XML

DECLARE @Message nvarchar(max);

DECLARE @DDLStatement nvarchar(max);

DECLARE @UserName nvarchar(max); -- To hold the user that execute the command

DECLARE @DatabaseName nvarchar(max);

SET @EventData = EVENTDATA()

[/QUOTE]

ANd therein lies the problem: This is a DML trigger (AFTER INSERT,UPDATE,DELETE).

EVENTDATA returms data only when called directly from a DDL trigger or a LOGON trigger. Not when called in a DML trigger. So @EventDate will be NULL after that call.

edit: spelling of EVENTDATA