DDL Triggers

Hi,
I am trying to write a DDL Trigger so that whenever someone creates or drops
Database I store information somewhere.
My Trigger is working, now I am trying to make it more useful by extracting:
* "Name" of the database being dropped or created
* Name of the User performing the action ( login account)
* And time the action was performed.
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT 'Database Created.'
INSERT INTO AuditDB.dbo.dbAudit (userName, dbName, actionDate, action)
values (', ', ', 'DB Created')
GO
How do Iobtain userName, dbName, actionDate within the trigger ?
ThanksIt is a little bit more complicated than that. Take a look at the Eventdata
function in BOL and some of the examples there. Eventdata is used to return
the information you are looking for.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"news.microsoft.com" wrote:
> Hi,
> I am trying to write a DDL Trigger so that whenever someone creates or drops
> Database I store information somewhere.
> My Trigger is working, now I am trying to make it more useful by extracting:
> * "Name" of the database being dropped or created
> * Name of the User performing the action ( login account)
> * And time the action was performed.
> CREATE TRIGGER ddl_trig_database
> ON ALL SERVER
> FOR CREATE_DATABASE
> AS
> PRINT 'Database Created.'
> INSERT INTO AuditDB.dbo.dbAudit (userName, dbName, actionDate, action)
> values (', ', ', 'DB Created')
> GO
> How do Iobtain userName, dbName, actionDate within the trigger ?
> Thanks
>
>
>