[SQL Server] Suivre les événements au niveau de la BD : Create Table, Drop Table, Create Type ...
CREATE TABLE [dbo].[SUIVI_DATABASE](
[EVENEMENT] [xml] NULL,
[DATE_OPERATION] [datetime] NULL,
[UTILISATEUR] [nvarchar](50) NULL
) ON [PRIMARY]
CREATE TRIGGER [dbo_TRG_SUIVIDATABASE]
ON DATABASE
FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE,CREATE_TYPE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
INSERT INTO dbo.SUIVI_DATABASE(EVENEMENT,DATE_OPERATION,UTILISATEUR)
VALUES (eventdata(),getdate(),USER_NAME())
END
SELECT EVENEMENT.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)') DatabaseName ,
EVENEMENT.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(100)') LoginName ,
EVENEMENT.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(100)') ServerName
FROM dbo.SUIVI_DATABASE
--****************************
CREATE TABLE ddl_log(PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));
GO
CREATE TRIGGER log
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT ddl_log
(PostTime, DB_User, Event, TSQL)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), CURRENT_USER),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
GO