Déclencheur DDL complet

DECLARE @XML XML, @SCH sysname, @TAB sysname;
SET @XML = EVENTDATA();
— extraction à l’aide d’XQuery/XPath du nom du schema et du nom
de table
SELECT @SCH = @XML.value(‘(/EVENT_INSTANCE/SchemaName)[1]’,
‘sysname’),
@TAB = @XML.value(‘(/EVENT_INSTANCE/ObjectName)[1]’,
‘sysname’);
— génération d’une requête de création de la table
d’historisation
DECLARE @SQL VARCHAR(max);
— un schéma existe-il avec ce nom là ?
IF NOT EXISTS (SELECT *
FROM DB_HST_LIGNE.INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = @SCH)
BEGIN
— non : on le créé
SET @SQL = ‘CREATE SCHEMA ‘ + @SCH;
EXEC (@SQL);
END;
— création de la table
SET @SQL = ‘CREATE TABLE DB_HST_LIGNE.’ + @SCH+’.’+@TAB + ‘ (‘
+ ‘_ID BIGINT NOT NULL IDENTITY PRIMARY KEY, _MD CHAR(1), ‘
+ ‘_DH DATETIME DEFAULT CURRENT_TIMESTAMP, ‘
+ ‘_SU NVARCHAR(128) DEFAULT USER, _MA NCHAR(40), ‘;
SELECT @SQL = @SQL + COLUMN_NAME + ‘ ‘ + DATA_TYPE +
CASE
WHEN DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’,
‘nvarchar’)
THEN ‘ (‘ + CAST(CHARACTER_MAXIMUM_LENGTH AS
VARCHAR(16))
+ ‘) COLLATE ‘ + COLLATION_NAME
WHEN DATA_TYPE IN (‘decimal’, ‘numeric’)
THEN ‘ (‘ + CAST(NUMERIC_PRECISION AS VARCHAR(16))
+’, ‘
+ CAST(NUMERIC_SCALE AS VARCHAR(16)) + ‘)’
ELSE  »
END + ‘, ‘
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SCH
AND TABLE_NAME = @TAB;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) – 1) + ‘)’;
EXEC (@SQL);
— création du déclencheur DDL UPDATE de suivi de modification
SET @SQL = ‘CREATE TRIGGER E_U_’ + SUBSTRING(@TAB, LEN(@TAB) -2,
3) +
‘ ON ‘ + @SCH + ‘.’ + @TAB +
‘ FOR UPDATE AS SET NOCOUNT ON BEGIN INSERT INTO
DB_HST_LIGNE.’ +
@SCH + ‘.’ + @TAB + ‘ (_MA, _MD, ‘;
SELECT @SQL = @SQL + COLUMN_NAME + ‘, ‘
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SCH
AND TABLE_NAME = @TAB;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) – 1) + ‘) ‘+
‘SELECT client_net_address,  »U », i.* ‘ +
‘FROM inserted i ‘ +
‘CROSS JOIN sys.dm_exec_connections ‘ +
‘WHERE session_id = @@SPID ‘ +
‘END ‘
EXEC (@SQL);
— création du déclencheur DDL DELETE de suivi de suppression
SET @SQL = ‘CREATE TRIGGER E_D_’ + SUBSTRING(@TAB, LEN(@TAB) -2,
3) +
‘ ON ‘ + @SCH + ‘.’ + @TAB +
‘ FOR DELETE AS SET NOCOUNT ON BEGIN INSERT INTO
DB_HST_LIGNE.’ +
@SCH + ‘.’ + @TAB + ‘ (_MA, _MD, ‘;
SELECT @SQL = @SQL + COLUMN_NAME + ‘, ‘
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SCH
AND TABLE_NAME = @TAB;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) – 1) + ‘) ‘+
‘SELECT client_net_address,  »D », d.* ‘ +
‘FROM deleted d ‘ +
‘CROSS JOIN sys.dm_exec_connections ‘ +
‘WHERE session_id = @@SPID ‘ +
‘END ‘
EXEC (@SQL);
END;