Les triggers sous SQL Server !

**Artemus24** · 21/05/2016, 19h58

Salut à tous.

Je me suis attaqué aux triggers qui sont très différents de MySql et de FireBird, et ce que je connaissais sous DB2 Z/OS.

Le premier exercice consiste à simuler un "trigger before insert et update" sur une colonne ('quant') qui ne doit pas être NULL.
Le test se fait en deux phases :
--> je teste avec le trigger pour voir si les modification sont bien faites avant d'insérer les lignes dans la table.
--> je teste ensuite en désactivant le trigger et en refaisant l'insertion dans la table.
Voici le résultat :

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
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
-- ===========
-- Paramétrage
-- ===========
 
SET NOCOUNT    ON
 
-- ==================
-- Lien vers Database
-- ==================
 
use tempdb
 
Le contexte de la base de données a changé*; il est maintenant 'tempdb'.
 
-- ========================
-- Suppression Table 'test'
-- ========================
 
IF OBJECT_ID(N'dbo.test', N'U') IS NOT NULL
    DROP TABLE dbo.test
 
-- =====================
-- Création Table 'test'
-- =====================
 
create table dbo.test (
  id      smallint identity(1, 1) NOT NULL,
  quant   integer                 NOT NULL,
  date    datetime                NOT NULL,
  constraint pk_test_id   primary key clustered (id)
)
 
-- ==============================
-- Suppression Trigger 'modifier'
-- ==============================
 
IF OBJECT_ID(N'dbo.modifier', N'TR') IS NOT NULL
    DROP TRIGGER dbo.modifier
 
-- ===================
-- Trigger 'modifier'
-- ===================
 
CREATE TRIGGER dbo.modifier
ON dbo.test INSTEAD OF insert, update
AS
BEGIN
  IF NOT UPDATE(quant) RETURN
 
  IF EXISTS ( SELECT * FROM deleted     )
  BEGIN
    update dbo.test
      set quant = coalesce(i.quant, 0)
      from       inserted as i
      inner join dbo.test as t
      on t.id = i.id
  END
  ELSE
  BEGIN
    insert into dbo.test (quant, date)
      select coalesce(i.quant, 0) as quant, i.date
      from      inserted as i
      left join dbo.test as t
      on    t.id = i.id
          where t.id is null
  END
END
 
-- =====================
-- Insertion dans 'test'
-- =====================
 
INSERT INTO test (quant,date) VALUES
  (null,'2016-05-07 22:36:52'),
  (2,   '2016-05-12 15:00:23')
 
-- ================
-- Vidage de 'test'
-- ================
 
select * from test
 
id     quant       date
------ ----------- -----------------------
     1           0 2016-05-07 22:36:52.000
     2           2 2016-05-12 15:00:23.000
 
-- =======================
-- Mise à jour dans 'test'
-- =======================
 
UPDATE test set quant = null where id = 2;
 
-- ================
-- Vidage de 'test'
-- ================
 
select * from test
 
id     quant       date
------ ----------- -----------------------
     1           0 2016-05-07 22:36:52.000
     2           0 2016-05-12 15:00:23.000
 
-- =======================
-- On désactive le trigger
-- =======================
 
DISABLE TRIGGER dbo.modifier on dbo.test
 
-- ========================
-- On purge la table 'test'
-- ========================
 
truncate table dbo.test
 
-- =====================
-- Insertion dans 'test'
-- =====================
 
INSERT INTO test (quant,date) VALUES
  (null,'2016-05-07 22:36:52'),
  (2,   '2016-05-12 15:00:23')
 
Message 515, niveau 16, état 2, serveur ORION\SQLEXPRESS, ligne 5
Impossible d'insérer la valeur NULL dans la colonne 'quant', table 'tempdb.dbo.test'. Cette colonne n'accepte pas les valeurs NULL. Échec de INSERT.
L'instruction a été arrêtée.
-- ================
-- Vidage de 'test'
-- ================
 
select * from test
 
id     quant       date
------ ----------- -----------------------
 
Appuyez sur une touche pour continuer...

Vous me dites ce qui ne va pas ou encore, si vous avez une autre approche concernant ce "trigger before insert et update".

Merci !
@+

**hmira** · 22/05/2016, 23h04

Envoyé par Artemus24

Vous me dites ce qui ne va pas ou encore, si vous avez une autre approche concernant ce "trigger before insert et update".

Ci-dessous quelques remarques :

1 - D'abord, on ne dit pas "trigger", mais on dit plutôt déclencheur pour éviter ainsi tout anglicisme abusif et aussi pour ne pas titiller la susceptibilité des modérateurs, du présent forum, dont certains sont très pointilleux sur le respect de la langue française, une démarche à laquelle, personnellement, je souscris entièrement

.

2 - Si le but est seulement de s'assurer que la valeur insérée ou mise à jour de la colonne ne doit pas être nulle, compte-tenu du fait qu'une contrainte NOT NULL a été établie sur la dite colonne, et ce, pour ne pas générer d'erreur etc., l'utilisation d'un déclencheur de type "INSTEAD OF" n'est pas le meilleur moyen. Il faut lui préférer de loin une contrainte de type "valeur par défaut" (DEFAULT), beaucoup plus optimale en terme de performance.
Dès lors que vous optiez pour une contrainte "valeur par défaut" (DEFAULT), pour la colonne "quant", il ne faudra, en revanche, plus mentionner, dans les instructions INSERT ou UPDATE, la valeur NULL explicitement, mais la remplacer par le mot clé DEFAULT, voire ne pas la mentionner du tout dans l'instruction INSERT ou UPDATE.

Ce qui se traduit par :
2-1 - Je supprimerais définitivement le déclencheur

Code SQL :

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

1
2
IF OBJECT_ID(N'dbo.modifier', N'TR') IS NOT NULL
 DROP TRIGGER dbo.modifier

2-2 : Je rajouterais une contrainte défault

Code SQL :

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

ALTER TABLE dbo.test ADD CONSTRAINT DF_test_quant DEFAULT (0) FOR quant;

2-3 : J'utiliserais le cas échéant le mot clé DEFAULT dans les instruction insert ou update. Exemple :

Code SQL :

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

1
2
3
4
5
6
7
UPDATE dbo.test 
 SET quant = DEFAULT 
WHERE id = 2;
 
INSERT INTO test (quant,date) VALUES
 (DEFAULT,'2016-05-07 22:36:52'),
 (2, '2016-05-12 15:00:23')

3 - Plus généralement, il faut éviter d'utiliser ce genre de déclencheur de type "INSTEAD OF" pour le fonctionnent normal de vos applications en production. En effet, ce genre d'approche qui consiste à contourner ou à éviter les erreurs au travers l'utilisation des déclencheurs s'avère très néfaste pour les performances.
Les déclencheurs, au travers l'utilisation inhérente des pseudo table INSERTED et DELETED, assimilables à des tables temporaires, sollicitent énormément la base système tempdb et finissent par dégrader les performances.

4 - En conclusion, restez au stade de l'exercice et de test et n'envisagez surtout pas de passer au stade de la production avec ce genre de déclencheurs !
Exprimez plutôt le besoin réel que vous avez en terme de fonctionnalité. Bien souvent, il y a d'autres solutions mieux appropriées et beaucoup plus optimales en termes de performances.

A+

**Artemus24** · 23/05/2016, 10h56

Salut hmira.

Merci pour votre intervention.

Je tiens à nommer l'objet par son nom, plutôt que de le traduire en français car un déclencheur ne se rapporte pas nécessairement à l'objet trigger, même si l'un est la traduction littérale de l'autre.

Je conserve vos remarques sur les contraintes des valeurs par défaut, qui sont tout a fait pertinente, mais hors de propos par rapport au sujet.

Envoyé par hmira

Exprimez plutôt le besoin réel que vous avez en terme de fonctionnalité.

C'est ce que j'ai fait, mais vous n'en avez pas tenu compte.

Je reconnais que mon exemple n'est pas des plus judicieux et j'en suis désolé.
Mais le but de ce sujet concerne la manipulation des trigger.
Les exercices que je fais sont destinés à l'apprentissage de SQL Server, et non à une quelconque mise en production ou une question de performance.

J'attendais des remarques sur les trigger et non une solution différente à cet exercice.

Voici un deuxième exercice :

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
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
-- ===========
-- Paramétrage
-- ===========
 
SET NOCOUNT    ON
SET DATEFORMAT ymd
 
-- ==================
-- Lien vers Database
-- ==================
 
use tempdb
 
Le contexte de la base de données a changé*; il est maintenant 'tempdb'.
 
-- ============================
-- Suppression Table 'mouchard'
-- ============================
 
IF OBJECT_ID(N'dbo.mouchard', N'U') IS NOT NULL
    DROP TABLE dbo.mouchard
 
-- =========================
-- Création Table 'mouchard'
-- =========================
 
create table dbo.mouchard (
  id      smallint identity(1, 1)        NOT NULL,
  quand   datetime                       NOT NULL default current_timestamp,
  userid  char(06) COLLATE French_CI_AS  NOT NULL default current_user,
  action  char(06) COLLATE French_CI_AS  NOT NULL,
  type    char(03) COLLATE French_CI_AS  NOT NULL,
  clef    smallint                       NOT NULL,
  infos   char(20) COLLATE French_CI_AS  NOT NULL,
  constraint pk_mouchard_id  primary key clustered (id)
)
 
-- ========================
-- Suppression Table 'test'
-- ========================
 
IF OBJECT_ID(N'dbo.test', N'U') IS NOT NULL
    DROP TABLE dbo.test
 
-- =====================
-- Création Table 'test'
-- =====================
 
create table dbo.test (
  id      smallint identity(1, 1) NOT NULL,
  val     char(20)                NOT NULL,
  constraint pk_test_id   primary key clustered (id)
)
 
-- ================================
-- Suppression Trigger 'tgr_insert'
-- ================================
 
IF OBJECT_ID(N'dbo.tgr_insert', N'TR') IS NOT NULL
    DROP TRIGGER dbo.tgr_insert
 
-- ====================
-- Trigger 'tgr_insert'
-- ====================
 
CREATE TRIGGER dbo.tgr_insert
ON dbo.test AFTER insert
AS
BEGIN
  insert into dbo.mouchard (action, type, clef, infos)
    select 'insert' as action,
           'NEW'    as type,
           id       as clef,
           val      as infos
    from inserted
END
 
-- ================================
-- Suppression Trigger 'tgr_update'
-- ================================
 
IF OBJECT_ID(N'dbo.tgr_update', N'TR') IS NOT NULL
    DROP TRIGGER dbo.tgr_update
 
