USE master;
GO

CREATE DATABASE DB_PROD;
GO

USE DB_PROD;
GO

/*==============================================================*/
/* Table : T_CLIENT_CLI                                         */
/*==============================================================*/
create table T_CLIENT_CLI (
   CLI_ID               int                  identity,
   CLI_NOM              char(32)             not null,
   CLI_PRENOM           varchar(25)          null,
   constraint PK_T_CLIENT_CLI primary key  (CLI_ID)
)
go

/*==============================================================*/
/* Table : T_COMMANDE_CMD                                       */
/*==============================================================*/
create table T_COMMANDE_CMD (
   CLI_ID               int                  not null,
   PRD_REF              int                  not null,
   CMD_DATE             datetime             not null,
   CMD_QUANTITE         float(16)            not null,
   constraint PK_T_COMMANDE_CMD primary key  (CLI_ID, PRD_REF)
)
go

/*==============================================================*/
/* Index : T_COMMANDE_CMD2_FK                                   */
/*==============================================================*/
create   index T_COMMANDE_CMD2_FK on T_COMMANDE_CMD (
PRD_REF
)
go

/*==============================================================*/
/* Table : T_PRODUIT_PRD                                        */
/*==============================================================*/
create table T_PRODUIT_PRD (
   PRD_REF              int                  identity,
   PRD_MARQUE           char(25)             not null,
   PRD_MODELE           varchar(32)          not null,
   PRD_PRIX             decimal(16,2)        not null,
   constraint PK_T_PRODUIT_PRD primary key  (PRD_REF)
)
go

alter table T_COMMANDE_CMD
   add constraint FK_T_COMMAN_T_COMMAND_T_CLIENT foreign key (CLI_ID)
      references T_CLIENT_CLI (CLI_ID)
go

alter table T_COMMANDE_CMD
   add constraint FK_T_COMMAN_T_COMMAND_T_PRODUI foreign key (PRD_REF)
      references T_PRODUIT_PRD (PRD_REF)
go



USE master;
GO

CREATE DATABASE DB_HST_LIGNE;
GO

USE DB_PROD;
GO 

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 tgrigger DDL
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);

END;
GO



USE DB_PROD;
GO

CREATE TRIGGER E_D_CLI
ON dbo.T_CLIENT_CLI
FOR DELETE
AS
BEGIN

   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
GO






USE DB_PROD;
GO

CREATE TRIGGER dbo.E_D_CLI 
ON dbo.T_CLIENT_CLI
FOR DELETE 
AS 
BEGIN 

   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 





USE DB_PROD;
GO

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
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;
GO





USE DB_PROD;
GO

INSERT INTO dbo.T_CLIENT_CLI VALUES ('Duchemin', 'Marcel');
INSERT INTO dbo.T_CLIENT_CLI VALUES ('Montel', 'Marc');
GO

UPDATE dbo.T_CLIENT_CLI 
SET    CLI_NOM = UPPER(CLI_NOM);
GO

DELETE FROM dbo.T_CLIENT_CLI 
WHERE  CLI_PRENOM = 'marc' COLLATE French_CI_AI;
GO

SELECT *
FROM   DB_HST_LIGNE.dbo.T_CLIENT_CLI
UNION  ALL
SELECT NULL, NULL, NULL, NULL, NULL, *
FROM   dbo.T_CLIENT_CLI
ORDER  BY 6, 1 DESC