[2005] Créer un trigger (INSERT/UPDATE) paramètré
Il n'existe pas de possibilité directe de passer un paramètre à un trigger portant sur la modification (INSERT/DELETE/UPDATE) d'une table.
Un trigger n'a que pour seul input (données en entré) les tables virtuelles inserted et deleted.
L'idée, pour passer des paramètres est d'étendre les tables inserted et deleted avec des colonnes, dans l'exemple qui va suivre cette colonne est ViaSynchro et est de type BIT.
Ces deux tables (inserted et deleted) ne sont absolument pas modifiables au sein du trigger, pour en modifier la structure il faut donc agir en ammont.
Changer virtuellement la structure d'une table, ça nous amène à penser VIEW.
Et nous pouvons y penser car il est tout à fait possible de crée des trigger (INSERT/DELETE/UPDATE) sur des view.
Cela étant nous n'aurons pas de nouvelles possibilités pertinentes en employant un trigger DELETE sur notre view.
Il y a cependant une différence essentielle entre les possibilité de triggers faits sur des view et celles de triggers faits sur des tables : un trigger sur une view ne peut être que de type INSTEAD OF (trigger qui remplace le comportement par défaut d'une opération).
Un trigger INSTEAD OF soulève trois problèmatiques.
1. Ce trigger ne peut être qu'unique pour une opération (2 triggers INSTEAD OF ne peuvent pas cooéxiter pour la même opération pour une même table).
Vous ne pouvez donc pas, par exemple, scinder votre logique en plusieurs triggers INSTEAD OF UPDATE sur votre table MaTable.
2. INSTEAD OF ayant lieu à la place de l'opération par défaut, il vous faudra généralement éxécuter vous même cette opération dans votre trigger.
Ainsi, si la structure d'une table évolue, il vous faudra sans doute réécrire les opérations UPDATE, INSERT (et moins fréquement DELETE) présentes dans les triggers INSTEAD OF de cette table.
3. Lors d'une opération d'insertion, INSTEAD OF étant déclenché sans que l'insertion ait déjà eut lieu, toutes les colonnes de type INDENTITY n'auront pas de valeur dans la table virtuelle inserted puisque ces valeurs sont attribués lors de l'insertion effective. Et, dans le cas d'une view, même après insertion (cela se verait dans la clause OUPUT par exemple).
Les points 1 et 2 que je viens de citer sont des contraintes qui peuvent vous imposer du travail mais du travail que vous savez déjà faire.
Je vais répondre à la problématique du point 3, car ce point là pourrait en mettre certains en déroute.
3. INSTEAD OF INSERT ON viewname
L'objectif de mon trigger va être de pouvoir faire une insertion et d'avoir pour chaque ligne insérée une colonne me servant de paramètre décisionnel (cela ne sera pas illustré) qui n'appartient pourtant pas à ma table de base.
Le problème étant que dans mon trigger, je dois pouvoir identifier les lignes insérés dans la table et que le seul identifiant qui me le permette est de type IDENTITY (INT dans l'exemple).
Cette identification des lignes inserées dans la table est pourtant essentiel si je veux associer ces lignes aux lignes de la table inserted (insertion dans la view).
Comment procéder :
1 - rajouter une colonne virtuelle de type uniqueidentifier sur ma view.
Grâce a cela la colonne DATA_ID qui sera évidemment présente dans la table inserted de mon trigger pourra me servir d'identifiant pour cette table.
Il faudra, après que le trigger soit crée, veillier lors de l'insertion sur cette view de passer une valeur unique à DATA_ID pour chaque ligne (la fonction NEWID() est faite pour).
Code:
1
2
3
4
5
6
7
8
9
|
CREATE VIEW vDATA_tHandicaps
AS
SELECT
*
, CAST(NULL AS BIT) AS ViaSynchro
, CAST(NULL AS UNIQUEIDENTIFIER) AS DATA_ID
FROM tHandicaps
GO |
2 - insérer des dummies (lignes reiviales) dans la table.
La table inserted accessible dans mon trigger ne pourra pas me servir pour identifier les lignes inserrés, comme je l'ai expliqué seul la colonne de type IDENTITY peut me servir d'identifiant dans la table et je ne pourrez jamais savoir quelle ligne inserrée aura reçu quel valeur pour cette colonne.
Vis à vis de l'identifier de chaque ligne nous sommes aveugles, par contre il est possible par la clause OUTPUT de récupérer toutes les valeurs qu'aura pris cette colonne lors de l'insertion.
À la fin de cette opération nous auront inséré le bon nombre de ligne et nous auront la liste des identifiants (Rappel : la colonne de type IDENTITY) de ces lignes.
Code:
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 vDATA_tHandicaps_IOINSERT
ON vDATA_tHandicaps
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @InsertedID TABLE
(
Id BIGINT
)
-- INSERT tHandicaps : Dummy entries
INSERT tHandicaps
(
IdMember
, EntryDate
, Handicap
, OriginClubId
, Formula
, Remark
, QualificationStatus
, TypeOfAdjustement
, OldHcp
)
OUTPUT inserted.Id INTO @InsertedID(Id)
SELECT
1
, '01-01-1900'
, 0
, 1000
, ''
, ''
, 1
, 1
, 0
FROM inserted AS H |
3 - Établir une table qui pourra faire le lien entre une ligne à insérer* et une ligne de dummies.
Nous avons des lignes insérées, il faudrait maintenant pour chacune de ces lignes lui attribuer les vraies valeurs d'une des lignes que nous désirions inserées (Rappel : lignes présentes dans la table inserted).
Il nous faut alors un mécanisme pour faire une association 1 à 1.
Cette association se fera entre l'identifiant de la table inserted (DATA_ID) et celui de la table où eu lieux l'insertion (Rappel : la colonne de type IDENTITY).
Cette association est arbitraire, en effet on peut associer n'importe quel identifiant à n'importe quelle ligne à mettre à jour, cela n'a aucune importance.
Pour mettre en place ce mécanisme, je me sert de ROW_NUMBER(), ROW_NUMBER() pourra me donner une valeur unique (1, 2, 3, ...) parmi les lignes d'identifiant et unique (1, 2, 3, ...) parmi les lignes de la table inserted.
Code:
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
39
40
41
42
43
44
45
46
47
|
-- @HcpSynchro
DECLARE @HcpSynchro TABLE
(
Id BIGINT
, DATA_ID UNIQUEIDENTIFIER
)
INSERT @HcpSynchro
(
DATA_ID
)
SELECT
I.DATA_ID
FROM inserted AS I
-- Random assignement of real id to virtual id
WITH Assignment
(
DATA_ID
, Id
)
AS
(
SELECT
S.DATA_ID
, IID.Id
-- Match Row - Row
FROM (
SELECT
S.DATA_ID
, ROW_NUMBER() OVER(ORDER BY S.DATA_ID ASC) AS Row
FROM @HcpSynchro AS S
) AS S
INNER JOIN (
SELECT
IID.Id
, ROW_NUMBER() OVER(ORDER BY IID.Id ASC) As Row
FROM @InsertedID AS IID
) AS IID ON (
IID.Row = S.Row
) )
UPDATE @HcpSynchro
SET Id = A.Id
FROM Assignment AS A
WHERE [@HcpSynchro].DATA_ID = A.DATA_ID |
J'ai maintenant une table (@HcpSynchro) qui fait un lien clair (1 - 1) entre un identifiant unique (DATA_ID) présent dans la table inserted et un identifiant présent dans la table où eu lieu l'insertion.
4- Finallement on met à jour nos dummies grâce au lien 1 - 1 établi.
Code:
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
|
UPDATE @HcpSynchro
SET Id = A.Id
FROM Assignment AS A
WHERE [@HcpSynchro].DATA_ID = A.DATA_ID
-- UPDATE tHandicaps
UPDATE tHandicaps
SET
IdMember = H.IdMember
, EntryDate = H.EntryDate
, Handicap = H.Handicap
, OriginClubId = H.OriginClubId
, Formula = H.Formula
, Stableford = H.Stableford
, Csa = H.Csa
, CsaStableford = H.CsaStableford
, Remark = H.Remark
, IdCompetition = H.IdCompetition
, QualificationStatus = H.QualificationStatus
, ForeignKey = H.ForeignKey
, TypeOfAdjustement = H.TypeOfAdjustement
, EventDate = H.EventDate
, NbHoles = H.NbHoles
, OldHcp = H.OldHcp
FROM @HcpSynchro AS S
INNER JOIN inserted AS H ON (
H.DATA_ID = S.DATA_ID
)
WHERE tHandicaps.Id = S.Id
END |
Ici se clôt mon trigger d'exemple.
Je n'ai fait aucun usage de la colonne ViaSynchro mais si vous voulez un exemple d'utilisation, voici pour exemple, une instruction qui aurait pu être mise avant le END.
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
-- tSynchro_BatchHandicapLines
INSERT tSynchro_BatchHandicapLines
(
IdHandicap
, ForeignKey
, [Action]
, OldHcp
)
SELECT
S.Id
, H.ForeignKey
, 'insert'
, H.OldHcp
FROM @HcpSynchro AS S
INNER JOIN inserted AS H ON (
H.DATA_ID = S.DATA_ID )
WHERE S.ViaSynchro = 0 |
La jointure entre @HcpSynchro et inserted est donc d'une complexité triviale.
J'espère que mon explication vous aura aider ou ouvert à certaines possibilités.
Concernant des supressions paramétrés dans une table, je vous invite à lire mon autre article (en s'orientant sans doute sur l'UPDATE d'une VIEW, accès à l'existant pour le modifier).
Serge.