-- ====================
-- Trigger 'tgr_update'
-- ====================
 
CREATE TRIGGER dbo.tgr_update
ON dbo.test AFTER update
AS
BEGIN
  insert into dbo.mouchard (action, type, clef, infos)
    select 'update' as action,
           'OLD'    as type,
           id       as clef,
           val      as infos
    from deleted
 
  insert into dbo.mouchard (action, type, clef, infos)
    select 'update' as action,
           'NEW'    as type,
           id       as clef,
           val      as infos
    from inserted
END
 
-- ================================
-- Suppression Trigger 'tgr_delete'
-- ================================
 
IF OBJECT_ID(N'dbo.tgr_delete', N'TR') IS NOT NULL
    DROP TRIGGER dbo.tgr_delete
 
-- ====================
-- Trigger 'tgr_delete'
-- ====================
 
CREATE TRIGGER dbo.tgr_delete
ON dbo.test AFTER delete
AS
BEGIN
  insert into dbo.mouchard (action, type, clef, infos)
    select 'delete' as action,
           'OLD'    as type,
           id       as clef,
           val      as infos
    from deleted
END
 
-- =====================
-- Insertion dans 'test'
-- =====================
 
INSERT INTO test (val) VALUES
  ('un'),('deux'),('trois'),('quatre'),('cinq')
 
-- =================
-- Vidage des tables
-- =================
 
select * from test
 
id     val
------ --------------------
     1 un
     2 deux
     3 trois
     4 quatre
     5 cinq
 
select * from mouchard
 
id     quand                   userid action type clef   infos
------ ----------------------- ------ ------ ---- ------ --------------------
     1 2016-05-23 10:46:51.080 dbo    insert NEW       5 cinq
     2 2016-05-23 10:46:51.080 dbo    insert NEW       4 quatre
     3 2016-05-23 10:46:51.080 dbo    insert NEW       3 trois
     4 2016-05-23 10:46:51.080 dbo    insert NEW       2 deux
     5 2016-05-23 10:46:51.080 dbo    insert NEW       1 un
 
-- =======================
-- Mise à jour dans 'test'
-- =======================
 
UPDATE test set val = 'two'   where id = 2;
UPDATE test set val = 'three' where id = 3;
 
-- =================
-- Vidage des tables
-- =================
 
select * from test
 
id     val
------ --------------------
     1 un
     2 two
     3 three
     4 quatre
     5 cinq
 
select * from mouchard
 
id     quand                   userid action type clef   infos
------ ----------------------- ------ ------ ---- ------ --------------------
     1 2016-05-23 10:46:51.080 dbo    insert NEW       5 cinq
     2 2016-05-23 10:46:51.080 dbo    insert NEW       4 quatre
     3 2016-05-23 10:46:51.080 dbo    insert NEW       3 trois
     4 2016-05-23 10:46:51.080 dbo    insert NEW       2 deux
     5 2016-05-23 10:46:51.080 dbo    insert NEW       1 un
     6 2016-05-23 10:46:51.223 dbo    update OLD       2 deux
     7 2016-05-23 10:46:51.223 dbo    update NEW       2 two
     8 2016-05-23 10:46:51.223 dbo    update OLD       3 trois
     9 2016-05-23 10:46:51.223 dbo    update NEW       3 three
 
-- =======================
-- Suppression dans 'test'
-- =======================
 
DELETE FROM test where id in (2,3);
 
-- =================
-- Vidage des tables
-- =================
 
select * from test
 
id     val
------ --------------------
     1 un
     4 quatre
     5 cinq
 
select * from mouchard
 
id     quand                   userid action type clef   infos
------ ----------------------- ------ ------ ---- ------ --------------------
     1 2016-05-23 10:46:51.080 dbo    insert NEW       5 cinq
     2 2016-05-23 10:46:51.080 dbo    insert NEW       4 quatre
     3 2016-05-23 10:46:51.080 dbo    insert NEW       3 trois
     4 2016-05-23 10:46:51.080 dbo    insert NEW       2 deux
     5 2016-05-23 10:46:51.080 dbo    insert NEW       1 un
     6 2016-05-23 10:46:51.223 dbo    update OLD       2 deux
     7 2016-05-23 10:46:51.223 dbo    update NEW       2 two
     8 2016-05-23 10:46:51.223 dbo    update OLD       3 trois
     9 2016-05-23 10:46:51.223 dbo    update NEW       3 three
    10 2016-05-23 10:46:51.403 dbo    delete OLD       3 three
    11 2016-05-23 10:46:51.403 dbo    delete OLD       2 two
 
Appuyez sur une touche pour continuer...

Le mécanisme des pseudo tables inserted et deleted correspond à la gestion du new et du old dans les autres SGBD comme DB2 z/os, MySql ou encore firebird.
Mais je trouve que cette façon de faire est d'une extrême lourdeur.
Pourquoi avoir changé l'approche des triggers vis-à-vis des autres SGBD ?

@+

**aieeeuuuuu** · 23/05/2016, 13h10

Bonjour,

Votre premier déclencheur a un bogue de taille : il ne permet pas la mise a jour des autres colonnes de la table :

Code :

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

1
2
 
UPDATE test set date += 1000 where id = 2;

sera sans effet !

en effet, un déclencheur de type "INSTEAD OF" remplace la commande qui l'a déclenché...

**Artemus24** · 23/05/2016, 13h30

Salut aieeeuuuuu.

Envoyé par aieeeuuuuu

Votre premier déclencheur a un bogue de taille : il ne permet pas la mise a jour des autres colonnes de la table :

J'ai fait le test et c'est même pire que ce que tu dis.
Il ne modifie pas la date, mais vient me modifier la colonne "quant" en mettant un zéro à la place de l'ancienne valeur qui est à "2".

Je vais revoir ce trigger afin de tenir compte de ta remarque. Merci !

@+

**aieeeuuuuu** · 23/05/2016, 13h53

non, je ne crois pas.
Ou alors tu as déjà modifié le déclencheur entre temps...

[edit] ou bien tenté de passer la colonne quant à NULL dans la requete UPDATE[/edit]

**SQLpro** · 23/05/2016, 16h26

Envoyé par Artemus24;8639963
Le mécanisme des pseudo tables [c

inserted[/c] et deleted correspond à la gestion du new et du old dans les autres SGBD comme DB2 z/os, MySql ou encore firebird.
Mais je trouve que cette façon de faire est d'une extrême lourdeur.
Pourquoi avoir changé l'approche des triggers vis-à-vis des autres SGBD ?

Pur une raison très simple... C'est Sybase qui a inventé la notion de trigger en 1986 (et par là même la notion de "persistent stored module"). Sybase l'a codifié comme ceci car par nature les déclencheur Sybase étaient ensemblistes (et ils le sont toujours) seule l'utilisation des pseudo tables suffit.

D'ailleurs si l'on veut faire un déclencheur ligne à ligne dans SQL Server il suffit de poser un curseur sur les tables inserted et/ou deleted.

Bien au contraire d'une lourdeur c'est super léger car direct... regardez le nombre de ligne pour un même déclencheur dans les autres SGBDR par rapport à Sybase/SQL Server et vous verrez que vous en avez en moyenne bien moins....

A +

**SQLpro** · 23/05/2016, 16h47

Autre chose, votre exemple est mal choisit, car vous pouvez remplacer l'ensemble de vos 3 déclencheurs par la clause OUTPUT dans les ordres INSERT, UPDATE et DELETE, de cette manière :

Code :

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

1
2
3
4
5
INSERT INTO dbo.test ....
OUTPUT 'insert' as action,
           'NEW'    as type,
           inserted.id       as clef,
           inserted.val      as infos

De même pour DELETE et UPDATE !

A +

**Artemus24** · 23/05/2016, 18h38

Salut aieeeuuuuu.

Je pense avoir résolu mon problème (je parle de mon premier déclencheur).

Voici ce que j'essaye de faire :

1) j'essaye tant bien que mal de simuler un "trigger before insert, update" comme sous DB2 z/os, MySql et Firebird.
Pour ce faire, j'utilise deux colonnes :
a) la colonne "quant" (quantième) qui est "NOT NULL".
b) la colonne "date" qui est aussi "NOT NULL".

Le but de ce test est :
--> mettre "NULL" dans la colonne "quant" lors d'un insert et d'un update.
--> mettre à jour uniquement la colonne "date" sans que la colonne "quant" change.

2) mon trigger se décompose en deux parties, la première relative à l'UPDATE et la seconde à l'INSERT.

3) dans l'UPDATE, je vérifie si les colonnes ont été modifiées.
En cherchant un peu sur le net, j'ai trouvé une fonction bien utile, de nom "UPDATE(nom_de_la_colonne) !
La syntaxe que j'ai utilisé est la suivante :

Code :

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

set quant = case when update(quant) then coalesce(i.quant, 0) else d.quant end,

si la colonne a changé, je récupère la valeur de la pseudo table inserted sinon, je prends la valeur de la pseudo table deleted.
En procédant ainsi, je suis obligé de faire trois jointures. Bonjour la performance !

4) dans l'INSERT, je l'ai décomposé aussi en deux parties :
--> l'insertion quand la ligne n'existe pas dans la table test.
--> une mise à jour, si la ligne existe déjà.

Je rappelle que c'est un test de manipulation d'un trigger before, et n'a aucune autre vocation que de comprendre son fonctionnement. Voici le résultat :

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
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
-- ===========
-- Paramétrage
-- ===========
 
SET NOCOUNT ON
SET DATEFORMAT ymd
 
-- ==================
-- Lien vers Database
-- ==================
 
use tempdb
 
Le contexte de la base de données a changé*; il est maintenant 'tempdb'.
 
-- ========================
-- Suppression Table 'test'
-- ========================
 
IF OBJECT_ID(N'dbo.test', N'U') IS NOT NULL
    DROP TABLE dbo.test
 
-- =====================
-- Création Table 'test'
-- =====================
 
create table dbo.test (
  id      smallint identity(1, 1) NOT NULL,
  quant   integer                 NOT NULL,
  date    datetime                NOT NULL,
  constraint pk_test_id   primary key clustered (id)
)
 
-- ==============================
-- Suppression Trigger 'modifier'
-- ==============================
 
IF OBJECT_ID(N'dbo.modifier', N'TR') IS NOT NULL
    DROP TRIGGER dbo.modifier
 
-- ==================
-- Trigger 'modifier'
-- ==================
 
