MS-SQL - trace de table
/SQL/MS-SQL - trace de table.sql
USE [siroccov2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_trace_initialisation](@tablename VARCHAR(255))
AS
IF @tablename = ''
RETURN
DECLARE @TRACE_table VARCHAR(255)
DECLARE @TRACE_table_with_identity VARCHAR(255)
SET @TRACE_table = 'TRACE_' + @tablename
SET @TRACE_table_with_identity = @TRACE_table+'_with_identity'
--
-- création table TRACE_<table> :
--
IF NOT EXISTS (
SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[' + @TRACE_table + ']') AND OBJECTPROPERTY(id, N'IsUserTable') = 1
)
BEGIN
--
-- copie la structure de la table (malheureusemt avec l'attribut INDENTITY) :
--
EXEC('SELECT * INTO '+@TRACE_table_with_identity+' FROM '+@tablename+' WHERE 1=2')
-- vire les contraintes :
EXEC('ALTER TABLE '+@TRACE_table_with_identity+' NOCHECK CONSTRAINT ALL')
--
-- Pour suppr ce putain d'INDENTITY :
-- génère le script de création et fait un replace() :
--
DECLARE @bufferSql VARCHAR(8000)
DECLARE @objDMO INT, @resultCode INT
BEGIN TRY
EXEC @resultcode = sp_OACreate 'SQLDMO.SQLServer', @objDMO OUT
IF @resultcode <> 0 RAISERROR('[Created Object SQLDMO.SQLServer] planté', 16, 1)
EXEC @resultcode = sp_OASetProperty @objDMO, 'loginsecure', 'true'
EXEC @resultcode = sp_OAMethod @objDMO, 'Connect', NULL, '(local)'
IF @resultcode <> 0 RAISERROR('[Connect] planté', 16, 1)
DECLARE @cmd VARCHAR(300)
SET @cmd = 'databases("'+db_name()+'").tables("'+@TRACE_table_with_identity+'").script'
EXEC @resultcode = sp_OAMethod @objDMO, @cmd, @bufferSql OUTPUT, 4
IF @resultcode <> 0 RAISERROR('[sp_OAMethod] planté', 16, 1)
EXEC sp_OADestroy @objDMO
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
RETURN
END CATCH
EXEC('DROP TABLE '+@TRACE_table_with_identity) -- plus besoin de cette table
SET @bufferSql = REPLACE(@bufferSql, '['+@TRACE_table_with_identity+']', '['+@TRACE_table+']')
SET @bufferSql = REPLACE(@bufferSql, 'IDENTITY (1, 1) ', '')
SET @bufferSql = REPLACE(@bufferSql, CHAR(13)+CHAR(10)+'GO'+CHAR(13)+CHAR(10), '') -- vire ligne du "GO"
--PRINT @bufferSql
EXEC(@bufferSql)
--
-- add les colonnes de trace d'évènement :
--
EXEC('ALTER TABLE '+@TRACE_table+' ADD date_evt datetime NULL DEFAULT GETDATE() WITH VALUES')
EXEC('ALTER TABLE '+@TRACE_table+' ADD user_evt varchar(255) NULL DEFAULT APP_NAME() WITH VALUES')
EXEC('ALTER TABLE '+@TRACE_table+' ADD action INT NULL')
END
-- /TRACE_<table>
-- liste des champs de la table à tracer :
DECLARE @champs VARCHAR(8000)
DECLARE @colonne VARCHAR(8000)
DECLARE colonnes CURSOR FOR
SELECT COLUMN_NAME FROM information_schema.columns WHERE TABLE_NAME=@tablename
SET @champs = ''
OPEN colonnes
FETCH NEXT FROM colonnes INTO @colonne
WHILE @@FETCH_STATUS = 0
BEGIN
SET @champs = @champs + @colonne + ','
FETCH NEXT FROM colonnes INTO @colonne
END
CLOSE colonnes
DEALLOCATE colonnes
--
-- Les triggers de traçage :
--
/* CODES ACTION DANS LA TABLE DE TRACE
0 : INSERT, 1 : MODIF, 2 : DELETE */
EXEC('CREATE TRIGGER dbo.ti_trace_' + @tablename + '_insert ON ' + @tablename + ' FOR INSERT AS'
+' INSERT INTO dbo.' + @TRACE_table + '(' + @champs + 'date_evt, user_evt, action)'
+' SELECT ' + @champs + ' GETDATE(), APP_NAME(), 0 FROM inserted')
EXEC('CREATE TRIGGER dbo.ti_trace_' + @tablename + '_update ON ' + @tablename + ' FOR UPDATE AS'
+' INSERT INTO dbo.' + @TRACE_table + '(' + @champs + 'date_evt, user_evt, action)'
+' SELECT ' + @champs + ' GETDATE(), APP_NAME(), 1 FROM inserted')
EXEC('CREATE TRIGGER dbo.ti_trace_' + @tablename + '_delete ON dbo.' + @tablename + ' FOR DELETE AS'
+' INSERT INTO ' + @TRACE_table + '(' + @champs + 'date_evt, user_evt, action)'
+' SELECT ' + @champs + ' GETDATE(), APP_NAME(), 2 FROM deleted')
--
-- table de config :
--
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[trace_config]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE trace_config(
ctr_id [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
ctr_table [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
ctr_table_trace [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
ctr_trace_debut [datetime] NOT NULL ,
ctr_trace_fin [datetime] NULL)
END
-- log le lancement du tracé :
INSERT INTO trace_config(ctr_table, ctr_table_trace, ctr_trace_debut)
VALUES(@tablename, @TRACE_table, GETDATE())