Discussion :

[loukoum82]

Désolé pour le titre pas très clair, je vais essayer d'expliquer :
Je voudrais lors de l'insertion d'un nouvel enregistrement dans une table incrémenter automatiquement une des colonnes de la clé primaire en fonction des autres colonnes de la clé primaire.

J'ai une table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
CREATE TABLE dbo.MATABLE (
CLE int NOT NULL, 
NUM  int NOT NULL, 
DM DATETIME, 
ORDRE  int DEFAULT 1 NOT NULL, 
LIBELLE VARCHAR(100), 
PRIMARY KEY (CLE,NUM,DM,ORDRE));

Et je voudrais que lorsque que j'insère des lignes du style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

 INSERT INTO MATABLE (CLE,NUM,DM,LIBELLE) VALUES (1000,1000,CURRENT_TIMESTAMP),(1000,1000,CURRENT_TIMESTAMP)

où les 3 premières colonnes de la clé primaire sont identiques, faire pour que la 4eme colonne (ORDRE) s'incrémente à chaque ligne insérée.

J'ai voulu le faire dans un TRIGGER FOR INSERT, mais ça ne fonctionne pas, même en utilisant ROW_NUMBER.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
CREATE  TRIGGER dbo.MONTRIGGER ON dbo.MATABLE
FOR INSERT
AS
begin
DECLARE @MAXORDRE int
SET @MAXORDRE = (SELECT isNull(MAX(a.ORDRE),0) FROM dbo.MATABLE a JOIN INSERTED i on i.cle=a.cle and i.num= a.num);
 
with tmptable as
(select ROW_NUMBER()OVER(ORDER BY i.cle,i.num,i.dm DESC) as nbrow,* from inserted i )
UPDATE a
    SET ORDRE = @MAXORDRE + tmptable.nbrow 
    FROM
        tmptable
    INNER JOIN dbo.MATABLE a ON
        a.cle = tmptable.cle and a.num=a.num and a.dm = tmptable.dm 
end

Quelqu'un aurait-il une idée ?

[fsmrel]

@loukoum82,

Vous codez :

SET ORDRE = @MAXORDRE + tmptable.nbrow 
FROM
    tmptable
INNER JOIN MATABLE a ON
    a.cle = tmptable.cle and a.num=a.num and a.dm = tmptable.dm 

Toutes les lignes concernées dans MATABLE seront mises à jour, mais avec la même valeur pour la colonne ORDRE.

Cela provoquera un viol de clé primaire dès que deux lignes seront concernées.

[fsmrel]

Quelqu'un aurait-il une idée ?

Pour se simplifier grandement la vie (pas de trigger compliqué) et obtenir un résultat correct, on peut utiliser une séquence.

Exemple :

DROP TABLE IF EXISTS MATABLE ;
DROP SEQUENCE IF EXISTS ordreSeq ;

CREATE SEQUENCE ordreSeq AS INT START WITH 1 ;

CREATE TABLE MATABLE 
(
        CLE         INT          NOT NULL, 
        NUM         INT          NOT NULL, 
        DM          DATETIME     NOT NULL, 
        ORDRE       INT DEFAULT NEXT VALUE FOR ordreSeq, 
        LIBELLE     VARCHAR(100)  NOT NULL, 
    PRIMARY KEY (CLE, NUM, DM, ORDRE)
) ;

INSERT INTO MATABLE (CLE, NUM, DM, LIBELLE) 
VALUES 
    (1000,1000,CURRENT_TIMESTAMP, 'a')
  , (1000,1000,CURRENT_TIMESTAMP, 'b')
  , (1000,1000,CURRENT_TIMESTAMP, 'c')
  , (1000,1000,CURRENT_TIMESTAMP, 'd')
;

SELECT * FROM MATABLE ; 

Au résultat :

CLE           NUM           DM                        ORDRE         LIBELLE
-----------   -----------   -----------------------   -----------   ----------
1000          1000          2021-03-21 17:57:06.217   1             a
1000          1000          2021-03-21 17:57:06.217   2             b
1000          1000          2021-03-21 17:57:06.217   3             c
1000          1000          2021-03-21 17:57:06.217   4             d

Source d’inspiration (séquences) :

SQL Server 2014 - Développer et administrer pour la performance de Frédéric Brouard & al., pages 61-63. Merci les gars !

[loukoum82]

Pour se simplifier grandement la vie (pas de trigger compliqué) et obtenir un résultat correct, on peut utiliser une séquence.

Ca revient à utiliser une identité, non ?

Merci pour la solution, je suis parti sur une identité pour me simplifier la vie, mais j'aurais bien aimé trouver une solution pour que l'incrément ne soit pas général à toute la table mais vraiment conditionnel à quelques colonnes (j'ai pu le faire sous ORACLE grâce à un trigger BEFORE INSERT FOR EACH ROW, qui permet d'éviter les doublons en cas d'insert multiples).

Ca sera pour une autre fois.

[fsmrel]

Courage ! Allons-y avec un trigger BEFORE.

(1) Je crée la table, en simplifiant le type pour la colonne dm, histoire de ne pas m’embrouiller avec la syntaxe des dates avec SQL Server :

CREATE TABLE matable (
        cle      int         not null 
      , num      int         not null
      , dm       char(10)    not null
      , ordre    int         not null
      , libelle  varchar(10) 
 , PRIMARY KEY (cle, num, dm, ordre)
) ;

(2) Le trigger. Je déclare une variable de type table (@theInserted) pour mieux tracer le comportement de SQL Server.

go
CREATE  TRIGGER montrigger on matable
instead of insert
AS
begin
declare @maxOrdre int ;

declare @theInserted table 
(
     rowNum int
   , cle int
   , num int
   , dm char(10)
   , ordre int
   , libelle varchar(10)
) ;

/* @theInserted = inserted enrichie du row_number */

insert into @theInserted (rowNum, cle, num , dm, ordre, libelle) 
    select row_number() over(order by cle), cle, num, dm, ordre, libelle
    from   inserted
;
select '' as '@theInserted', * from @theInserted ;

/* Numéro d'ordre le plus élevé dans matable
   pour le triplet cle, num, dm */

set @maxOrdre = 
   (select isNull(max(a.ordre),0) 
    from   matable as a 
      join inserted as i on i.cle = a.cle and i.num = a.num and i.dm = a.dm)
;

/* mise à jour de matable */

insert into matable
    select cle, num, dm, @maxOrdre + rowNum as ordre, libelle  
    from   @theInserted

end
go

[loukoum82]

Merci fsmrel !
Je garde ça sous le coude.