Déclencheur pour capture des UPDATE

INSERT INTO DB_HST_LIGNE.dbo.T_CLIENT_CLI
(_MA, _MD, CLI_ID, CLI_NOM, CLI_PRENOM)
SELECT client_net_address, ‘D’, d.*
FROM deleted d
CROSS JOIN sys.dm_exec_connections
WHERE session_id = @@SPID
END

Listing 6
— création du déclencheur DDL
SET @SQL = ‘CREATE TRIGGER E_U_’ + SUBSTRING(@TAB, LEN(@TAB) -2,
3) +
‘ ON ‘ + @SCH + ‘.’ + @TAB +
‘ FOR UPDATE AS BEGIN SET NOCOUNT ON 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);