CREATE TRIGGER dbo.modifier
ON dbo.test INSTEAD OF insert, update
AS
BEGIN
  IF EXISTS ( SELECT * FROM deleted     )
    update dbo.test
      set quant = case when update(quant) then coalesce(i.quant, 0) else d.quant end,
          date  = case when update(date)  then          i.date      else d.date  end
      from       dbo.test as t
      inner join inserted as i
      on i.id = t.id
      inner join deleted  as d
      on d.id = t.id
  ELSE
    IF NOT EXISTS ( select *
                    from       dbo.test as t
                    inner join inserted as i
                    on i.id = t.id )
      insert into dbo.test (id, quant, date)
        select i.id, coalesce(i.quant, 0) as quant, i.date
        from inserted as i
    ELSE
      update dbo.test
        set quant = coalesce(i.quant, 0), date = i.date
        from       dbo.test as t
        inner join inserted as i
        on i.id = t.id
END
 
-- =====================
-- Insertion dans 'test'
-- =====================
 
SET IDENTITY_INSERT tempdb.dbo.test ON
 
INSERT INTO test (id,quant,date) VALUES
  (1,null,'2016-05-07 22:36:52'),
  (2,2,   '2016-05-12 15:00:23')
 
select * from test
 
id     quant       date
------ ----------- -----------------------
     1           0 2016-05-07 22:36:52.000
     2           2 2016-05-12 15:00:23.000
 
-- ===============================
-- Même Insertion que précédemment
-- ===============================
 
INSERT INTO test (id,quant,date) VALUES
  (1,null,'2016-05-07 17:36:52'),
  (2,2,   '2016-05-12 23:00:23')
 
select * from test
 
id     quant       date
------ ----------- -----------------------
     1           0 2016-05-07 17:36:52.000
     2           2 2016-05-12 23:00:23.000
 
-- =======================
-- Mise à jour dans 'test'
-- =======================
 
UPDATE test set date = '2015-12-31 23:59:59' where id = 2
 
select * from test
 
id     quant       date
------ ----------- -----------------------
     1           0 2016-05-07 17:36:52.000
     2           2 2015-12-31 23:59:59.000
 
-- =======================
-- Mise à jour dans 'test'
-- =======================
 
UPDATE test set quant = null where id = 2;
 
select * from test
 
id     quant       date
------ ----------- -----------------------
     1           0 2016-05-07 17:36:52.000
     2           0 2015-12-31 23:59:59.000
 
Appuyez sur une touche pour continuer...

Même remarque que précédemment sur un mauvais choix fait dans cet exemple, ou encore une simplification à faire.

Merci !
@

**Artemus24** · 23/05/2016, 19h47

Salut SQPRO.

Envoyé par SQLPRO

Pour une raison très simple... C'est Sybase qui a inventé la notion de trigger en 1986 (et par là même la notion de "persistent stored module"). Sybase l'a codifié comme ceci car par nature les déclencheur Sybase étaient ensemblistes (et ils le sont toujours) seule l'utilisation des pseudo tables suffit.

Sybase est peut-être l'inventeur de la notion de trigger, mais à part SQL Server, je ne connais aucun autre SGBD qui a repris cette façon de faire.
Si la raison est de conserver la notion ensembliste de la manipulation des données, je veux bien, mais c'est une autre façon de raisonner.
Au départ, c'est déroutant de travailler ainsi. Je pense que c'est une question d'habitude !

Envoyé par SQLPRO

D'ailleurs si l'on veut faire un déclencheur ligne à ligne dans SQL Server il suffit de poser un curseur sur les tables inserted et/ou deleted.

Bonne idée ! Mais cette approche me rappelle la procédure stockée.
N'y a-t-il pas un mélange des genres entre le trigger et la procédure stockée sous SQL Server ?

Envoyé par SQLPRO

Bien au contraire d'une lourdeur c'est super léger car direct... regardez le nombre de ligne pour un même déclencheur dans les autres SGBDR par rapport à Sybase/SQL Server et vous verrez que vous en avez en moyenne bien moins....

Ça dépend de ce que l'on veut faire. En tout cas, pour ma première impression, je trouve ça lourd.

Envoyé par SQLPRO

Autre chose, votre exemple est mal choisit, car vous pouvez remplacer l'ensemble de vos 3 déclencheurs par la clause OUTPUT dans les ordres INSERT, UPDATE et DELETE, de cette manière :

Pas vraiment car mon but est de comprendre la manipulation des triggers sous SQL Server.

En tout cas, merci pour l'information car je ne connaissais pas cette façon de faire. Voici ce j'ai fait :

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
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
-- ===========
-- Paramétrage
-- ===========
 
SET NOCOUNT    ON
SET DATEFORMAT ymd
 
-- ==================
-- Lien vers Database
-- ==================
 
use tempdb
 
Le contexte de la base de données a changé*; il est maintenant 'tempdb'.
 
-- ============================
-- Suppression Table 'mouchard'
-- ============================
 
IF OBJECT_ID(N'dbo.mouchard', N'U') IS NOT NULL
    DROP TABLE dbo.mouchard
 
-- =========================
-- Création Table 'mouchard'
-- =========================
 
create table dbo.mouchard (
  id      smallint identity(1, 1)        NOT NULL,
  quant   datetime                       NOT NULL default current_timestamp,
  userid  char(06) COLLATE French_CI_AS  NOT NULL default current_user,
  action  char(06) COLLATE French_CI_AS  NOT NULL,
  type    char(03) COLLATE French_CI_AS  NOT NULL,
  clef    smallint                       NOT NULL,
  infos   char(20) COLLATE French_CI_AS  NOT NULL,
  constraint pk_mouchard_id  primary key clustered (id)
)
 
-- ========================
-- Suppression Table 'test'
-- ========================
 
IF OBJECT_ID(N'dbo.test', N'U') IS NOT NULL
    DROP TABLE dbo.test
 
-- =====================
-- Création Table 'test'
-- =====================
 
create table dbo.test (
  id      smallint identity(1, 1) NOT NULL,
  val     char(20)                NOT NULL,
  constraint pk_test_id   primary key clustered (id)
)
 
-- =====================
-- Insertion dans 'test'
-- =====================
 
INSERT INTO dbo.test (val)
  output current_timestamp as quant,
         current_user      as userid,
         'insert'          as action,
         'new'             as type,
         inserted.id       as clef,
         inserted.val      as infos
  into dbo.mouchard
  VALUES ('un'),('deux'),('trois'),('quatre'),('cinq')
 
-- =================
-- Vidage des tables
-- =================
 
select * from test
 
id     val
------ --------------------
     1 un
     2 deux
     3 trois
     4 quatre
     5 cinq
 
select * from mouchard
 
id     quant                   userid action type clef   infos
------ ----------------------- ------ ------ ---- ------ --------------------
     1 2016-05-23 19:40:17.020 dbo    insert new       1 un
     2 2016-05-23 19:40:17.020 dbo    insert new       2 deux
     3 2016-05-23 19:40:17.020 dbo    insert new       3 trois
     4 2016-05-23 19:40:17.020 dbo    insert new       4 quatre
     5 2016-05-23 19:40:17.020 dbo    insert new       5 cinq
 
-- =======================
-- Mise à jour dans 'test'
-- =======================
 
UPDATE test set val = 'two'
  output current_timestamp as quant,
         current_user      as userid,
         'update'          as action,
         'old'             as type,
         deleted.id        as clef,
         deleted.val       as infos
  into mouchard
  where id = 2
 
UPDATE test set val = 'three'
  output current_timestamp as quant,
         current_user      as userid,
         'update'          as action,
         'old'             as type,
         deleted.id        as clef,
         deleted.val       as infos
  into mouchard
 
  where id = 3
 
 
-- =================
-- Vidage des tables
-- =================
 
select * from test
 
id     val
------ --------------------
     1 un
     2 two
     3 three
     4 quatre
     5 cinq
 
select * from mouchard
 
id     quant                   userid action type clef   infos
------ ----------------------- ------ ------ ---- ------ --------------------
     1 2016-05-23 19:40:17.020 dbo    insert new       1 un
     2 2016-05-23 19:40:17.020 dbo    insert new       2 deux
     3 2016-05-23 19:40:17.020 dbo    insert new       3 trois
     4 2016-05-23 19:40:17.020 dbo    insert new       4 quatre
     5 2016-05-23 19:40:17.020 dbo    insert new       5 cinq
     6 2016-05-23 19:40:17.307 dbo    update old       2 deux
     7 2016-05-23 19:40:17.360 dbo    update old       3 trois
 
-- =======================
-- Suppression dans 'test'
-- =======================
 
DELETE FROM test
  output current_timestamp as quant,
         current_user      as userid,
         'delete'          as action,
         'old'             as type,
         deleted.id        as clef,
         deleted.val       as infos
  into mouchard
  where id in (2,3)
 
 
-- =================
-- Vidage des tables
-- =================
 
select * from test
 
id     val
------ --------------------
     1 un
     4 quatre
     5 cinq
 
select * from mouchard
 
id     quant                   userid action type clef   infos
------ ----------------------- ------ ------ ---- ------ --------------------
     1 2016-05-23 19:40:17.020 dbo    insert new       1 un
     2 2016-05-23 19:40:17.020 dbo    insert new       2 deux
     3 2016-05-23 19:40:17.020 dbo    insert new       3 trois
     4 2016-05-23 19:40:17.020 dbo    insert new       4 quatre
     5 2016-05-23 19:40:17.020 dbo    insert new       5 cinq
     6 2016-05-23 19:40:17.307 dbo    update old       2 deux
     7 2016-05-23 19:40:17.360 dbo    update old       3 trois
     8 2016-05-23 19:40:17.507 dbo    delete old       2 two
     9 2016-05-23 19:40:17.507 dbo    delete old       3 three
 
Appuyez sur une touche pour continuer...

Il y a deux choses que je n'ai pas pu résoudre :

1) dans ma table mouchard, il y a deux colonnes qui ont des valeurs par défaut : "quant" et "userid".
J'aurai aimé ne pas préciser le nom de ces deux colonnes dans le output. Comme faire ?

2) dans le cas d'un update, j'aurai aimé produire deux lignes dans ma table mouchard.
L'une avec "type=new" et les valeurs venant de "inserted" et l'autre avec "type=old" et les valeurs venant de "deleted".
J'ai cherché mais je crois que la syntaxe de ce output admet qu'une seule table en sortie.

@+

**SQLpro** · 24/05/2016, 10h16

Envoyé par Artemus24

Salut SQPRO.
Sybase est peut-être l'inventeur de la notion de trigger, mais à part SQL Server, je ne connais aucun autre SGBD qui a repris cette façon de faire.

