CODEWITHSUNDEEP
sqlsql-servert-sqltriggersddl
J. Doe
J. Doe

Reputation: 239

Is there a single event type for ddl trigger that tracks all drop events in database (t-sql)?

I want my trigger to log all drop events happening in my database.

CREATE TRIGGER [log]
ON DATABASE
FOR DROP_TABLE, DROP_FUNCTION, DROP_TRIGGER, ...
AS ...

Can I just replace all the drop events with one general for drop? 

CREATE TRIGGER [log]
ON DATABASE
FOR DROP_FOR_ALL_DATABASE_OBJECTS
AS ...

Upvotes: 1

Views: 272

Answers (1)

mohabbati
mohabbati

Reputation: 1158

You need to use DDL_DATABASE_LEVEL_EVENTS. By this event you can log every events.

evendata() function includes every information you need to log. For example event type, object name and so on. Note that eventdata() returns XML.

create trigger [log]
on database
for DDL_DATABASE_LEVEL_EVENTS
as
begin

    declare
        @eventType  as sysname;

    select
        @eventType  = eventdata().value('(/EVENT_INSTANCE/EventType)[1]', 'SYSNAME');

    if @eventType like '%drop%'
        ...

end;

UPDATE:

eventdate() XML sample result:

<EVENT_INSTANCE>
  <EventType>**</EventType>
  <PostTime>**</PostTime>
  <SPID>**</SPID>
  <ServerName>**</ServerName>
  <LoginName>**</LoginName>
  <UserName>**</UserName>
  <DatabaseName>**</DatabaseName>
  <SchemaName />
  <ObjectName>**</ObjectName>
  <ObjectType>**</ObjectType>
  <TargetObjectName />
  <TargetObjectType>**</TargetObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
    <CommandText>
        ...
    </CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

Upvotes: 2

Related Questions