Sql Auditing Changes Stored Procedure Design

/*
I will publish this as I don't have time to finish the documentation but this is the stored procedure that goes with the table below

All users who can drop tables need to be able to send and email or EXECUTE AS need to be applied to allow the stored procedure to both send the email and write the record.
*/

USE [Common]
GO
/****** Object: DdlTrigger [Event_SP_changes] Script Date: 06/02/2009 16:58:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create trigger [Event_SP_changes]
on database --WITH EXECUTE AS 'UserName'
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
DECLARE @FullFile xml
DECLARE @EventType nvarchar(25)
DECLARE @EventTime datetime
DECLARE @LoginName nvarchar(50)
DECLARE @UserName nvarchar(25)
DECLARE @ObjectName nvarchar(50)
DECLARE @ObjectType nvarchar(25)
DECLARE @Command nvarchar(max)
SET @FullFile = eventdata()
SET @EventType = @FullFile.value('(//EVENT_INSTANCE/EventType)[1]', 'nvarchar(25)')
SET @EventTime = @FullFile.value('(//EVENT_INSTANCE/PostTime)[1]', 'DATETIME')
SET @LoginName = @FullFile.value('(//EVENT_INSTANCE/LoginName)[1]', 'nvarchar(50)')
SET @UserName = @FullFile.value('(//EVENT_INSTANCE/UserName)[1]', 'nvarchar(25)')
SET @ObjectName = @FullFile.value('(//EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(50)')
SET @ObjectType = @FullFile.value('(//EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(25)')
SET @Command = @FullFile.value('(//EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
--print @command
INSERT INTO [AuditDB].dbo.Common_SP_Events
([EventType]
,[EventTime]
,[LoginName]
,[UserName]
,[ObjectName]
,[ObjectType]
,[Command]
,[FullFile])
SELECT @EventType
, @EventTime
, @LoginName
, @UserName
, @ObjectName
, @ObjectType
, @Command
, @FullFile



GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [Event_SP_changes] ON DATABASE



----------------
Now playing: NPR - 0905150: NPR: 05-30-2009 Wait Wait... Don't Tell Me!
via FoxyTunes