Ce que je veut dire par là c'est qu'ils ont laissé cette syntaxe par soucis de rétro compatibilité et comme elle est suffisamment complète pour tous les cas de figure, aucun intérêt d'en changer.

Pour votre trigger INSEAD of INSERT, voici une solution :

Code :

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

1
2
3
4
5
6
7
8
CREATE TRIGGER E_IU_TEST
ON dbo.test 
INSTEAD OF INSERT
AS
INSERT INTO dbo.test (quant, date)
SELECT COALESCE(quant, DATEPART(dayofyear, COALESCE(date, SYSDATETIME()))), COALESCE(date, SYSDATETIME())
FROM   inserted;
GO

Voyez qu'il se résumé à une seule instruction... Serait-ce aussi simple avec oracle, MySQmerde ou postgreSQL ???

test :

Code :

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

1
2
3
4
5
6
7
INSERT INTO dbo.test DEFAULT VALUES
 
SELECT * FROM dbo.test
 
id     quant       date
------ ----------- -----------------------
1      145         2016-05-24 10:14:26.887

A +

**aieeeuuuuu** · 24/05/2016, 17h37

Envoyé par Artemus24

Le but de ce test est :
--> mettre "NULL" dans la colonne "quant" lors d'un insert et d'un update.
--> mettre à jour uniquement la colonne "date" sans que la colonne "quant" change.

Je n'ai pas bien compris le but... pourquoi mettre null pour quant alors qu'elle n'accepte pas les nulls ?

Envoyé par Artemus24

3) dans l'UPDATE, je vérifie si les colonnes ont été modifiées.
En cherchant un peu sur le net, j'ai trouvé une fonction bien utile, de nom "UPDATE(nom_de_la_colonne) !
La syntaxe que j'ai utilisé est la suivante :

Code :

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

set quant = case when update(quant) then coalesce(i.quant, 0) else d.quant end,

si la colonne a changé, je récupère la valeur de la pseudo table inserted sinon, je prends la valeur de la pseudo table deleted.
En procédant ainsi, je suis obligé de faire trois jointures. Bonjour la performance !

Attention, la fonction UPDATE() indique si la colonne est visée par une mise à jour, pas si les données sont modifiées

par exemple UPDATE(UneColonne) sera vrai pour une requete UPDATE UneTable Set UneColonne=UneColonne.

Je pense que cette fonction n'a donc aucune utilité dans ton cas.

Envoyé par Artemus24

4) dans l'INSERT, je l'ai décomposé aussi en deux parties :
--> l'insertion quand la ligne n'existe pas dans la table test.
--> une mise à jour, si la ligne existe déjà.

cette partie est incorrecte dans le trigger également : le test est à revoir, car si la colonne identity est activée, la valeur pour la colonne id dans le trigger instead of sera toujours 0...

et même si elle fonctionnait (en forçant les valeurs d'identité), le trigger transformerait alors des commandes INSERT en commande UPDATE de façon silencieuse, ce qui n'est pas une bonne idée a mon avis...
En plus il faudrait prendre en compte le cas où certaines lignes existent, d'autres pas.

**aieeeuuuuu** · 24/05/2016, 18h01

Envoyé par Artemus24

1) dans ma table mouchard, il y a deux colonnes qui ont des valeurs par défaut : "quant" et "userid".
J'aurai aimé ne pas préciser le nom de ces deux colonnes dans le output. Comme faire ?

En précisant les colonne cibles de l'insertion :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
INSERT INTO dbo.test (val)
  output 
         'insert'          as action,
         'new'             as type,
         inserted.id       as clef,
         inserted.val      as infos
  into dbo.mouchard (
				action,
				type,
				clef,
				infos
		)  
  VALUES ('un'),('deux'),('trois'),('quatre'),('cinq')

Envoyé par Artemus24

2) dans le cas d'un update, j'aurai aimé produire deux lignes dans ma table mouchard.
L'une avec "type=new" et les valeurs venant de "inserted" et l'autre avec "type=old" et les valeurs venant de "deleted".
J'ai cherché mais je crois que la syntaxe de ce output admet qu'une seule table en sortie.

En effet, je ne pense pas que cela soit possible directement.
Il faudrait un table mouchard comportant chaque colonne en double, une pour les insertion et une pour les suppression...

Mais, pour mettre en place un tel suivi des modifications, le plus simple et encore d'utiliser change data capture. avec cette fonctionnalité, vous aurez bien vos deux lignes lors d'un opération de mise à jour. en plus cela se fait de façon asynchrone, et ça s'active en quelques lignes de code...

**SQLpro** · 25/05/2016, 10h31

Envoyé par Artemus24

Il y a deux choses que je n'ai pas pu résoudre :

1) dans ma table mouchard, il y a deux colonnes qui ont des valeurs par défaut : "quant" et "userid".
J'aurai aimé ne pas préciser le nom de ces deux colonnes dans le output. Comme faire ?

Utiliser INSERTED.* et/ou DELETED.*
Personellement j'utilise du XML et route tout ceci dans une table unique :

-- la table mouchard

Code :

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

1
2
3
4
5
6
CREATE SCHEMA S_MOUCHARD
CREATE TABLE T_HSITORIQUE_DML
(DML_ID          BIGINT IDENTITY PRIMARY KEY,
 DML_DATEHEURE   DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
 DML_XML         XML);
GO

-- pour toutes les tables à pister, le déclencheur :

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
CREATE TRIGGER E_DML_IUD
ON MonSchema.MaTable
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON;
WITH T AS
(
SELECT *, 'INSERTED' AS EVENT_DATA FROM inserted
UNION ALL
SELECT *, 'DELETED'  AS EVENT_DATA FROM deleted
)
INSERT INTO S_MOUCHARD.T_HSITORIQUE_DML (DML_XML)
SELECT CAST((SELECT * FROM T
             FOR XML AUTO, ELEMENTS, ROOT('trigger')) AS XML);
GO

MAIS...

SQL Server est doté de multiples outils très avancés et faire un mouchard comme ceci n'est pas du tout indiqué pour des raisons de performance et de sécurité.
Si vous deviez faire cela en entreprise, alors il suffit d'utiliser DATABASE AUDIT qui permet de tracer pour la sécurité toutes les commandes que l'on souhaite sur toutes les tables et autres objets.

Exemple :
-- création de l'enveloppe du stockage de l'audit des manipulations faites dans la base

USE master;
GO
CREATE SERVER AUDIT DBA_DML
TO FILE ( FILEPATH = 'C:\SQLDATA\' ) ;
GO

-- spécifification de ce que l'on veut tracer, ici toutes les commandes DML (y compris SELECT) sur toutes les tables de la base :

Code :

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

1
2
3
4
5
6
Use MaBase;
GO
CREATE DATABASE AUDIT SPECIFICATION DBA_DML_TABLE
FOR SERVER AUDIT DBA_DML
ADD (SELECT, INSERT, UPDATE, DELETE ON DATABASE::Mabase BY public)
WITH (STATE = ON) ;

PERFORMANCES : ce tracking n'est pas effectué dans des déclencheurs (et donc dans la transaction ce qui allongerait la durée de vérouillage), mais en lisant le journal de transaction de manière asynchrone.
SÉCURITÉ : en cas de plantage du système de tracking (par exemple saturation du disque) l'audit arrête le serveur (SHUTDOWN lancé par l'audit) pour couper les connexion et arrêter le service afin que quelqu'un de mal intentionné n'en profite pas à ce moment.

2) dans le cas d'un update, j'aurai aimé produire deux lignes dans ma table mouchard.
L'une avec "type=new" et les valeurs venant de "inserted" et l'autre avec "type=old" et les valeurs venant de "deleted".
J'ai cherché mais je crois que la syntaxe de ce output admet qu'une seule table en sortie.

@+

Utilisez une requête ensembliste avec UNION ALL comme celle figurant dans le trigger ci avant

A +

**Artemus24** · 25/05/2016, 19h02

Salut à tous.

Envoyé par SQLPRO

Voyez qu'il se résumé à une seule instruction... Serait-ce aussi simple avec oracle, MySQmerde ou postgreSQL ???

