drop proc captured

could you tell me what is wrong with
it does not like "DROP_PROC"

CREATE TRIGGER [db_LOG]
ON DATABASE
FOR DROP_PROC
AS
      SET NOCOUNT ON
      DECLARE @xEvent XML
      SET @xEvent = eventdata() 
      INSERT INTO dbLOG VALUES(
            REPLACE(CONVERT(VARCHAR(50), @xEvent.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
            CONVERT(VARCHAR(25), @xEvent.query('data(/EVENT_INSTANCE/ServerName)')),
            CONVERT(VARCHAR(15), @xEvent.query('data(/EVENT_INSTANCE/UserName)')),
            CONVERT(VARCHAR(MAX), @xEvent.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
            )

Select allOpen in new window

Here's my version and please note it relies and populates a table in a MyAuditDB database:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [My_DDL_LOGIN_EVENTS]
ON All Server
FOR
DDL_LOGIN_EVENTS
AS
SET NOCOUNT ON
DECLARE @data XML
SET @data = EVENTDATA()
if (@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)') + ':' + @data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)')) <> 'NT AUTHORITY\SYSTEM:dbo'
      INSERT MyAuditDB..dba_security_audit (posted_on, db_user, event_logged, transact_sql,DatabaseName,SchemaName,ObjectName,ObjectType)
      VALUES       (--GETDATE(),
                  @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
                  @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)') + ':' + @data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)') ,
                  @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
                  'database - ' + @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)') + ': ' + @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'),
                  @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)'),
                  @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(100)'),
                  @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(255)'),
                  @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(50)') );

GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [OneShopSVR_DDL_LOGIN_EVENTS] ON ALL SERVER
GO

event_logged
ALTER_AUTHORIZATION_DATABASE
ALTER_FUNCTION
ALTER_INDEX
ALTER_PROCEDURE
ALTER_SCHEMA
ALTER_TABLE
ALTER_TRIGGER
ALTER_USER
ALTER_VIEW
CREATE_FUNCTION
CREATE_INDEX
CREATE_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME
CREATE_PROCEDURE
CREATE_ROLE
CREATE_SCHEMA
CREATE_STATISTICS
CREATE_TABLE
CREATE_TRIGGER
CREATE_TYPE
CREATE_USER
CREATE_VIEW
DENY_DATABASE
DROP_FUNCTION
DROP_INDEX
DROP_PARTITION_FUNCTION
DROP_PROCEDURE
DROP_ROLE
DROP_SCHEMA
DROP_STATISTICS
DROP_TABLE
DROP_TRIGGER
DROP_USER
DROP_VIEW
GRANT_DATABASE
REVOKE_DATABASE
UPDATE_STATISTICS

thank- now, i am getting error on
Invalid object name 'MyAuditDB..dba_ddl_audit'.

do you have a script to create this table?

thanks very much.