Create trigger for create and drop database

--Create trigger for create and drop database

/****** Object: DdlTrigger [ddl_trig_database] Script Date: 02/01/2011 10:05:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [ddl_trig_database]
ON ALL SERVER
FOR CREATE_DATABASE ,DROP_DATABASE
AS
INSERT INTO DB1..Trig_database
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'),getdate(),user_name(),host_name(),suser_name()


GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [ddl_trig_database] ON ALL SERVER

/*
Create below table for trigger
USE [DB1]
GO
/****** Object: Table [dbo].[Trig_database] Script Date: 02/01/2011 10:05:25 ******/
CREATE TABLE [dbo].[Trig_database](
[event] [varchar](8000) NULL,
[date_Action] [datetime] NULL,
[username] [varchar](200) NULL,
[hostname] [varchar](100) NULL,
[login] [varchar](200) NULL
) ON [PRIMARY]

*/