Voyez par vous même comment cela se résout avec MySql :

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
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        default character set `latin1`
        default collate       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`     integer unsigned not null auto_increment primary key,
  `quant`  integer unsigned not null,
  `date`   date             not null
) engine=innoDB
  default charset=latin1 collate=latin1_general_ci
  row_format=compressed
--------------
 
--------------
DROP TRIGGER IF EXISTS `modifier`
--------------
 
--------------
CREATE TRIGGER `modifier`
BEFORE INSERT ON `test`
FOR EACH ROW BEGIN
  set NEW.date  = coalesce(NEW.date,  current_date);
  set NEW.quant = coalesce(NEW.quant, dayofyear(NEW.date));
END
--------------
 
--------------
insert into `test` (`quant`,`date`) value (null, null)
--------------
 
--------------
select * from test
--------------
 
+----+-------+------------+
| id | quant | date       |
+----+-------+------------+
|  1 |   146 | 2016-05-25 |
+----+-------+------------+
--------------
commit
--------------
 
--------------
set autocommit = 1
--------------
 
Appuyez sur une touche pour continuer...

En comparant la partie utile de nos deux trigger (pardon déclencheur), nous avons pour SQL Server :

Code :

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

INSERT INTO dbo.test (quant, date) SELECT COALESCE(quant, DATEPART(dayofyear, COALESCE(date, SYSDATETIME()))), COALESCE(date, SYSDATETIME()) FROM inserted;

et nous avons pour MySql :

Code :

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

1
2
set NEW.date  = coalesce(NEW.date,  current_date);
set NEW.quant = coalesce(NEW.quant, dayofyear(NEW.date));

A vous de juger !

Envoyé par aieeeuuuuu

Je n'ai pas bien compris le but... pourquoi mettre null pour quant alors qu'elle n'accepte pas les nulls ?

Vous êtes la deuxième personne à me dire que mon exemple n'est pas très explicite.
Mon but est de comprendre comment fonctionne un "trigger INSTEAD OF insert, update" sous SQL Server.

Je voulais trouver une situation où l'action directe, donc sans déclencheur, puisse être bloquante.
J'ai pensé mettre un "NOT NULL" sur la colonne "quant" et forcer l'insertion (ou le update) avec un "NULL" afin d'avoir un beau message d'erreur.

Ensuite, faire un "trigger INSTEAD OF insert, update" afin de résoudre ce problème.
Dans ce déclencheur, je voulais complexifier le traitement en deux cas :
--> le cas d'une mise à jour, en forçant à [/c]NULL[/c] la colonne "quant" et aussi le cas de la mise à jour de la date seule.
--> le cas de l'insertion, soit pour la première fois dans la table, soit dans le cas où j'aurai pu avoir un message de duplication.

Au final, j'aurai un modèle qui pourra me servir au cas où j'aurai un jour un problème similaire.

Envoyé par aieeeuuuuu

Attention, la fonction UPDATE() indique si la colonne est visée par une mise à jour, pas si les données sont modifiées
par exemple UPDATE(UneColonne) sera vrai pour une requête UPDATE UneTable Set UneColonne=UneColonne.
Je pense que cette fonction n'a donc aucune utilité dans ton cas.

Tout l'intérêt de votre remarque est justement de me donner une solution pour traiter le cas de la mise à jour d'une seule colonne.
En effet, en cherchant sur le net, j'ai trouvé cette fonction update() qui me signale que la colonne doit subir une mise à jour.
Mais n'ayant pas trouvé un exemple simple, j'ai imaginé que cette solution pouvait convenir.
A l'inverse, j'ai trouvé des mises en jour en cascade, où à chaque fois, l'on tester si la colonne à été visé, puis si la valeur à changé, puis enfin la mise à jour uniquement sur cette colonne.
Si par exemple, j'ai cinq colonnes à gérer ainsi, cela fait cinq gros pavé où l'on fait à chaque fois un update. Pas très performant comme approche.

Envoyé par aieeeuuuuu

cette partie est incorrecte dans le trigger également : le test est à revoir, car si la colonne identity est activée, la valeur pour la colonne id dans le trigger instead of sera toujours 0...

En lisant la documentation, j'ai découvert cette particularité où la colonne "identity()" n'est pas renseigné lors d'un insert, sera à zéro dans la pseudo table inserted.
Dans ce nouvel exemple, je me propose de tester la valeur de la colonne "[c]id[\c]" à zéro.
Si c'est égale à zéro alors c'est un insert sinon je fais un update.

Envoyé par aieeeuuuuu

le trigger transformerait alors des commandes INSERT en commande UPDATE de façon silencieuse, ce qui n'est pas une bonne idée a mon avis...

Oui, je suis d'accord que ce n'est pas une façon correcte de travailler.
Mais c'est juste un exercice, histoire de comprendre le fonctionnement de ce type de trigger.

Voici la dernière version de mon déclencheur "INSTEAD OF" :

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
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
-- ===========
-- Paramétrage
-- ===========
 
SET NOCOUNT ON
SET DATEFORMAT ymd
 
-- ==================
-- Lien vers Database
-- ==================
 
use tempdb
 
Le contexte de la base de données a changé*; il est maintenant 'tempdb'.
 
-- ========================
-- Suppression Table 'test'
-- ========================
 
IF OBJECT_ID(N'dbo.test', N'U') IS NOT NULL
    DROP TABLE dbo.test
 
-- =====================
-- Création Table 'test'
-- =====================
 
create table dbo.test (
  id      smallint identity(1, 1) NOT NULL,
  quant   integer                 NOT NULL,
  date    date                    NOT NULL,
  constraint pk_test_id   primary key clustered (id)
)
 
-- ==============================
-- Suppression Trigger 'modifier'
-- ==============================
 
IF OBJECT_ID(N'dbo.modifier', N'TR') IS NOT NULL
    DROP TRIGGER dbo.modifier
 
-- ==================
-- Trigger 'modifier'
-- ==================
 
CREATE TRIGGER dbo.modifier
ON dbo.test INSTEAD OF insert, update
AS
BEGIN
  IF EXISTS ( SELECT * FROM deleted )
    update dbo.test
      set quant = case when update(quant) then coalesce(i.quant, DATEPART(dayofyear, coalesce(i.date, GETDATE()))) else d.quant end,
          date  = case when update(date)  then                                       coalesce(i.date, GETDATE())   else d.date  end
      from       inserted as i
      inner join deleted  as d
      on d.id = i.id
      inner join dbo.test as t
      on t.id = i.id
  ELSE
    if exists ( select * from inserted where id = 0 )
      insert into dbo.test (quant, date)
        select      coalesce(i.quant, DATEPART(dayofyear, coalesce(i.date, GETDATE()))) as quant,
                                                          coalesce(i.date, GETDATE())   as date
        from inserted as i
    ELSE
      update dbo.test
        set quant = coalesce(i.quant, DATEPART(dayofyear, coalesce(i.date, GETDATE()))),
            date  =                                       coalesce(i.date, GETDATE())
        from       inserted as i
        inner join dbo.test as t
        on t.id = i.id
END
 
-- ===============
-- 1 ère Insertion
-- ===============
 
INSERT INTO test (quant,date) VALUES
  (null,null),
  (2,   '2016-05-07'),
  (null,'2016-05-12')
 
select * from test
 
id     quant       date
------ ----------- ----------------
     1         146       2016-05-25
     2           2       2016-05-07
     3         133       2016-05-12
 
-- ================
-- 2 ième Insertion
-- ================
 
INSERT INTO test (quant,date) DEFAULT VALUES
 
select * from test
 
id     quant       date
------ ----------- ----------------
     1         146       2016-05-25
     2           2       2016-05-07
     3         133       2016-05-12
     4         146       2016-05-25
 
-- ========================
-- Insertion sur même clef
-- ========================
 
SET IDENTITY_INSERT tempdb.dbo.test ON
 
INSERT INTO test (id,quant,date) VALUES
  (1,5,   null),
  (2,2   ,'2016-05-15'),
  (3,null,'2016-05-21')
 
select * from test
 
id     quant       date
------ ----------- ----------------
     1           5       2016-05-25
     2           2       2016-05-15
     3         142       2016-05-21
     4         146       2016-05-25
 
-- =================
-- 1 ère Mise à jour
-- =================
 
UPDATE test set date = '2016-06-01' where id = 3
 
select * from test
 
id     quant       date
------ ----------- ----------------
     1           5       2016-05-25
     2           2       2016-05-15
     3         142       2016-06-01
     4         146       2016-05-25
 
-- ==================
-- 2 ième Mise à jour
-- ==================
 
UPDATE test set quant = null where id = 2;
 
select * from test
 
id     quant       date
------ ----------- ----------------
     1           5       2016-05-25
     2         136       2016-05-15
     3         142       2016-06-01
     4         146       2016-05-25
 
Appuyez sur une touche pour continuer...

Ça à l'air de fonctionne.

Envoyé par aieeeuuuuu

En précisant les colonne cibles de l'insertion :

En lisant la documentation, j'ai vu que je devais mettre les noms des colonnes dans la partie "into", comme ci-après :

Code :

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

1
2
3
4
INSERT INTO dbo.test (val)
  output 'insert', 'new', inserted.id, inserted.val
  into dbo.mouchard (action, type, clef, infos)
  VALUES ('un'),('deux'),('trois'),('quatre'),('cinq')

Envoyé par aieeeuuuuu

En effet, je ne pense pas que cela soit possible directement.
Il faudrait une table mouchard comportant chaque colonne en double, une pour les insertion et une pour les suppression...

C'est ce que j'ai compris aussi.
Par contre, dupliquer les colonnes de la table mouchard, juste pour avoir la version "old" et la version "new", n'est pas une solution très judicieuse.

Envoyé par SQLPRO

-- pour toutes les tables à pister, le déclencheur :

Cette solution est plus concise que la solution que j'ai proposé dans le post #3.
Voici le déclencheur que j'ai utilisé :

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
21
-- =============================
-- Création Trigger 'historique'
-- =============================
 
CREATE TRIGGER dbo.historique
ON dbo.test AFTER insert, update, delete
AS
BEGIN
  DECLARE @activity char(06)
 
  set @activity = 'update';
  if     exists (select * from inserted) and not exists (select * from deleted) set @activity = 'insert';
  if not exists (select * from inserted) and     exists (select * from deleted) set @activity = 'delete';
 
  with temp as (select @activity as action, 'new' as type, id as clef, val as infos from inserted
          union select @activity as action, 'old' as type, id as clef, val as infos from deleted
)
insert into dbo.mouchard (action,type,clef,infos)
  select * from temp
END
;

Envoyé par SQLPRO

SQL Server est doté de multiples outils très avancés et faire un mouchard comme ceci n'est pas du tout indiqué pour des raisons de performance et de sécurité.
Si vous deviez faire cela en entreprise, alors il suffit d'utiliser DATABASE AUDIT qui permet de tracer pour la sécurité toutes les commandes que l'on souhaite sur toutes les tables et autres objets.

Je constate de plus en plus que SQL Server est bien plus riche en possibilité que le pauvre MySql.
Je n'ai pas bien compris où va le résultat final.

@+

**SQLpro** · 26/05/2016, 12h16

Envoyé par Artemus24

CREATE TRIGGER `modifier`
BEFORE INSERT ON `test`
FOR EACH ROW BEGIN
set NEW.date = coalesce(NEW.date, current_date);
set NEW.quant = coalesce(NEW.quant, dayofyear(NEW.date));
END

...

En comparant la partie utile de nos deux trigger (pardon déclencheur), nous avons pour SQL Server :

INSERT INTO dbo.test (quant, date)
SELECT COALESCE(quant, DATEPART(dayofyear, COALESCE(date, SYSDATETIME()))), COALESCE(date, SYSDATETIME())
FROM inserted;

et nous avons pour MySql :

set NEW.date = coalesce(NEW.date, current_date);
set NEW.quant = coalesce(NEW.quant, dayofyear(NEW.date));

A vous de juger !

Ce n'est pas la même chose. Le déclencheur SQL insert dans la table mouchard, tandis que celui de MySQL met à jour la table...

Pour un code équivalent sous MySQL, je pense à vu de nez que ce serait :

Code :

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

1
2
INSERT INTO test (quant, date) 
VALUES (coalesce(NEW.date,  current_date), coalesce(NEW.quant, dayofyear(NEW.date)));

Donc en ligne de code, pas loin de la même chose... Mais en terme de performances rien à voir : L'un (MySQmerde) fait du ligne à ligne, l'autre (SQL Server) fait de l'ensembliste.
C'est à peut près la différence qu'il y a entre un coucou d'aéroclub et un avion de chasse au niveau vitesse (et je ne parlerais pas des conséquences en matière de blocage des tables dans les transactions.... y'a pas photo!!! C'est pour cela que MySQL au dela de 10 utilisateurs simultané se plante la gueule !!!)

Vous êtes la deuxième personne à me dire que mon exemple n'est pas très explicite.
Mon but est de comprendre comment fonctionne un "trigger INSTEAD OF insert, update" sous SQL Server.

Je voulais trouver une situation où l'action directe, donc sans déclencheur, puisse être bloquante.

Il faudrait plutôt aller faire des tests sur des assertions (contraintes multitables). Par exemple comment faire pour que la remise maximale accordée à un client soit fonction de son volume de commande sur 12 mois glissants !

...

Par contre, dupliquer les colonnes de la table mouchard, juste pour avoir la version "old" et la version "new", n'est pas une solution très judicieuse.

C'est pourquiu je vous ait proposé de faire un UNION ALL pour avoir les images avant et après concaténées.

Cette solution est plus concise que la solution que j'ai proposé dans le post #3.
Voici le déclencheur que j'ai utilisé :

...

DECLARE @activity char(06)
set @activity = 'update';
if exists (select * from inserted) and not exists (select * from deleted) set @activity = 'insert';
if not exists (select * from inserted) and exists (select * from deleted) set @activity = 'delete';

Simplifiez!!! simplifiez !!! Simplifiez !!!!!!!!!!!!!!!!

Code :

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

1
2
3
4
DECLARE @activity char(06);
SELECT @activity = CASE WHEN NOT EXISTS(SELECT * FROM inserted) THEN 'delete'
                        WHEN NOT EXISTS(SELECT * FROM deleted) THEN 'insert'
                        ELSE 'update' END;

Ce code n'utilise que 2 appels de table. Votre code 4 !

...

Je constate de plus en plus que SQL Server est bien plus riche en possibilité que le pauvre MySql.

Au niveau des fonctionnalités, SQL Server commence depuis peu à dépasser Oracle et se trouve loin devant IBM DB2... C'est pas pour rien que notre livre fait 1265 pages et encore nous n'avons pas traiter tous les sujets... Il y manque manque notamment la réplication de données (a ne pas confondre avec les PCA/PRA), service broker (pour faire des bases de données réparties - grid computing), notifications services (pour être informé de changement d'état de données par "abonnement" à une requête SQL) et tout ce qui concerne le reporting (SSRS), les bases de données OLAP et le datamining (SSAS) et l'ETL intégré (SSIS), sans parler des outils complémentaires comme SQL Server Distributed Replay (pour faire des benchmark en vrai grandeur), Replay Markup Langage (RML, pour vérifier que les migrations se passent bien), Performance Analysis of Log (PAL, un analyseur automatique de performance...)... et j'en passe. Bref, 3 tomes de 1000 pages n'y suffiraient pas !
Au niveau de la sécurité et de la fiabilité, cela fait plus de 10 ans qu'il a dépassé de très loin Oracle (voir les études de Litchfield et co...)
Quand à le comparer à MySQL on est à des années lumières de distance...

Je n'ai pas bien compris où va le résultat final.
@+

Dans un fichier, dont la destination est spécifiée dans la première étape, pour ne pas perturber l'audité ! Ce fichier pouvant d'ailleurs être requêté directement en SQL,via la fonction table : sys.fn_get_audit_file

A +

**Artemus24** · 26/05/2016, 16h20

Salut SQLPRO.

Envoyé par SQLPRO

Ce n'est pas la même chose.

Oui, vous avez raison, ce n'est pas la même façon d'aborder le fonctionnement du trigger.

Envoyé par SQLPRO

Le déclencheur SQL insert dans la table mouchard, ...

Je suis d'accord car sous SQL Server, lors d'un insert, le résultat se trouve d'abord dans la pseudo table [/c]inserted.[/c]
De ce fait, la table est vide puisque le "trigger INSTEAD OF insert" ne fait pas l'insertion.
Et pour les insérer dans la table, vous devez le faire par vous-même.

Ce qui correspond bien à un traitement ensembliste.

Envoyé par SQLPRO

... tandis que celui de MySQL met à jour la table ...

Non, pas du tout. Je rappelle que c'est un "trigger before".
La table est totalement vide. Il existe une pseudo table, qui, inversement à SQL Server, l'utilisateur n'y a pas accès directement sauf au travers du trigger. En faisant ceci :

Code :

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

1
2
set NEW.date = coalesce(NEW.date, current_date);
set NEW.quant = coalesce(NEW.quant, dayofyear(NEW.date));

je viens modifier le contenu de la pseudo table, ligne par ligne.
Et j'ai le choix entre la valeur "OLD" ou "NEW" selon que je me trouve en insert (NEW), update (OLD et NEW) ou delete (OLD).
A la fin du "trigger before", MySql vient insérer les lignes modifiées dans la table.

Donc oui, MySql traite ligne par ligne, mais à partir de sa pseudo table avant de les insérer définitivement dans la table.

Envoyé par SQLPRO

Pour un code équivalent sous MySQL, je pense à vu de nez que ce serait :

Code :

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

1
2
INSERT INTO test (quant, date) 
VALUES (coalesce(NEW.date,  current_date), coalesce(NEW.quant, dayofyear(NEW.date)));

Non, cela ne peut pas fonctionner ainsi, car les préfixes "old" et "new" ne sont accessibles que dans le "trigger".
Et de plus, dans la partie "value", MySql n'accepte que des valeurs.

Un équivalent serait d'insérer les lignes sans contraintes, puis ensuite de faire les corrections qui s'imposent. Ce serait plutôt 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
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        default character set `latin1`
        default collate       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`     integer unsigned not null auto_increment primary key,
  `quant`  integer unsigned     null,
  `date`   date                 null
) engine=innoDB
  default charset=latin1 collate=latin1_general_ci
  row_format=compressed
