Discussion :

[Kropernic]

Bonjour,

Je n'arrive pas à définir un trigger de manière ensembliste. Voici ce que je voudrais arriver à faire...

Soit la table suivante :

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
18
19
20
CREATE TABLE [dbo].[T_CONTRACT_CTR](
    [CTR_ID] [int] IDENTITY(1,1) NOT NULL,
    [CTR_DATE] [date] NOT NULL,
    [BRA_ID] [int] NOT NULL,
    [DEP_ID] [smallint] NOT NULL,
    [SUP_ID] [int] NOT NULL,
 CONSTRAINT [PK_T_CONTRACT_CTR] PRIMARY KEY CLUSTERED 
(
    [CTR_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[T_CONTRACT_CTR]  WITH CHECK ADD  CONSTRAINT [FK_T_CONTRACT_CTR_TJ_DEP_JSB_JDJ] FOREIGN KEY([DEP_ID], [BRA_ID], [SUP_ID])
REFERENCES [dbo].[TJ_DEP_JSB_JDJ] ([DEP_ID], [BRA_ID], [SUP_ID])
GO
 
ALTER TABLE [dbo].[T_CONTRACT_CTR] CHECK CONSTRAINT [FK_T_CONTRACT_CTR_TJ_DEP_JSB_JDJ]
GO

Lors de l'insertion de nouvelles lignes, il faut que la date insérée dans la colonne CTR_DATE soit plus grande que toutes les autres valeurs de CTR_DATE pour le triple {DEP_ID, BRA_ID, SUP_ID}.

Mais je n'arrive pas à formuler cette condition dans un IF.

Mais p-e que je fais fausse route à la base.

[Kropernic]

Je pense y être finalement arriver avec ceci :

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
18
19
IF EXISTS    (
                SELECT    *
                FROM    DBO.T_CONTRACT_CTR CTR
                            INNER JOIN inserted INS
                                ON    CTR.BRA_ID = INS.BRA_ID
                                AND    CTR.DEP_ID = INS.DEP_ID
                                AND    CTR.SUP_ID = INS.SUP_ID
                                AND    CTR.CTR_DATE > INS.CTR_DATE
                )
        DELETE    
        FROM    DBO.T_CONTRACT_CTR
        WHERE    CTR_ID IN    (
                            SELECT    INS.CTR_ID
                            FROM    DBO.T_CONTRACT_CTR CTR
                                        INNER JOIN inserted INS
                                            ON    CTR.BRA_ID = INS.BRA_ID
                                            AND    CTR.DEP_ID = INS.DEP_ID
                                            AND    CTR.SUP_ID = INS.SUP_ID
                                            AND    CTR.CTR_DATE > INS.CTR_DATE)

[mikedavem]

Hmm ton trigger ne fait pas ce que tu dis dans le postulat de départ

Lors de l'insertion de nouvelles lignes, il faut que la date insérée dans la colonne CTR_DATE soit plus grande que toutes les autres valeurs de CTR_DATE pour le triple {DEP_ID, BRA_ID, SUP_ID}.

Dans ton trigger si j'ai bien compris si tu trouves au moins une ligne dont la date est supérieure à celle insérée tu les supprimes ... est ce vraiment le comportement voulu ?

++

[Kropernic]

Bah je me trouve dans l'after insert (car y avait déjà un trigger after insert pour insérer un truc dans une autre table alors autant l'utiliser).

Donc si une ligne insérer contient une date avec une date plus petite que la date maximum pour le triplet{BRA_ID, SUP_ID, DEP_ID}, cela n'est pas valide donc je la supprime.

A la base, je voulais tester cela dans une contrainte CHECK histoire de ne pas avoir à supprimer à posteriori mais je n'y suis pas arrivé .

[mikedavem]

et pourquoi pas un trigger FOR INSERT et un ROLLBACK ?

++

[Kropernic]

et pourquoi pas un trigger FOR INSERT et un ROLLBACK ?

++

Je crois que la réponse technique est "parce que j'suis un noob" .

Je connaissais les triggers AFTER INSERT et INSTEAD OF INSERT mais pas FOR INSERT.

Je testerai demain

[SQLpro]

FOR INSERT = AFTER INSERT !

Personnellement je testerais de la manière suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH T0 AS
(
SELECT MAX(CTR_DATE) OVER(PARTITION BY DEP_ID, BRA_ID, SUP_ID) AS D, 
       DEP_ID, BRA_ID, SUP_ID
FROM   dbo.T_CONTRACT_CTR)
SELECT 1
FROM   T0 
       INNER JOIN inserted AS i
             ON T.CTR.BRA_ID = i.BRA_ID
                AND T0.DEP_ID = i.DEP_ID
                AND T0.SUP_ID = i.SUP_ID
                AND T0.D > i.CTR_DATE
IF @@ROWCOUNT > 0
   ROLLBACK;

A +

[mikedavem]

Merci pour la précision Frédéric, je n'ai pas eu le temps de répondre à Kropernic --> AFTER = FOR.

Le reste de la réponse est donné par SQLPro.

++

[Kropernic]

J'ai encore vraiment du mal avec les transactions...

Sinon, c'est dangereux je trouve que FOR = AFTER. L'un est quand même le contraire de l'autre.

On (les néophytes dont je fais partie) serait en droit de penser qu'un trigger FOR INSERT est déclenché AVANT l'insertion et non après...

Vous ne trouvez pas ?

[SQLpro]

En fait il n'y a pas de réel intérêt aux triggers BEFORE... C'est d'ailleurs même Sybase qui en 1986 à inventé les déclencheurs, bien avant qu'Oracle ne les introduise....
Or compte tenu qu'il existe IDENTITY pour auto incrémenter, les déclencheurs BEFORE sont de peu d'utilité en sus d'être contre performant :

• pour le préventif, utiliser les contraintes CHECK est généralement suffisant
• les données n'étant pas encore présente dans la table il faut faire des UNION ALL avec la table cible avant de faire des jointures

A +