CREATE TABLE S_COM.T_CLIENT_CLI
(CLI_NUMERO                      INTEGER,
 CLI_NOM                         VARCHAR(32),
 CLI_REMISE_MAXI_POURCENT        FLOAT,
 CLI_REMISE_MAXI_MONTANT         DECIMAL(16,2));

CREATE TABLE S_COM.T_FACTURE_FCT
(FCT_NUMERO                      INTEGER,
 CLI_NUMERO                      INTEGER,
 FCT_DATE_EMISSION               DATE,
 FCT_MONTANT_TOTAL_AVANT_REMISE  DECIMAL(16,2),
 FCT_MONTANT_REMISE_TOTALE       DECIMAL(16,2),
 CONSTRAINT CK_FCT_REMISES 
      CHECK (FCT_MONTANT_REMISE_TOTALE 
             <= (SELECT CLI_REMISE_MAXI_MONTANT
                 FROM   S_COM.T_CLIENT_CLI
                 WHERE  S_COM.T_CLIENT_CLI.CLI_NUMERO 
                        = S_COM.T_FACTURE_FCT.CLI_NUMERO)
             AND
             FCT_MONTANT_REMISE_TOTALE 
             <= (SELECT CAST(CLI_REMISE_MAXI_POURCENT  
                             * FCT_MONTANT_TOTAL_AVANT_REMISE 
                             / 100.0 AS DECIMAL(16,2))
                 FROM   S_COM.T_CLIENT_CLI
                 WHERE  S_COM.T_CLIENT_CLI.CLI_NUMERO 
                        = S_COM.T_FACTURE_FCT.CLI_NUMERO)));

/*

--> syntaxe pour SQL Server

CREATE FUNCTION S_COM.F_GET_CLI_REMISE_MAXI_MONTANT (@CLI_NUMERO INT)
RETURNS FLOAT
AS
BEGIN
RETURN (SELECT CLI_REMISE_MAXI_MONTANT
                 FROM   S_COM.T_CLIENT_CLI
                 WHERE  CLI_NUMERO = @CLI_NUMERO);
END;
GO

CREATE FUNCTION S_COM.F_GET_CLI_REMISE_MAXI_POURCENT (@CLI_NUMERO INT)
RETURNS FLOAT
AS
BEGIN
RETURN (SELECT CLI_REMISE_MAXI_POURCENT
        FROM   S_COM.T_CLIENT_CLI
        WHERE  CLI_NUMERO = @CLI_NUMERO);
END;
GO

CREATE TABLE S_COM.T_CLIENT_CLI
(CLI_NUMERO                      INTEGER,
 CLI_NOM                         VARCHAR(32),
 CLI_REMISE_MAXI_POURCENT        FLOAT,
 CLI_REMISE_MAXI_MONTANT         DECIMAL(16,2));


CREATE TABLE S_COM.T_FACTURE_FCT
(FCT_NUMERO                      INTEGER,
 CLI_NUMERO                      INTEGER,
 FCT_DATE_EMISSION               DATE,
 FCT_MONTANT_TOTAL_AVANT_REMISE  DECIMAL(16,2),
 FCT_MONTANT_REMISE_TOTALE       DECIMAL(16,2),
 CONSTRAINT CK_FCT_REMISES 
      CHECK (FCT_MONTANT_REMISE_TOTALE 
             <= S_COM.F_GET_CLI_REMISE_MAXI_MONTANT(CLI_NUMERO)
             AND
             FCT_MONTANT_REMISE_TOTALE 
             <= CAST(S_COM.F_GET_CLI_REMISE_MAXI_POURCENT (CLI_NUMERO)
                             * FCT_MONTANT_TOTAL_AVANT_REMISE 
                             / 100.0 AS DECIMAL(16,2))));

*/