--------------
 
--------------
insert into `test` (`quant`,`date`) value (null, null)
--------------
 
--------------
select * from test
--------------
 
+----+-------+------+
| id | quant | date |
+----+-------+------+
|  1 |  NULL | NULL |
+----+-------+------+
--------------
replace into `test` (id, quant, date)
  select 1 as id,
         coalesce(quant, dayofyear(coalesce(date, current_date))) as quant,
         coalesce(date,    current_date)                          as date
  from test
--------------
 
--------------
select * from test
--------------
 
+----+-------+------------+
| id | quant | date       |
+----+-------+------------+
|  1 |   147 | 2016-05-26 |
+----+-------+------------+
--------------
commit
--------------
 
--------------
set autocommit = 1
--------------
 
Appuyez sur une touche pour continuer...

Mais cette approche est franchement très mauvaise.

Envoyé par SQLPRO

Donc en ligne de code, pas loin de la même chose... Mais en terme de performances rien à voir : L'un (MySQmerde) fait du ligne à ligne, l'autre (SQL Server) fait de l'ensembliste.

Sur la question performance, je ne suis pas d'accord.
L'approche ensembliste est bien plus coûteuse (nécessite de faire des jointures) que de traiter ligne par ligne les insertions (aucune jointure n'est nécessaire).
Inversement, mais là c'est une autre question, on ne sait pas techniquement parlant comment cela est traité derrière.

Envoyé par SQLPRO

C'est à peut près la différence qu'il y a entre un coucou d'aéroclub et un avion de chasse au niveau vitesse ...

Justement, la comparaison n'est pas judicieuse, puisque la différence va se faire sur la longueur de la piste de décollage.
--> https://fr.wikipedia.org/wiki/Piste_(a%C3%A9ronautique)

Les pistes pour avions légers font en général de 600 à 1 000 mètres de long pour 25 à 45 mètres de large mais les plus courtes peuvent ne faire que 200 m de long et 8 m de large. Celles des grands aéroports avec un trafic d'avions de ligne « gros porteurs » font parfois jusqu'à 5 500 mètres de long pour 45 à 60 mètres de large. La plus longue piste d'atterrissage au monde fut longtemps celle de la Zone 51, d'une distance d'environ 10 km (désaffectée).

MySql est fait pour les petites volumétries et pour les débutants, tandis que SQL Server pour les grosses volumétries et les professionnels.
De plus MySql est devenu le standard pour le développement web.
A chacun son coucou, pardon son avion !

Envoyé par SQLPRO

C'est pour cela que MySQL au dela de 10 utilisateurs simultané se plante la gueule !!!

Pas nécessairement, mais les temps d'accès sont plus long.

Envoyé par SQLPRO

Il faudrait plutôt aller faire des tests sur des assertions (contraintes multitables). Par exemple comment faire pour que la remise maximale accordée à un client soit fonction de son volume de commande sur 12 mois glissants !

Je ne cherche pas à faire une application, mais juste à comprendre les différences de fonctionnement de SQL Server par rapport à ce que je connais déjà.
Entre autre, le "trigger INSTEAD OF". Inversement, je n'ai pas de problème avec le "trigger after".
Au fait, je croyais que le "trigger for" était devenu obsolète et est synonyme du "trigger after".

Envoyé par SQLPRO

Simplifiez!!! simplifiez !!! Simplifiez !!!!!!!!!!!!!!!!

J'ai simplifié et ça fonctionne parfaitement.

Envoyé par SQLPRO

C'est pas pour rien que notre livre fait 1265 pages et encore nous n'avons pas traiter tous les sujets...

Quel livre ?

Il ne fait que 890 pages ! Et à quand la version 2016 ? A moins que vous parlez de votre prochain livre ?

@ Tous : J'aimerai savoir si mon exemple de "trigger instead of" est conforme avant de clôturer le sujet ?

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
21
22
23
24
25
26
27
28
29
30
31
-- ==================
-- Trigger 'modifier'
-- ==================
 
CREATE TRIGGER dbo.modifier
ON dbo.test INSTEAD OF insert, update
AS
BEGIN
  IF EXISTS ( SELECT * FROM deleted )
    update dbo.test
      set quant = case when update(quant) then coalesce(i.quant, DATEPART(dayofyear, coalesce(i.date, GETDATE()))) else d.quant end,
          date  = case when update(date)  then                                       coalesce(i.date, GETDATE())   else d.date  end
      from       inserted as i
      inner join deleted  as d
      on d.id = i.id
      inner join dbo.test as t
      on t.id = i.id
  ELSE
    if exists ( select * from inserted where id = 0 )
      insert into dbo.test (quant, date)
        select      coalesce(i.quant, DATEPART(dayofyear, coalesce(i.date, GETDATE()))) as quant,
                                                          coalesce(i.date, GETDATE())   as date
        from inserted as i
    ELSE
      update dbo.test
        set quant = coalesce(i.quant, DATEPART(dayofyear, coalesce(i.date, GETDATE()))),
            date  =                                       coalesce(i.date, GETDATE())
        from       inserted as i
        inner join dbo.test as t
        on t.id = i.id
END

@+

**SQLpro** · 27/05/2016, 13h09

