1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
|
CREATE TRIGGER nb_chercheur ON chercheur
FOR INSERT,DELETE,UPDATE
AS
BEGIN
MERGE specialite AS T
USING (
SELECT
COALESCE(D.specialite_chercheur, I.specialite_chercheur) AS specialite_chercheur
, ISNULL(I.Cnt, 0) - ISNULL(D.Cnt, 0) AS Delta
FROM (
SELECT
I.specialite_chercheur
, COUNT(*) AS Cnt
FROM inserted AS I
GROUP BY I.specialite_chercheur
) AS I
FULL JOIN (
SELECT
D.specialite_chercheur
, COUNT(*) AS Cnt
FROM deleted AS D
GROUP BY D.specialite_chercheur
) AS D ON (
D.specialite_chercheur = I.specialite_chercheur
)
) AS Src ON (
T.acronyme = Src.specialite_chercheur
)
WHEN NOT MATCHED THEN
INSERT (acronyme, nb_chercheurs)
VALUES (Src.specialite_chercheur, Src.Delta)
WHEN MATCHED THEN
UPDATE
SET
T.nb_chercheurs = T.nb_chercheurs + Src.Delta
;
END |