> Tech > Déclencheur DDL complet

Déclencheur DDL complet

Tech - Par iTPro - Publié le 24 juin 2010
email

Le code complet du déclencheur DDL est donc le suivant : voir listing 9.

Listing 9
CREATE TRIGGER E_DB_CRETAB
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
SET NOCOUNT ON;
-- récupération des informations du "paquet" d'événement du
trigger DDL

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;

Téléchargez gratuitement cette ressource

Protection des Données : 10 Best Practices

Protection des Données : 10 Best Practices

Le TOP 10 des meilleures pratiques, processus et solutions de sécurité pour mettre en œuvre une protection efficace des données et limiter au maximum les répercutions d’une violation de données.

Tech - Par iTPro - Publié le 24 juin 2010