Envoyé par Artemus24

De plus MySql est devenu le standard pour le développement web.

Ha non, pas du tout... My SQL est utilisé par de petits site web genre blog ou forums. Rarement pour de le vente en ligne...
Si l'on prend le TOP 10 des sites web de vente français on trouve très majoritairement du SQL Server et un peu d'Oracle. Aucun MySQL :
Ventre privées : SQL Server
CDiscount : SQL Server
Fnac.com : SQL Server
Carrefour : SQL Server
Sarneza : SQL Server
Zalendo : SQL Server
Voyages-Sncf.com : Oracle
E.Leclerc : SQL Server
Air France : Oracle
ShowroomPrive : SQL Server
ARAMIS-AUTO : SQL Server
...

...

Au fait, je croyais que le "trigger for" était devenu obsolète et est synonyme du "trigger after".

For et After ont toujours été des synonyme au niveau des déclencheurs dans SQL Server MS. Lorsque Sybase invente les triggers en 1986 c'était FOR et l'événement était après l'insertion. La nécessité des déclencheur BEFORE n'a d'intérêt que si vous voulez ajouter des données dans une colonne (cas de l'objet SEQUENCE inventé par Oracle). Comme SQL Server possédait déjà son mécanisme d'auto incrément via IDENTITY , pas besoin de séquence donc et pas besoin de trigger BEFORE de ce fait ! seuls les déclencheur INSTEAD OF présentent un réel intérêt, notamment lorsqu'ils sont implémentés sur des vues...

J'ai simplifié et ça fonctionne parfaitement.

Quel livre ?

Il ne fait que 890 pages ! Et à quand la version 2016 ? A moins que vous parlez de votre prochain livre ?

Non, il fait bien 1268 pages :
890 imprimées + 378 en ligne !!!!!!

@ Tous : J'aimerai savoir si mon exemple de "trigger instead of" est conforme avant de clôturer le sujet ?

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
21
22
23
24
25
26
27
28
29
30
31
-- ==================
-- Trigger 'modifier'
-- ==================
 
CREATE TRIGGER dbo.modifier
ON dbo.test INSTEAD OF insert, update
AS
BEGIN
  IF EXISTS ( SELECT * FROM deleted )
    update dbo.test
      set quant = case when update(quant) then coalesce(i.quant, DATEPART(dayofyear, coalesce(i.date, GETDATE()))) else d.quant end,
          date  = case when update(date)  then                                       coalesce(i.date, GETDATE())   else d.date  end
      from       inserted as i
      inner join deleted  as d
      on d.id = i.id
      inner join dbo.test as t
      on t.id = i.id
  ELSE
    if exists ( select * from inserted where id = 0 )
      insert into dbo.test (quant, date)
        select      coalesce(i.quant, DATEPART(dayofyear, coalesce(i.date, GETDATE()))) as quant,
                                                          coalesce(i.date, GETDATE())   as date
        from inserted as i
    ELSE
      update dbo.test
        set quant = coalesce(i.quant, DATEPART(dayofyear, coalesce(i.date, GETDATE()))),
            date  =                                       coalesce(i.date, GETDATE())
        from       inserted as i
        inner join dbo.test as t
        on t.id = i.id
END

@+

Un peu lourd, mais pas mal !

Félicitations

A +

**Artemus24** · 27/05/2016, 14h58

Salut SQLPRO.

J'avais bien compris que vous ne jurez que par Microsoft Sql Server.
Et que vous considérez MySql comme une erreur et que cela ne devrait même pas porter le nom de SGBD.

Envoyé par SQLPRO

My SQL est utilisé par de petits site web genre blog ou forums. Rarement pour de le vente en ligne...

Je parlais des hébergeurs qui offrent comme SGBD : PostgreSQL, MySQL, SQLite, CouchDB, MongoDB.
A partir du moment où l'offre est réduite, dans un hébergement mutualisé vous ne pouvez pas faire n'importe quoi.
C'est cette contrainte qui vous conduit à utiliser MySql !

Je parlais aussi de WampServer où MySql est installé nativement.
Quand on est débutant, et que l'on veut se faire la main en php, il va de soi que c'est MySql qui sera votre première base de données.

Le standard des forums est le phpbb3 : https://fr.wikipedia.org/wiki/PhpBB#cite_note-2
Et la plupart du temps, la base de données sous-jacente est le MySql.

Pour la vente en ligne, je connais que PrestaShop et il est développé sous MySql : https://fr.wikipedia.org/wiki/PrestaShop
C'est la plus grosse offre sur le web pour pour créer un site d'e-commerce.

Après si l'on veut monter en puissance, MySql n'est plus du tout adapté à de grosses volumétries, à des problèmes de sécurités, et encore moins en terme de bande passante.
C'est pourquoi votre liste d'entreprises font appels à des SGBD professionnels qui sont capables de stabilités et de performance, comme Microsoft SQL Server.

En terme de classement des SGBD, la question est plus complexe qu'il n'y parait.
Si l'on parle de mainframe (gros système), DB2 Z/OS reste le premier SGBD de part le monde.
Dans le monde du WEB, et chez les hébergeurs, c'est bien MySql qui vient en premier.
Après il y a les deux grands, qui sont d'après ce que j'ai pu lire, dans l'ordre Oracle et Microsoft SQL Server.
Je ne parle même pas des autres SGBD qui sont très loin derrière.

Envoyé par SQLPRO

La nécessité des déclencheur BEFORE n'a d'intérêt que si vous voulez ajouter des données dans une colonne (cas de l'objet SEQUENCE inventé par Oracle). Comme SQL Server possédait déjà son mécanisme d'auto incrément via IDENTITY , pas besoin de séquence donc et pas besoin de trigger BEFORE de ce fait ! seuls les déclencheur INSTEAD OF présentent un réel intérêt, notamment lorsqu'ils sont implémentés sur des vues...

D'après ce que j'ai compris, le déclencheur "instead of" est très peu utilisé, comparativement au déclencheur "after".
La plupart du temps, s'il s'agit de faire des initialisations de colonnes, c'est la contrainte default qui est la solution. Cf. les remarques de hmira dans son post #2.

Envoyé par SQLPRO

Un peu lourd, mais pas mal !

J'aurai dû faire deux déclencheurs, l'un pour le insert et l'autre pour le update.
Ce déclencheur est un cas d'école. Il n'a pas vocation à résoudre un problème mais juste à montrer son fonctionnement.

J'ai repris un déclencheur issu de ce lien : http://sqlpro.developpez.com/cours/s...nsactsql/#L5.4
Il s'agir de l'exemple N° 3. J'ai repris ton exemple en le simplifiant. Il fait exactement la même chose.

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
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
-- ===========
-- Paramétrage
-- ===========
 
SET NOCOUNT    ON
 
-- ==================
-- Lien vers Database
-- ==================
 
use tempdb
 
Le contexte de la base de données a changé*; il est maintenant 'tempdb'.
 
-- ==========================
-- Suppression Table 'client'
-- ==========================
 
IF OBJECT_ID(N'dbo.client', N'U') IS NOT NULL
    DROP TABLE dbo.client
 
-- =======================
-- Création Table 'client'
-- =======================
 
create table dbo.client (
  id         smallint identity(1, 1) NOT NULL,
  nom        varchar(20)             NOT NULL,
  telephone  varchar(30)                 NULL default NULL
  constraint pk_client_id   primary key clustered (id)
)
 
-- =======================
-- Insertion dans 'client'
-- =======================
 
INSERT INTO client (nom,telephone) VALUES ('durand','9- 8-7- 6-5-  4-3-  2-1-0')
 
-- ==================
-- Vidage de 'client'
-- ==================
 
select * from client
 
id     nom                  telephone
------ -------------------- ------------------------------
     1 durand               9- 8-7- 6-5-  4-3-  2-1-0
 
-- ===============================
-- Suppression Trigger 'telephone'
-- ===============================
 
IF OBJECT_ID(N'dbo.telephone', N'TR') IS NOT NULL
    DROP TRIGGER dbo.telephone
 
-- ===================
-- Trigger 'telephone'
-- ===================
 
CREATE TRIGGER dbo.telephone
ON dbo.client AFTER insert, update
AS
BEGIN
  IF NOT UPDATE(telephone) RETURN
 
  DECLARE @id     as smallint
  DECLARE @avant  as varchar(255)
  DECLARE @apres  as varchar(255)
  DECLARE @car    as    char(001)
  DECLARE @i      as integer
  DECLARE @j      as integer
  DECLARE curseur CURSOR for select id, telephone from inserted
 
  OPEN  curseur
  FETCH curseur INTO @id, @avant
 
  WHILE (@@fetch_status = 0)
  BEGIN
    IF (@avant <> '')
    BEGIN
      SET @i = 1
      SET @j = 1
      SET @apres = ''
 
      WHILE (@i <= len(@avant))
      BEGIN
        SET @car = SUBSTRING(@avant,@i,1)
 
        IF (ascii(@car) >= 48) and (ascii(@car) <= 57)
        BEGIN
          IF (@j % 2 = 1) and (@j > 1) SET @apres += '.'
 
          SET @apres += @car
          SET @j     += 1
        END
 
        SET @i += 1
      END
 
      IF (@avant <> @apres) UPDATE client set telephone = @apres where id = @id
    END
 
    FETCH curseur INTO @id, @avant
  END
 
  CLOSE      curseur
  DEALLOCATE curseur
END
 
-- =======================
-- Insertion dans 'client'
-- =======================
 
INSERT INTO client (nom,telephone) VALUES ('tintin', default), ('dupond','12345 6_7-8.90'),('haddock', ''),('rastapopoulos','98765 4_3-2.10')
 
-- ==================
-- Vidage de 'client'
-- ==================
 
select * from client
 
id     nom                  telephone
------ -------------------- ------------------------------
     1 durand               9- 8-7- 6-5-  4-3-  2-1-0
     2 tintin               NULL
     3 dupond               12.34.56.78.90
     4 haddock
     5 rastapopoulos        98.76.54.32.10
 
-- =======================
-- Mise à jour de 'client'
-- =======================
 
update client set nom = 'nestor' where nom = 'durand'
 
-- ==================
-- Vidage de 'client'
-- ==================
 
select * from client
 
id     nom                  telephone
------ -------------------- ------------------------------
     1 nestor               9- 8-7- 6-5-  4-3-  2-1-0
     2 tintin               NULL
     3 dupond               12.34.56.78.90
     4 haddock
     5 rastapopoulos        98.76.54.32.10
 
Appuyez sur une touche pour continuer...

1) Au lien de mettre la liste des chiffres allant de "0" à "9", j'ai utilisé l'intervalle des chiffres de la table ASCII.

2) J'ai supprimé aussi ton effet de bord.

3) Et j'ai aussi supprimé ceci :

Code :

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

IF @@error <> 0 GOTO LBL_ERROR

Normalement, il n'est pas nécessaire de tester le bon fonctionnement des instructions dans le déclencheur.

4) En ce qui concerne la gestion des transactions, ne serait-il pas plus judicieux de les traiter à l'extérieur du déclencheur ?
Mettre un raiserror dans le déclencheur, puis dans la partie procédurale, après l'insert, tester le code retour.
Si le code erreur est <> zéro alors faire un rollback.

5) Question : il n'y a qu'une seule sorte de boucle sous SQL Server : while () do ... end ?

Merci.
@+

**SQLpro** · 03/06/2016, 12h09

Envoyé par Artemus24

En terme de classement des SGBD, la question est plus complexe qu'il n'y parait.
Si l'on parle de mainframe (gros système), DB2 Z/OS reste le premier SGBD de part le monde.
Dans le monde du WEB, et chez les hébergeurs, c'est bien MySql qui vient en premier.
Après il y a les deux grands, qui sont d'après ce que j'ai pu lire, dans l'ordre Oracle et Microsoft SQL Server.
Je ne parle même pas des autres SGBD qui sont très loin derrière.

Non votre classement est faux. les études menées en entreprise montre que DB2 est aujourd'hui à la traine (20%). Il a représenté il y a 15/20 ans la plus grosse part de marché. Il a été double par Oracle il y a plus de 10 ans :
*http://photos1.blogger.com/blogger/6...1600/07814.png
et a son tour oracle est doublé depuis peu par SQL Server, ceci en terme de nombre d'installation, comme en volume !
http://reports.informationweek.com/a...chnology-.html
En ressources c'est encore différent. oracle est devant parce que plus complexe à utiliser et plus complexe à administrer ce qui revient à dire plus cher en TCO et donc plus de monde, de demandes...
http://db-engines.com/en/ranking
En part de marché, c'est différent. une licence oracle coute en moyenne 4 fois plus cher que du SQL Server et jusqu'à 25 fois plus cher. C'est pire pour DB2 ! mais du DB2 il ne s'en vend beaucoup moins et c'est encore plus cher qu'Oracle !
http://www.dadbm.com/wp-content/uplo...enue_share.jpg

D'après ce que j'ai compris, le déclencheur "instead of" est très peu utilisé, comparativement au déclencheur "after".
La plupart du temps, s'il s'agit de faire des initialisations de colonnes, c'est la contrainte default qui est la solution. Cf. les remarques de hmira dans son post #2.

Évidemment c'est beaucoup plus performant...

J'aurai dû faire deux déclencheurs, l'un pour le insert et l'autre pour le update.
Ce déclencheur est un cas d'école. Il n'a pas vocation à résoudre un problème mais juste à montrer son fonctionnement.

J'ai repris un déclencheur issu de ce lien : http://sqlpro.developpez.com/cours/s...nsactsql/#L5.4
Il s'agir de l'exemple N° 3. J'ai repris ton exemple en le simplifiant. Il fait exactement la même chose.

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
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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
-- ===========
-- Paramétrage
-- ===========
 
SET NOCOUNT    ON
 
-- ==================
-- Lien vers Database
-- ==================
 
use tempdb
 
Le contexte de la base de données a changé*; il est maintenant 'tempdb'.
 
-- ==========================
-- Suppression Table 'client'
-- ==========================
 
IF OBJECT_ID(N'dbo.client', N'U') IS NOT NULL
    DROP TABLE dbo.client
 
-- =======================
-- Création Table 'client'
-- =======================
 
create table dbo.client (
  id         smallint identity(1, 1) NOT NULL,
  nom        varchar(20)             NOT NULL,
  telephone  varchar(30)                 NULL default NULL
  constraint pk_client_id   primary key clustered (id)
)
 
-- =======================
-- Insertion dans 'client'
-- =======================
 
INSERT INTO client (nom,telephone) VALUES ('durand','9- 8-7- 6-5-  4-3-  2-1-0')
 
-- ==================
-- Vidage de 'client'
-- ==================
 
select * from client
 
id     nom                  telephone
------ -------------------- ------------------------------
     1 durand               9- 8-7- 6-5-  4-3-  2-1-0
 
-- ===============================
-- Suppression Trigger 'telephone'
-- ===============================
 
IF OBJECT_ID(N'dbo.telephone', N'TR') IS NOT NULL
    DROP TRIGGER dbo.telephone
 
-- ===================
-- Trigger 'telephone'
-- ===================
 
CREATE TRIGGER dbo.telephone
ON dbo.client AFTER insert, update
AS
BEGIN
  IF NOT UPDATE(telephone) RETURN
 
  DECLARE @id     as smallint
  DECLARE @avant  as varchar(255)
  DECLARE @apres  as varchar(255)
  DECLARE @car    as    char(001)
  DECLARE @i      as integer
  DECLARE @j      as integer
  DECLARE curseur CURSOR for select id, telephone from inserted
 
  OPEN  curseur
  FETCH curseur INTO @id, @avant
 
  WHILE (@@fetch_status = 0)
  BEGIN
    IF (@avant <> '')
    BEGIN
      SET @i = 1
      SET @j = 1
      SET @apres = ''
 
      WHILE (@i <= len(@avant))
      BEGIN
        SET @car = SUBSTRING(@avant,@i,1)
 
        IF (ascii(@car) >= 48) and (ascii(@car) <= 57)
        BEGIN
          IF (@j % 2 = 1) and (@j > 1) SET @apres += '.'
 
          SET @apres += @car
          SET @j     += 1
        END
 
        SET @i += 1
      END
 
      IF (@avant <> @apres) UPDATE client set telephone = @apres where id = @id
    END
 
    FETCH curseur INTO @id, @avant
  END
 
  CLOSE      curseur
  DEALLOCATE curseur
END
 
-- =======================
-- Insertion dans 'client'
-- =======================
 
INSERT INTO client (nom,telephone) VALUES ('tintin', default), ('dupond','12345 6_7-8.90'),('haddock', ''),('rastapopoulos','98765 4_3-2.10')
 
-- ==================
-- Vidage de 'client'
-- ==================
 
select * from client
 
id     nom                  telephone
------ -------------------- ------------------------------
     1 durand               9- 8-7- 6-5-  4-3-  2-1-0
     2 tintin               NULL
     3 dupond               12.34.56.78.90
     4 haddock
     5 rastapopoulos        98.76.54.32.10
 
-- =======================
-- Mise à jour de 'client'
-- =======================
 
update client set nom = 'nestor' where nom = 'durand'
 
-- ==================
-- Vidage de 'client'
-- ==================
 
select * from client
 
id     nom                  telephone
------ -------------------- ------------------------------
     1 nestor               9- 8-7- 6-5-  4-3-  2-1-0
     2 tintin               NULL
     3 dupond               12.34.56.78.90
     4 haddock
     5 rastapopoulos        98.76.54.32.10
 
Appuyez sur une touche pour continuer...

1) Au lien de mettre la liste des chiffres allant de "0" à "9", j'ai utilisé l'intervalle des chiffres de la table ASCII.

Il serait beaucoup plus simple et nettement plus efficace de procéder en 2 temps :

Créer une fonction de formatage
Créer un déclencheur ensembliste utilisant cette fonction

Ce afin d'éviter le curseur qui est stupide car il transforme un comportement ensembliste en comportement itératif et donc pourri les performances !

L'udf :

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
CREATE FUNCTION dbo.F_CLEAN_TEL (@TEL_IN VARCHAR(32))
RETURNS VARCHAR(32)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
   DECLARE @TEL_OUT VARCHAR(32) = '', @C CHAR(1), @I TINYINT = 1;
   WHILE @I <= LEN(@TEL_IN)
   BEGIN
      SET @C = SUBSTRING(@TEL_IN, @I, 1)
      IF @C BETWEEN '0' AND '9' OR @C = '.'
         SET @TEL_OUT = @TEL_OUT + @C;
      SET @I = @I + 1;
   END;
   RETURN @TEL_OUT
END;
GO

Le déclencheur :

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TRIGGER dbo.telephone
ON dbo.client 
AFTER insert, update
AS
UPDATE T
   SET telephone = dbo.F_CLEAN_TEL (telephone)
FROM   dbo.client AS T
       JOIN inserted AS i
            ON T.id = i.id
GO

3 fois plus simple et 1000 fois plus rapide !

2) J'ai supprimé aussi ton effet de bord.

3) Et j'ai aussi supprimé ceci :

Code :

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

IF @@error <> 0 GOTO LBL_ERROR

Normalement, il n'est pas nécessaire de tester le bon fonctionnement des instructions dans le déclencheur.

[/CODE]
FAUX : ce n'est pas parce qu'il y a erreur que la transaction est annulée. Une transaction doit toujours se finaliser par COMMIT ou ROLLBACK. L'erreuer sera donc propagée et la transaction reste vivante...

Code :

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

1
2
3
4
5
6
 
 
 
4) En ce qui concerne la gestion des transactions, ne serait-il pas plus judicieux de les traiter à l'extérieur du déclencheur ?
Mettre un raiserror dans le déclencheur, puis dans la partie procédurale, après l'insert, tester le code retour.
Si le code erreur est <> zéro alors faire un rollback.

Si tu désire que les performances soient catastrophique c'est comme cela qu'il faut procéder !
Ne t'as ton pas appris qu'en cas d'erreur il faut traiter le problème au plus tôt ?
La tu laisse une transaction ouverte donc des tables bloquées alors qu'on ne sait pas quoi faire à cause de l'erreur...
Imagine un train dont un wagon à d"éraillé et le conducteur qui dit, ben c'est pas grave on va continuer jusqu’à la station suivante ????
[CODE]

5) Question : il n'y a qu'une seule sorte de boucle sous SQL Server : while () do ... end ?

Oui, par ce que le WHILE est théoriquement suffisant pour tout type de boucle. Je t'aid déjà dit que Transact SQL était un langage simple, avec un jeu d'instruction très réduit destiné à la productivité. C'est dans cet esprit que seul existe la boucle WHILE. Note que tu peut utiliser aussi le CASE en dehors des requêtes SQL.
De la mème maniere il n'y a pas de ENDIF de ELSIF qui est totalement inutile !
Bref les instructions de branchement se résument à :
Nom : TransactSQL.png
Affichages : 2844
Taille : 146,2 Ko