Les triggers sous SQL Server !

Version imprimable

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:

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 !
@+

22/05/2016, 23h04
hmira
Citation:

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:

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:

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:

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+

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.

Citation:

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:

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 ?

@+

23/05/2016, 13h10
aieeeuuuuu
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:

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é...
23/05/2016, 13h30
Artemus24

Salut aieeeuuuuu.

Citation:

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 !

@+
23/05/2016, 13h53
aieeeuuuuu

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]
23/05/2016, 16h26
SQLpro

Citation:

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 +
23/05/2016, 16h47
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 :
Code:

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 +

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:

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:

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 !
@

Salut SQPRO.

Citation:

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 !

Citation:

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 ?

Citation:

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.

Citation:

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:

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.

@+

Citation:

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:

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:

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 +

24/05/2016, 17h37
aieeeuuuuu

Citation:

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 ?

Citation:

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:

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.

Citation:

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.

Citation:

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:

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')
Citation:

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...

Citation:

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:

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:

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

Citation:

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:

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.

Citation:

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 +

Salut à tous.

Citation:

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:

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:

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:

1 2 set NEW.date = coalesce(NEW.date, current_date); set NEW.quant = coalesce(NEW.quant, dayofyear(NEW.date));
A vous de juger !

Citation:

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.

Citation:

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.

Citation:

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.

Citation:

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:

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.

Citation:

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:

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')
Citation:

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.

Citation:

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:

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 ;
Citation:

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.

@+

Citation:

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:

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 !!!)

Citation:

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 !

Citation:

...

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.

Citation:

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:

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 !

Citation:

...

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...

Citation:

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 +

Salut SQLPRO.

Citation:

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.

Citation:

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.

Citation:

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:

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.

Citation:

Envoyé par SQLPRO

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

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:

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.

Citation:

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.

Citation:

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)

Citation:

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 !

Citation:

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.

Citation:

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".

Citation:

Envoyé par SQLPRO

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

J'ai simplifié et ça fonctionne parfaitement.

Citation:

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 ?

http://ecx.images-amazon.com/images/...UL._AA160_.jpg

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:

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
@+

Citation:

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
...

Citation:

...

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...

Citation:

J'ai simplifié et ça fonctionne parfaitement.

Quel livre ?

http://ecx.images-amazon.com/images/...UL._AA160_.jpg

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 !!!!!!
Citation:
@ Tous : J'aimerai savoir si mon exemple de "trigger instead of" est conforme avant de clôturer le sujet ?
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 -- ================== -- 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 +

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.

Citation:

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.

Citation:

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.

Citation:

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:

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:

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.
@+

1 pièce(s) jointe(s)

Citation:

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

Citation:

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...
Citation:
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:

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:

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:

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 !
Citation:
2) J'ai supprimé aussi ton effet de bord.

3) Et j'ai aussi supprimé ceci :

Code:

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:

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 à :
Pièce jointe 211701
Ceci est extrait d'un mémento SQL Server imprimé en cours de maquetage et qui sera disponible d'ici peu....

A +

03/06/2016, 15h44
escartefigue

Citation:

Envoyé par SQLpro

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

Tous ces classements sont à prendre avec prudence, car selon que l'on mesure le nombre de licences, d'utilisateurs connectés, de téraoctets stockés ou tout autre critère, on a un résultat différent.
Pour ce qui concerne DB2, il reste de fait le standard du marché, mais uniquement si l'on considère la version DB2 for Z/OS et pour cause, il fait partie de l'offre globale sur le marché captif du mainframe, dont les fournisseurs sont IBM pour le cœur du système SGBD inclus, et Computer Associate pour les outils associés (il y en a quelques autres qui se partagent les miettes)
Par contre la version DB2 open est effectivement en concurrence avec MS SQL server et autres SGBD ouverts, c'est là que le marché est concurrentiel.
03/06/2016, 16h58
SQLpro

Citation:

Envoyé par escartefigue

Tous ces classements sont à prendre avec prudence, car selon que l'on mesure le nombre de licences, d'utilisateurs connectés, de téraoctets stockés ou tout autre critère, on a un résultat différent.
Pour ce qui concerne DB2, il reste de fait le standard du marché, mais uniquement si l'on considère la version DB2 for Z/OS et pour cause, il fait partie de l'offre globale sur le marché captif du mainframe, dont les fournisseurs sont IBM pour le cœur du système SGBD inclus, et Computer Associate pour les outils associés (il y en a quelques autres qui se partagent les miettes)
Par contre la version DB2 open est effectivement en concurrence avec MS SQL server et autres SGBD ouverts, c'est là que le marché est concurrentiel.

Mais même en mainframe IBM vends de moins en moins, et la plupart des gens préfèrent passer à Linux, même en conservant la solution chez IBM.
Beaucoup de grosses boîte migrent vers Linux ou dans le cloud en ce moment... Ce que IBM n'ravait pas prévu !
Mon voisin en Provence est un des gros vendeurs IBM de mainframe EMEA pour de l'hébergement data et à du se mettre au cloud et à Linux alors qu'il est à 2 ans de la retraite...

A +
03/06/2016, 21h15
elsuket

Bonjour,

J'aimerai revenir sur quelques éléments :

Citation:

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.

Les moteurs de bases de données relationnelles SQL sont conçus et optimisés pour exécuter des jointures. Par exemple dans SQL Server, il y a 3 algorithmes de jointure.
Si spécifier des jointures dans un moteur de bases de données relationnel est un problème, alors il faut enlever le R de SGBDR.

Traiter les lignes une à une est donc un non-sens pour tout SGBDR SQL, et je vous mets au défi de mesurer les temps avec un curseur ou un WHILE dans un trigger, quel que soit le SGBDR.

Citation:

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.

Je ne vois pas en quoi cela va de soi. C'est plutôt une solution de facilité, parce que l'ensemble W/LAMP est livré en bundle, qui plus est gratuit et libre.
Pour avoir bossé dans plusieurs boîtes web, j'ai vu du Php, du Java, du Delphi (d'ailleurs migré depuis Oracle) et du C# sur du SQL Server.
Entre un site web marchand et un site web de blogs ou forums ou qui réalise un faible nombre de transactions de paiement, il y a quand même une différence.

Citation:

C'est la plus grosse offre sur le web pour pour créer un site d'e-commerce.

Là encore on parle de "petits" sites; rien à voir par exemple avec ceux que cite SQLPro.

Il y a quelques années, j'ai rencontré un DBA Oracle qui me disait que son employeur commençait à considérer SQL Server 2005. Dès qu'il a démarré sur cet opus, il a trouvé cela beaucoup plus simple qu'Oracle, simplement pour les sauvegardes (Rman vs. BACKUP DATABASE ou si on a la flème, un plan de maintenance). Petit à petit, son employeur qui a grossi est arrivé à 50-50 en terme de nombre de licences. Aujourd'hui, ils migrent doucement les bases Oracle vers de nouvelles instances SQL Server. Motif invoqué par DSI : côut des licences, quantité de personnel par base de données ... C'est un cas isolé me direz-vous, mais le fait est que c'est un mouvement que l'on observe en France, aux Etats-Unis et en Thaïlande, où Oracle est très bien implanté, notamment dans les banques. Je cite ces trois pays car j'y ai travaillé.

Par ailleurs je n'ai jamais vu Microsoft faire de la pub pour SQL Server en première page du Washington Post, comme Oracle, qui par ailleurs ne dispose pas d'une plateforme cloud.
Il suffit enfin de regarder les tests TPC pour voir ce que l'on peut faire avec SQL Server, qui y est très présent, alors que plusieurs éditeurs de SGBDR, dont Oracle, contribuent à cette association.

@++ ;)
03/06/2016, 22h27
Artemus24
Salut SQPRO.
Citation:
Envoyé par SQLPRO

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
Oui, entièrement d'accord, sauf que le but de cet exercice est d'apprendre et de manipuler les déclencheurs.

Citation:

Envoyé par SQLPRO

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

Même remarque sur le curseur. C'est juste pour apprendre comment fonctionne SQL Server.
Je sais que mes exercices ne sont pas conforme à l'usage que j'en fais.

Citation:

Envoyé par SQLPRO

FAUX : ce n'est pas parce qu'il y a erreur que la transaction est annulée.

Je n'ai jamais dit cela.

Citation:

Envoyé par SQLPRO

Une transaction doit toujours se finaliser par COMMIT ou ROLLBACK.

Oui, je suis d'accord sur le principe, mais je ne parle pas supprimer cette finalité.
Vous mettez le "begin transaction au niveau le plus général dans le script, et vous testez le bon fonctionnement d'un ordre en faisant soit un "commit" ou un "rollback" dans le déclencheur.
Tout ce que je dis, c'est de mettre le "commit" ou le "rollback" au même niveau que le "begin transaction.
Il n'a jamais été question de les supprimer.

Citation:

Envoyé par SQLPRO

L'erreur sera donc propagée et la transaction reste vivante...

Je sais très bien que tout démarrage d'une transaction doit à un moment ou une autre se terminer par un "commit" ou un "rollback".
C'est juste que je ne suis pas d'accord avec vous, sur l'emplacement de cette finalité, ni sur la façon dont vous le gérez. Pourquoi ?
Je ne voie aucun point de reprises et encore moins un traitement ligne par ligne.
De ce fait, le traitement ensembliste est du genre : soit tout est validé, soit tout est rejeté.
Autant le faire à l'extérieur, dans le script après les insert. Cela ne va pas changer le fonctionner du mode transactionnel.

Citation:

Envoyé par SQLPRO

Si tu désire que les performances soient catastrophique c'est comme cela qu'il faut procéder !

Si vous voulez améliorer les performances, dans ce cas, on ne fait pas un traitement tout ou rien sur la globalité de ce que l'on cherche à charger.
On fait cela par lots de N lignes. Imaginons un seul instant que le plantage se fasse sur la dernière ligne des 1.000.000 lignes à charger.
SQL Server va devoir défaire (Rollback) tout ce qui a été traité mais non validé (Commit). Si vous trouvez cela performant, ce n'est pas mon cas.
Inversement, si vous avez des lots de 10.000 lignes, vous aurez 990.000 lignes qui auront été validés (Commit), mais les 10.000 dernières seront rejetés (Rollback).
Avec un point de reprise, on ne traite à nouveau que le dernier lot de 10.000 lignes.

Ce point de reprise ne peut pas se gérer dans un déclencheur ou autre chose, mais au niveau procédurale, dans le script.
D'où ma remarque de mettre le "commit" ou le "rollback" au même niveau que le "begin transaction.

Citation:

Envoyé par SQLPRO

Ne t'as ton pas appris qu'en cas d'erreur il faut traiter le problème au plus tôt ?

Oui, je le sais, mais on ne le fait pas n'importe où ! C'est de cela dont je parle !
La notion de grappe de validité fait que le "rollback" ou le "commit" doivent se faire sur un point de cohérence du traitement.
Si vous avez deux tables qui sont en relation, vous l'allez pas valider l'une, quand l'autre sera rejeté.
De ce fait, vous créez un problème d'intégrité dans la base de données !!!

Citation:

Envoyé par SQLPRO

Oui, par ce que le WHILE est théoriquement suffisant pour tout type de boucle.

C'est une approche minimaliste de la programmation.

Il existe quatre cas :
--> la boucle "pour i de 1 à n par-pas-de 1 faire ... fin-faire".
--> la boucle "répéter ... jusqu'à (condition)".
--> la boucle "tant que (condition) faire ... fin_faire".
--> la boucle "itérer ... sortir si (condition) ... fin-itérer".

Elles ont chacune leur utilité.
C'est dans ce quatrième cas que l'on retrouve les mots clef "break" (sortir de la boucle) ou "continue" (passer à la prochaine itération).
Mais comme aujourd'hui, on se permet une certaine permissivité dans les structures dite algorithme, on mélange des notions qui n'ont pas lieu d'être.
Et tout ça, pour être minimaliste.

Citation:

Envoyé par SQLPRO

De la même manière il n'y a pas de ENDIF de ELSIF qui est totalement inutile !

Ce qui est source d'erreur ! Par exemple :
Code:

1 2 3 if (@test = 1) if (@val = 'oui) set @res = 'ok' else set @res = 'ko'
Il "else va t-il se rapporter au premier ou au second "if" ?
Ce qui nécessite d'introduire le bloc "begin ... end" afin de clarifier la syntaxe du "if". D'où !
Code:

1 2 3 4 5 if (@test = 1) begin if (@val = 'oui) set @res = 'ok' end else set @res = 'ko'
ou bien
Code:

1 2 3 4 5 if (@test = 1) begin if (@val = 'oui) set @res = 'ok' else set @res = 'ko' end
Encore que le deuxième exemple est incorrecte en terme de définition du périmètre de la variable @val.
Rien n'indique qu'elle admet que deux valeurs, à savoir "oui" et "non".
Et que l'on doit nécessairement écraser la valeur existante de @res dans le cas du else que l'on interprète à tort, comme étant l'équivalent du 'non'.

Citation:

Envoyé par SQLPRO

Ceci est extrait d'un mémento SQL Server imprimé en cours de maquetage et qui sera disponible d'ici peu...

J'aime bien les mémentos car j'ai toujours des problèmes à me souvenir de la syntaxe exacte.

Citation:

Envoyé par Artemus24

Et à quand la version 2016 ?

Vous n'avez pas répondu à ma question ?
Je parlais bien sûr de la version express 2016 qui doit être sortie (le 1er juin 2016).

@+
04/06/2016, 00h34
Artemus24

Salut elsuket.

Citation:

Envoyé par elsuket

Par exemple dans SQL Server, il y a 3 algorithmes de jointure.

Pouquoi dans SQL Server ? Est-ce parce que nous nous trouvons dans le forum consacré à SQL Server ?

A moins de me tromper, ce sont :
--> nested-loop join,
--> hash-based join
--> sort/merge join

Je disais plus simplement que la jointure est plus coûteuse en terme de performance que de faire un simple accès sans jointure.

Citation:

Envoyé par elsuket

Traiter les lignes une à une est donc un non-sens pour tout SGBDR SQL

Pourquoi est-ce un non sens ? Vous oublier que la question de la performance est le critère qui détermine les bons choix.

Citation:

Envoyé par elsuket

je vous mets au défi de mesurer les temps avec un curseur ou un WHILE dans un trigger, quel que soit le SGBDR.

Mais pas rapport à quoi ?

Si j'ai le choix entre un traitement avec une jointure (donc sur deux tables), et un traitement sur une seule table (du genre curseur), vous affirmez que la jointure est plus performante ?
Il y a tellement de paramètres qui peuvent infirmer cette idée que je ne comprends pas comment pouvez vous être aussi affirmatif.

Citation:

Envoyé par elsuket

Je ne vois pas en quoi cela va de soi.

Vous êtes sous windows et vous cherchez à intaller un serveur WEB. Qu'est-ce que vous allez prendre ?
Dans la plupart des cas, c'est Wamp (Windows, Apache, MySql et PhpMySql) qui est installé.
Pour EasyPhp, on a le choix entre MySQL, Nginx et PostgreSQL.
Pour IIS de Microsoft, il n'y a pas de SGBD. C'est juste un serveur web.

Citation:

Envoyé par elsuket

C'est plutôt une solution de facilité

Nous sommes d'accord, car MySql est installé nativement dans ces packages.
Et de plus, on trouve aussi chez les hébergeurs MySql et Postgres.
Donc pourquoi vouloir faire autre chose comme SGBD s'ils ne sont pas disponible chez les hébergeurs ?

Citation:

Envoyé par elsuket

Entre un site web marchand et un site web de blogs ou forums ou qui réalise un faible nombre de transactions de paiement, il y a quand même une différence.

Oui, il y a une différence. Pourquoi payer si on peut l'avoir gratuitement ?
Après, si vous voulez la stabilité, la performance, la sécurité, le respect des normes, et bien il faut mettre le prix.
Mais pour un débutant, ce ne sont pas des critères importants.

Citation:

Envoyé par elsuket

Il y a quelques années, j'ai rencontré un DBA Oracle qui me disait que son employeur commençait à considérer SQL Server 2005. Dès qu'il a démarré sur cet opus, il a trouvé cela beaucoup plus simple qu'Oracle, simplement pour les sauvegardes (Rman vs. BACKUP DATABASE ou si on a la flemme, un plan de maintenance). Petit à petit, son employeur qui a grossi est arrivé à 50-50 en terme de nombre de licences. Aujourd'hui, ils migrent doucement les bases Oracle vers de nouvelles instances SQL Server. Motif invoqué par DSI : coût des licences, quantité de personnel par base de données ... C'est un cas isolé me direz-vous, mais le fait est que c'est un mouvement que l'on observe en France, aux Etats-Unis et en Thaïlande, où Oracle est très bien implanté, notamment dans les banques. Je cite ces trois pays car j'y ai travaillé.

Votre exemple démontre ce que j'essaye d'affirmer.
Si vous avez deux offres comparables, l'une chère et l'autre beaucoup moins. Que faites vous ?
Vous restez fidèle à vos convictions de départ et tant pis si cela plombe votre budget.
On bien vous prenez celui qui est le plus attractif ?

Et bien, le débutant, il fait le même choix que le professionnel ! C'est cela qui vous parait bizarre ?

Ensuite, il y a le coût de la maintenance qui n'est pas négligeable.
La migration de l'ancien SGBD vers, par exemple, SQL Server. Cela peut poser des problèmes.
Les compétences professionnelles que l'on trouve sur le marché du travail.
La rapidité de l'évolution du SGBD par rapport aux nouvelles normes
Et surtout, est-ce que cela répond aux besoins de mes projets ?

Si SQL Server a le vent en poupe, ce n'est pas pour rien.
Car Oracle est vieillissant, et n'a pas la même marche de manœuvre de Microsoft.

Je ne critique aucun SGBD. Ils ont tous des qualités et des défauts.
Mais au final, c'est celui qui est le plus adaptatif (comme dans le darwinisme) qui va remporter la victoire.
La sélection naturel se fait surtout sur les défauts que sur les qualités du produit.

@+
04/06/2016, 13h02
elsuket

Citation:

Oui, il y a une différence. Pourquoi payer si on peut l'avoir gratuitement ?
Après, si vous voulez la stabilité, la performance, la sécurité, le respect des normes, et bien il faut mettre le prix.
Mais pour un débutant, ce ne sont pas des critères importants.

SQL Server Express est gratuit. Les hébergeurs ne le mettent à disposition parce que ce n'est pas ce que les développeurs demandent.
En ce qui concerne la stabilité, les performances, la sécurité, le respect des normes, SQL Server Express n'est pas amoindri du fait de cette édition.

Donc c'est un choix qui est fait; MySQL pour ne citer que lui n'est pas proche de la norme SQL.
Côté performance et sécurité, on sait ce qu'il en est par rapport à SQL Server.

Ce n'est pas un critère important pour un débutant parce que le SQL est enseigné de façon assez légère : en tout cas c'est ce que je trouve.
Donc on commence à faire du développement sur un ersatz d'SGBDR, et ensuite en entreprise on retrouve un modèle catastrophique avec des tables obèses, inindexable, avec des requêtes horribles. Rares sont les entreprises qui commencent par modéliser la base de données, parce que tout le monde a démarré avec un SGBD pauvre, où on peut mettre trois tables qui ont peu de lignes et cela "satisfait". Quand on demande à voir le modèle, en général on a des yeux ronds; moins souvent le modèle physique, et rarement le modèle conceptuel.

Citation:

Pouquoi dans SQL Server ? Est-ce parce que nous nous trouvons dans le forum consacré à SQL Server ?

Je disais cela parce que je ne connais pas les algorithmes de jointure dans les autres SGBDR.

Citation:

Je disais plus simplement que la jointure est plus coûteuse en terme de performance que de faire un simple accès sans jointure. [...]
Pourquoi est-ce un non sens ? Vous oublier que la question de la performance est le critère qui détermine les bons choix. Mais pas rapport à quoi ?
[...]
Si j'ai le choix entre un traitement avec une jointure (donc sur deux tables), et un traitement sur une seule table (du genre curseur), vous affirmez que la jointure est plus performante ?
Il y a tellement de paramètres qui peuvent infirmer cette idée que je ne comprends pas comment pouvez vous être aussi affirmatif.

Seriez-vous prêt à affirmer qu'une table avec 200 colonnes vaut mieux à tout type d'accès que la même table correctement modélisée, c'est à dire explosée en plusieurs autres tables ?
Justement, la modélisation permet d'allier une organisation des données aux performances. Par ailleurs en termes d'intégrité des données, de situations de blocage, ... il n'y a pas photo.
En ce qui concerne un traitement avec un curseur, je vous renvoie à ce petit test que j'ai fait : le constat est sans appel.
En ce qui concerne les tables obèses, je vous renvoie vers le billet de SQLPro sur ce sujet.

Prouvez-nous donc le contraire, mais pas avec 4 lignes qui se battent en duel ;)

Je suis en train de remodéliser petit bout par petit bout une base de données pour un éditeur de logiciels, où l'on trouve des tables qui comptent jusqu'à 400 colonnes.
Parfois elles terminent explosées en plus de 20 tables, et comme on le fait petit bout par petit bout, celles que l'on avait déjà crées par normalisation sont intégrées sans difficulté au nouveau modèle. Pour obtenir le même résultat, non seulement les requêtes sont bien plus simples à exprimer (ce sont les Développeurs eux-même qui m'en font part) que ce qu'elle était à l'origine, les index sont bien plus petits et le côut de stockage est bien amoindri, mais surtout, les performances sont bien plus élevées. Si l'on compare les plans d'exécution, sans même tenir compte du coût, là encore après remodélisation, il y a très peu d'optimisation à effectuer : elle est principalement constituée par l'ajout d'index qui couvrent mieux la requête à l'étude.

D'ailleurs, c'est assez drôle puisque la migration de MySQL à SQL Server avec le même modèle et les mêmes données a été opérée par cet éditeur de logiciels lorsque SQL Server 2005 est sorti.
Le DSI a continuellement migré toute ses bases de données de MySQL à SQL Server. Pourquoi pensez-vous qu'il a fait ce choix ? En tout cas cela lui a permis de rester jusqu'il y a peu sans DBA ;)

Citation:

Donc pourquoi vouloir faire autre chose comme SGBD s'ils ne sont pas disponible chez les hébergeurs ?

Donc si je ne vous vend qu'un seul produit, vous n'allez pas chercher ailleurs pour voir s'il n'y pas un produit similaire qui vous conviendrait mieux ?

Citation:

Si vous avez deux offres comparables, l'une chère et l'autre beaucoup moins. Que faites vous ?
Vous restez fidèle à vos convictions de départ et tant pis si cela plombe votre budget.
On bien vous prenez celui qui est le plus attractif ?

Détrompez vous; que ce soir professionnellement comme lors de mes achats, j'essaie de savoir pourquoi l'un est plus cher que l'autre. Quand on passe de gratuit à payant, il y a nécessairement une différence. Pour avoir aussit fait l'expérience du gratuit ou libre par rapport au payant dans l'entreprise, je n'ai fait qu'un constat : quand le SGBD (R ou pas) est libre, il faut plus de personnel pour le maintenir. Donc on fait des économies sur le prix de la licence qui sont écrasées par le côut de maintenance et le nombre de machines, et ceci sans compter les salaires : il faut bien payer les DBA, quel que soit le SGBD sur lequel ils sont spécialisés :D. Donc en terme de coût, le plus attractif n'est pas toujours celui que l'on croit.

Citation:

Je ne critique aucun SGBD. Ils ont tous des qualités et des défauts.
Mais au final, c'est celui qui est le plus adaptatif (comme dans le darwinisme) qui va remporter la victoire.
La sélection naturel se fait surtout sur les défauts que sur les qualités du produit.

Je crois effectivement que nous sommes d'accord; parfois il est difficile de se comprendre multuellement par écrits interposés.
C'est bien d'avoir le choix; il est évident que les éléments de la palette permettent de choisir, et le portefeuille pèse lourdement sur ce choix.
Cela ne change pas que le modèle est fondamental; les comparaisons effectuées par les tests TPC parlent d'eux-même : hardware équivalent, base de données identiques.
On mesure la durée d'exécution de la charge, avec des métriques hardware, sur plusieurs SGBDR, et on compare. On voit ça ensemble autour de quelques pintes ? ;)

@++ ;)

Salut elsuket.

Je comprends surtout que nous ne parlons pas de la même chose.

Citation:

Envoyé par elsuket

Les hébergeurs ne le mettent à disposition parce que ce n'est pas ce que les développeurs demandent.

Je ne comprends pas trop votre double négation.

Citation:

Envoyé par elsuket

Donc c'est un choix qui est fait;

A qui appartient ce choix ?
Est-ce comme je le crois, la direction qui prend la décision de choisir tel ou tel SGBD, la plupart du temps pour des raisons économiques.
Ou est-ce par nécessité en terme de performance et de coût de la maintenance, que la décision se fait dans l'urgence pour basculer vers un autre SGBD ?
Selon moi, la décision de ce choix n'est jamais réfléchie en terme d'évolution du projet et encore moins en terme de maintenabilité.

Citation:

Envoyé par elsuket

MySQL pour ne citer que lui n'est pas proche de la norme SQL.

Est-ce si grave que cela ? Non.
Ce qui est grave, c'est la vocation de MySql d'être abordable pour les débutants !
Pour correctement fonctionner, ce SGBD doit être correctement paramétré lors de son installation.
Mais aussi d'avoir une documentation compréhensible et complète sur ce sujet, ce qui n'est pas le cas, non plus.
Ce qui implique d'avoir une bonne connaissance de son paramétrage, ce qui est loin d'être le cas avec des débutants.
A part cela, je le trouve assez souple à l'usage.

Citation:

Envoyé par elsuket

Ce n'est pas un critère important pour un débutant parce que le SQL est enseigné de façon assez légère : en tout cas c'est ce que je trouve.

Nous sommes d'accord ! On enseigne juste le DML et bien plus rarement le DDL. Alors concernant le paramétrage, et l'administration, c'est une autre histoire.

Citation:

Envoyé par elsuket

Je disais cela parce que je ne connais pas les algorithmes de jointure dans les autres SGBDR.

En lisant ce pdf, je constate qu'il existe d'autres dénomination :
https://www.informatik.hu-berlin.de/...s/09_joins.pdf
Sous DB2 z/os, on parle plutôt de "hybrid (hash) join" et de "block-nested loop".

Citation:

Envoyé par elsuket

Seriez-vous prêt à affirmer qu'une table avec 200 colonnes vaut mieux à tout type d'accès que la même table correctement modélisée, c'est à dire explosée en plusieurs autres tables ?

Pourquoi abordez vous cette question ? Cela n'a jamais été mon propos.
Une base de données non modélisée est une absurdité ! Sur le principe, je suis d'accord avec vous.
Je rappelle que le mieux est l’ennemi du bien. Donc ne jugeons pas sans savoir de quoi il en retourne.

J'ai déjà vu des absurdités, mais quand j'en parlais au client, il me disais surtout de ne pas toucher car cela lui convenait parfaitement.
Je rappelle que le client est roi, et c'est lui qui a le dernier mot.
J'ai même vu un client refuser un projet qui fonctionnait parfaitement, mais dont il n'avait plus la maitrise car cela dépassait sa compétence.

Citation:

Envoyé par elsuket

Justement, la modélisation permet d'allier une organisation des données aux performances.

C'est une erreur de croire cela ! La modélisation à pour but d'organiser les données fonctionnellement.
Mais si fonctionnellement cela a un sens, en terme de performance, il est parfois nécessaire de dégrader la modélisation, pour obtenir un meilleur coût.
Tout le travail de l'administrateur de base de données repose sur :
--> la performance
--> l'intégrité des données
--> la garantie de pouvoir revenir à une situation antérieur en cas de très gros problème.

Citation:

Envoyé par elsuket

Par ailleurs en termes d'intégrité des données, de situations de blocage, ...

Ce n'est pas la modélisation qui répond à ce genre de problème, mais bien le SGBD.
Entre autre, un paramétrage correcte, et surtout une bonne compréhension de la façon de gérer les données au sein de la base de données.
C'est bien à l'administrateur de garantir le fonctionnement du SGBD au sein de ce projet.

Citation:

Envoyé par elsuket

En ce qui concerne un traitement avec un curseur, je vous renvoie à ce petit test que j'ai fait : le constat est sans appel.

J'ai refais le test. J'ai mis mes résultats dans votre blog.

Citation:

Envoyé par elsuket

Prouvez-nous donc le contraire, mais pas avec 4 lignes qui se battent en duel

Le contraire de quoi ?

Je ne suis pas un expert en SQL Server. J'ai repris votre exemple qui ne me convenait pas du tout.
Il y a un manque de rigueur dans la comparaison que vous faites.
J'obtiens un résultat six fois meilleur que le votre. A vous de vous justifier sur cette énorme différence !

Inversement, cela ne change pas la conclusion final. Le curseur est plus long que l'approche ensembliste.

Mais ce n'est pas de cela que je parlais depuis le début.
Je n'ai jamais dis que rajouter une surcouche à ce que sait déjà faire en interne SQL Server, sera plus performant !

Je disais que l'approche ensembliste est plus coûteuse en performance que le parcours séquentiellement d'une table.
Je n'ai jamais parlé de faire un curseur.

S'il vous faut une preuve, voici un exemple de ce que j'avance :
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 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 -- =========== -- 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 'devise' -- ========================== IF OBJECT_ID(N'dbo.devise', N'U') IS NOT NULL DROP TABLE dbo.devise Message 3726, niveau 16, état 1, serveur ORION\SQLEXPRESS, ligne 7 Impossible de supprimer l'objet 'dbo.devise' car il est référencé par une contrainte FOREIGN KEY. -- ======================= -- Création Table 'devise' -- ======================= create table dbo.devise ( devise integer identity(1, 1) not null, nom varchar(10) not null, cours decimal(15,2) not null, constraint pk_devise_id primary key clustered (devise) ) Message 2714, niveau 16, état 6, serveur ORION\SQLEXPRESS, ligne 5 Il existe déjà un objet nommé 'devise' dans la base de données. -- ======================= -- Insertion dans 'devise' -- ======================= insert into devise (nom,cours) values ('dem', 1.95583), ('frf', 6.55957), ('grd', 340.750), ('esp', 166.386) -- ================== -- Vidage de 'devise' -- ================== select * from dbo.devise; devise nom cours ----------- ---------- ----------------- 1 dem 1.96 2 frf 6.56 3 grd 340.75 4 esp 166.39 5 dem 1.96 6 frf 6.56 7 grd 340.75 8 esp 166.39 -- ======================== -- Suppression Table 'test' -- ======================== IF OBJECT_ID(N'dbo.test', N'U') IS NOT NULL DROP TABLE dbo.test -- ===================== -- Création Table 'test' -- ===================== create table test ( id integer identity(1, 1) not null, devise integer not null, montant decimal(15, 2) not null, constraint pk_test_id primary key clustered (id), constraint fk_test_devise foreign key (devise) references dbo.devise (devise) on delete cascade on update cascade ) -- =============================== -- Suppression Procédure 'remplir' -- =============================== IF OBJECT_ID(N'dbo.remplir', N'P') IS NOT NULL DROP PROCEDURE dbo.remplir -- ============================ -- création procédure 'remplir' -- ============================ create procedure dbo.remplir @nbre integer as declare @j smallint begin while (@nbre > 0) begin set @j = 4 while (@j > 0) begin insert into dbo.test (devise,montant) values (@j, 100000.00) set @j = @j - 1 end set @nbre = @nbre - 1 end end -- ============================== -- remplissage de la table 'test' -- ============================== execute remplir 250000 -- ============ -- Requête N° 1 -- ============ declare @datedeb datetime = cast(current_timestamp as datetime) update dbo.test set montant = case devise when 1 then montant * 1.95583 when 2 then montant * 6.55957 when 3 then montant * 340.750 when 4 then montant * 166.386 else 0 end declare @datefin datetime = cast(current_timestamp as datetime) select datediff(millisecond, @datedeb, @datefin) as 'temps elaps' temps elaps ----------- 2230 -- ============ -- Requête N° 2 -- ============ declare @datedeb datetime = cast(current_timestamp as datetime) update dbo.test set montant = t.montant * d.cours from dbo.test as t inner join dbo.devise as d on d.devise = t.devise declare @datefin datetime = cast(current_timestamp as datetime) select datediff(millisecond, @datedeb, @datefin) as 'temps elaps' temps elaps ----------- 9080 Appuyez sur une touche pour continuer...
Les jointures sont elles plus performantes ? Et bien non !

Le traitement fait exactement la même chose dans les deux cas. Je traite 1.000.000 de lignes dans la table test.

Dans le premier cas, le temps elaps est de : 2.230 secondes. C'est le cas du traitement séquentielle de la table.
Dans le second cas, le temps elaps est de : 9.080 secondes. C'est la jointure sur deux tables.

Y-a pas photo ! Et vous répondez quoi ?

Citation:

Envoyé par elsuket

Je suis en train de remodéliser petit bout par petit bout une base de données pour un éditeur de logiciels

A ce niveau de déstructuration de votre base de données, on ne nomme plus cela une re-modélisation.
C'est plus une refonte total avec migration des données dans un nouveau système, plus performant et plus adapté à cette volumétrie.

Citation:

Envoyé par elsuket

D'ailleurs, c'est assez drôle puisque la migration de MySQL à SQL Server avec le même modèle et les mêmes données a été opérée par cet éditeur de logiciels lorsque SQL Server 2005 est sorti.

Comportement absurde mais typique de la méconnaissance des SGBD. Chaque SGBD a sa propre façon de fonctionner.
Ce qui fonctionne chez l'un, peut se voir totalement dégradé en performance chez l'autre.
Des migrations, j'en ai fait beaucoup et je peux vous assurer que gagner du temps en faisant un copier/coller va vite se faire sentir en performance et en maintenance par la suite.
Le mieux est de faire une refonte total de la modélisation et de migrer les donner, en tenant compte des nouvelles contraintes.

Citation:

Envoyé par elsuket

Donc en terme de coût, le plus attractif n'est pas toujours celui que l'on croit.

Comme je l'ai dit précédemment, cela dépend de vos besoins.
Et si dès le départ, on se trompe dans le dimensionnement de son projet, il ne vaut pas s'étonner d'en payer le prix par la suite :
--> migration vers un nouveau SGBD
--> formation en interne des développeurs.
--> recrutement d'un nouveau DBA

Citation:

Envoyé par elsuket

parfois il est difficile de se comprendre mutuellement par écrits interposés.

Je crois surtout que j'ai une approche pragmatique et non à tout pris du respect des normes.
Je ne vais pas dire au client que MySql ne respecte pas les normes et que je ne sais pas comment faire.
Je dis au client que je vais essayer de trouver la meilleur solution de ce que je connais de la pratique de ce SGBD.
Sur un autre SGBD, j'aurai une autre réponse, et cela ne me gène pas.
La norme, c'est juste une béquille qui parfois est très utile pour ne pas trop se casser la tête.

Donc oui, il est difficile de se comprendre car nous n'avons pas la même expérience de notre métier.
Quand un client demande quelque chose, je réponds à son attente, même si cela doit déroger du respect des normes.
Le client, les normes il s'en fout. Tout ce qu'il veut, c'est que cela réponde à son attente.

Citation:

Envoyé par elsuket

Cela ne change pas que le modèle est fondamental;

Je ne suis pas aussi catégorique que vous.
Disons que cela vous facilite la vie et vous empêche de bien réfléchir sur les autres solutions qui pourraient exister, en dehors de la normalisation.
C'est d'ailleurs une des raisons pour laquelle on dégrade un modèle qui au premier abord semble parfait (en théorie).

Mais si je suis d'accord sur dégrader un modèle, je ne préconise pas pour autant de faire n'importe quoi.

Citation:

Envoyé par elsuket

On voit ça ensemble autour de quelques pintes ?

Je suis là pour apprendre, et donc me perfectionner !
Je propose une solution et nous pouvons en débattre sur la pertinence de cette approche.
C'est ce que je recherche, à savoir les échanges !

P.S.: mes résultats dans votre blog ne sont pas très lisible. Dois-je les mettre dans ce sujet ?

@+

05/06/2016, 15h04
SQLpro
Citation:

Envoyé par Artemus24

C'est juste que je ne suis pas d'accord avec vous, sur l'emplacement de cette finalité, ni sur la façon dont vous le gérez. Pourquoi ?
Je ne voie aucun point de reprises et encore moins un traitement ligne par ligne.
De ce fait, le traitement ensembliste est du genre : soit tout est validé, soit tout est rejeté.
Autant le faire à l'extérieur, dans le script après les insert. Cela ne va pas changer le fonctionner du mode transactionnel.

Mon dieu !!!!
Si vous en êtes là c'est que vous n'avez pas compris grand chose aux bases de données relationnelles !!!:mouarf:
En effet on ne choisit pas de faire un bout de transaction en commençant par le point A ou le point B et pas davantage en finissant par le point Y ou le point Z. On fait une transaction parce qu'il y a nécessité d'une règle métier qui l'impose !
Je vais vous donner un exemple tiré des exercices que je donnais à mes étudiants des arts et métiers...
Un voyagiste réalise des "packages" de voyage incluent un vol AR, un séjour à l’hôtel et une location de voiture et pour cela la base dispose de 3 tables pour la gestion des billet d'avions, des séjours hôteliers et des locations de voiture.
Si l'on considère l'enchaînement suivant du processus :
1) trouver un vol
2) trouver le séjour à l’hôtel
3) trouver une location voiture
Vous êtes en train de me dire que l'on peut faire juste 1 et 2 ou 2 et 3 avec votre histoire de point de reprise, ce qui est bien évidemment parfaitement idiot !
Seule la transaction 1) + 2) + 3) est valide.
Si au cours d'une de ces opérations un erreur est survenue, il me parait logique d'annuler IMMÉDIATEMENT la transaction (qui, beaucoup ne le savent pas, est un "état" de la session). La raison pour laquelle il est important d'agir au plus tôt et sans attendre est que, tant que la transaction n'est pas finalisée par un COMMIT ou un ROLLBACK, des verrous suspendent l'état des données ce qui bloque d'autres utilisateurs...
C'est un peu comme si à un passage à niveau un train percutait une voiture et le conducteur décidait finalement de ne s'arrêter qu'à la gare suivante !

Citation:

Si vous voulez améliorer les performances, dans ce cas, on ne fait pas un traitement tout ou rien sur la globalité de ce que l'on cherche à charger.
On fait cela par lots de N lignes. Imaginons un seul instant que le plantage se fasse sur la dernière ligne des 1.000.000 lignes à charger.
SQL Server va devoir défaire (Rollback) tout ce qui a été traité mais non validé (Commit). Si vous trouvez cela performant, ce n'est pas mon cas.
Inversement, si vous avez des lots de 10.000 lignes, vous aurez 990.000 lignes qui auront été validés (Commit), mais les 10.000 dernières seront rejetés (Rollback).
Avec un point de reprise, on ne traite à nouveau que le dernier lot de 10.000 lignes.

Encore une fois on ne choisit pas de faire une transaction ! On la fait par nécessité fonctionnelle ou alors on ne la fait pas !
Imaginez que ce traitement de 100 000 lignes soit les lignes de débit / crédit des cartes bleu à mettre sur le compte en fin de mois. Imaginez maintenant qu'une seule ligne soit en erreur et annule la transaction. Et imaginez que ce soit la seule ligne en crédit à mettre sur le compte.. Que va t-il se passer ?
Votre exemple est bien trop théorique... Revenez dans la réalité fonctionnelle !

Citation:

[...]

C'est une approche minimaliste de la programmation.

Il existe quatre cas :
--> la boucle "pour i de 1 à n par-pas-de 1 faire ... fin-faire".
--> la boucle "répéter ... jusqu'à (condition)".
--> la boucle "tant que (condition) faire ... fin_faire".
--> la boucle "itérer ... sortir si (condition) ... fin-itérer".
Elles ont chacune leur utilité.

La boucle WHILE avec BREAK et CONTINUE permet de faire ces 4 cas de figure... les 3 autres sont donc parfaitement inutiles...
Citation:
[...]
Au sujet du IF

Ce qui est source d'erreur ! Par exemple :

Code:

1 2 3 if (@test = 1) if (@val = 'oui) set @res = 'ok' else set @res = 'ko'
Déjà vous mettez des parenthèses inutiles... Avez vous acheté un stocke en solde et un tel besoin de les écouler ???
Ensuite avec une bonne indentation les choses sont simples :
Code:

1 2 3 4 5 if @test = 1 if @val = 'oui' set @res = 'ok' else set @res = 'ko' else set @res = 'sais pas !'
A +
05/06/2016, 18h35
elsuket

Bonjour Artemus,

Citation:

J'ai refais le test. J'ai mis mes résultats dans votre blog.

Non, vous avez fait un test différent qui met à jour une table de 10 lignes ... en boucle.

Citation:

Pour correctement fonctionner, ce SGBD doit être correctement paramétré lors de son installation.

Effectivement.

Citation:

Est-ce si grave que cela ? Non.

C'est votre avis.

Citation:

Envoyé par elsuket

Seriez-vous prêt à affirmer qu'une table avec 200 colonnes vaut mieux à tout type d'accès que la même table correctement modélisée, c'est à dire explosée en plusieurs autres tables ?

Citation:

Pourquoi abordez vous cette question ? Cela n'a jamais été mon propos.

Citation:

Je disais plus simplement que la jointure est plus coûteuse en terme de performance que de faire un simple accès sans jointure. [...]

...

Citation:

Envoyé par esuket

Justement, la modélisation permet d'allier une organisation des données aux performances.

Citation:

C'est une erreur de croire cela ! La modélisation à pour but d'organiser les données fonctionnellement.
Mais si fonctionnellement cela a un sens, en terme de performance, il est parfois nécessaire de dégrader la modélisation, pour obtenir un meilleur coût.
Tout le travail de l'administrateur de base de données repose sur :
--> la performance
--> l'intégrité des données
--> la garantie de pouvoir revenir à une situation antérieur en cas de très gros problème.

On ne peut dénormaliser qu'une fois que l'on a dénormalisé, et c'est un cas particulier.
Dans la liste des tâches de l'administrateur de base de données, vous omettez (mais l'avez-vous fait exprès ?) la modélisation ...

Citation:

Envoyé par elsuket

Par ailleurs en termes d'intégrité des données, de situations de blocage, ...
Ce n'est pas la modélisation qui répond à ce genre de problème, mais bien le SGBD.
Entre autre, un paramétrage correcte, et surtout une bonne compréhension de la façon de gérer les données au sein de la base de données.
C'est bien à l'administrateur de garantir le fonctionnement du SGBD au sein de ce projet.

Donc vous affirmez qu'on peut tout à faire mettre des tables monolithiques sur tout SGBDR et que ça marchera impeccablement, voire mieux que si l'on avait normalisé la même table.
Les SGBDR sont construit sur l'hypothèse de bases de données relationnelles SQL proprement modélisées.
Le SQL descend de l'algèbre relationnelle, donc un modèle mathématique est sous-jacent à l'optimisation ...

Citation:

J'ai même vu un client refuser un projet qui fonctionnait parfaitement, mais dont il n'avait plus la maitrise car cela dépassait sa compétence.

C'est son choix.

Citation:

Ou est-ce par nécessité en terme de performance et de coût de la maintenance, que la décision se fait dans l'urgence pour basculer vers un autre SGBD ?
Selon moi, la décision de ce choix n'est jamais réfléchie en terme d'évolution du projet et encore moins en terme de maintenabilité.

C'est effectivement selon vous ... MySQL est moins cher à l'achat que SQL Server, non ?

Citation:

J'ai repris votre exemple qui ne me convenait pas du tout.
Il y a un manque de rigueur dans la comparaison que vous faites.
J'obtiens un résultat six fois meilleur que le votre. A vous de vous justifier sur cette énorme différence !

Non c'est vous qui ne savez pas lire, et qui par ailleurs mettez à jour en boucle une table qui contient 10 lignes !!!
Mon test met à jour une table dans laquelle il y a 10, puis 100, puis 1000 lignes, et ainsi de suite suivant les puissances de 10.
Entre chaque ajout de lignes, on mesure le temps de mise à jour de toutes les lignes de la table. Donc pour le manque de rigueur, il faudra repasser.

Citation:

Je disais que l'approche ensembliste est plus coûteuse en performance que le parcours séquentiellement d'une table.
Je n'ai jamais parlé de faire un curseur.

C'est pourtant vous qui avez spécifié un curseur dans un trigger.

Citation:

Y-a pas photo ! Et vous répondez quoi ?

Ce résultat vous surprend-il vraiment, Artemus24 ?

Citation:

Je crois surtout que j'ai une approche pragmatique et non à tout pris du respect des normes.

Je n'ai jamais dit qu'il faut normaliser à tout prix.

Vous avez apparement le syndrôme de la mouche contre la vitre ...

Citation:

A ce niveau de déstructuration de votre base de données, on ne nomme plus cela une re-modélisation.
C'est plus une refonte total avec migration des données dans un nouveau système, plus performant et plus adapté à cette volumétrie.

Nous préférons plutôt changer des petits morceaux, histoire que tout n'explose pas, et qu'on ne bloque pas l'implémentation d'autres fonctionnalités.
Vous savez comme moi qu'une migration de données est une tâche très complexe.

Citation:

Comportement absurde mais typique de la méconnaissance des SGBD. Chaque SGBD a sa propre façon de fonctionner.
Ce qui fonctionne chez l'un, peut se voir totalement dégradé en performance chez l'autre.

On y arrive enfin ... Avez-vous regardé les tests TPC ?

Citation:

Des migrations, j'en ai fait beaucoup et je peux vous assurer que gagner du temps en faisant un copier/coller va vite se faire sentir en performance et en maintenance par la suite.

Effectivement, copier 10 lignes qui se battent en tournoi, c'est faisable. 10 To, peut-être pas.

Citation:

Je ne suis pas aussi catégorique que vous.
Disons que cela vous facilite la vie et vous empêche de bien réfléchir sur les autres solutions qui pourraient exister, en dehors de la normalisation.
C'est d'ailleurs une des raisons pour laquelle on dégrade un modèle qui au premier abord semble parfait (en théorie).

Cela oblige justement à penser à ce à quoi la base de données doit répondre. Comme je l'ai déjà dit, pour pouvoir dénormaliser, il faut d'abord avoir normalisé.
Si le SGBDR n'est pas adapté à la solution, rien n'empêche de passer à une autre qui répondra mieux au besoin. Ce choix ne devrait-il pas être fait avant de modéliser ?

Citation:

Je suis là pour apprendre, et donc me perfectionner !
Je propose une solution et nous pouvons en débattre sur la pertinence de cette approche.
C'est ce que je recherche, à savoir les échanges !

ça tombe bien, moi aussi.

Citation:

P.S.: mes résultats dans votre blog ne sont pas très lisible. Dois-je les mettre dans ce sujet ?

Non, c'est bon. Je vous ai répondu hier.

@++ ;)
06/06/2016, 15h24
Artemus24

Salut à SQLPRO.

On ne se comprend pas !!!

Citation:

Envoyé par SQLPRO

Vous êtes en train de me dire que l'on peut faire juste 1 et 2 ou 2 et 3 avec votre histoire de point de reprise, ce qui est bien évidemment parfaitement idiot !

C'est vous qui êtes idiot. Si vous ne comprenez pas ce que je dis, et bien dites le, au lieu de faire des hypothèses sur mon compte qui sont fausses.
J'ai surtout l'impression que vous ignorez ce que représente un point de reprise sur une validation de masse.

Citation:

Envoyé par SQLPRO

Seule la transaction 1) + 2) + 3) est valide.

Oui, c'est la bonne façon de faire et c'est ce que je nomme un grappe de données.
Afin de ne pas se retrouver avec un problème d'intégrité, on valide les trois cas en même temps ou on rejette les trois cas en même temps.
C'est une validation tout ou rien. Est-ce plus clair ?

Citation:

Envoyé par SQLPRO

Si au cours d'une de ces opérations un erreur est survenue, il me parait logique d'annuler IMMÉDIATEMENT la transaction

Je me demande encore un fois si vous comprenez bien ce que je dis ??? Je vais vous le répéter :

Afin de bien structurer votre script et votre déclencheur, on met le "commit" ou le "rollback" au même niveau que le "begin transaction.
C'est de cela dont je vous parle !!!

Comme vous le faites dans un déclencheur, ils ne sont pas au même niveau et c'est une source d'erreur dans la compréhension de votre façon de gérer la transaction.
Et en plus, on ne fait qu'un seul "commit" ou un seul "rollback" pour une transaction donnée.
Parce que la bonne façon de faire, est de multiplier dans votre traitement ces validations ou ces rejets. Bonjour la maintenance !

Citation:

Envoyé par SQLPRO

(qui, beaucoup ne le savent pas, est un "état" de la session).

Pour appliquer un "commit" ou le "rollback", les lignes doivent être journalisés au préalable.
Le mécanisme de la journalisation est le processus normal de la gestion des transactions.
Qu'est-ce que la session vient faire la dedans ?

Citation:

Envoyé par SQLPRO

La raison pour laquelle il est important d'agir au plus tôt et sans attendre est que, tant que la transaction n'est pas finalisée par un COMMIT ou un ROLLBACK, des verrous suspendent l'état des données ce qui bloque d'autres utilisateurs...

Comme si je ne le savais pas.

Mais vous oubliez une chose, c'est que l'on ne fait pas que des interventions ponctuelles sur une base de données, dans un contexte multi-utilisateurs.
Selon vous, on ne fait jamais de batch la nuit, avec des traitements de masse, dans un contexte mono-utilisateur ?
Genre, mettre à jour à partir d'un fichier mouvement bancaire des opérations de la journées, tous les comptes des clients qui sont impactés.
Comme c'est un traitement de masse, on utilise un point de reprise, afin de ne pas tout retraiter si une erreur vient à se produire disons sur la dernière opération.
C'est pourquoi, on découpe ce traitement par lot plus petit, afin de diminuer la volumétrie lors d'un "commit" ou le "rollback".
J'ai surtout l'impression que vous ne savez pas de quoi je parle !

Vous vous focalisez sur l'annulation IMMÉDIATE de la transaction, comme si cela résolvait vos problèmes de performance.
Dans le traitement de masse, un point de reprise est plus que nécessaire, sinon en cas de plantage, vous êtes obligés de tout recommencer.
Avez-vous compris ou pas ?

Citation:

Envoyé par SQLPRO

Encore une fois on ne choisit pas de faire une transaction !

A bon, parce que d'habitude, cela se fait tout seul ???
Si l'on fait une transaction, c'est pour avoir la possibilité soit de valider, ou soit de rejeter les lignes qui viennent d'être traités.
Sinon, en cas d'erreur, comment faites vous pour rejeter vos lignes, si vous ne faites pas une transaction ?
Donc oui, on choisit de faire une transaction, sinon je ne comprends pas votre façon de gérer vos données.

Citation:

Envoyé par SQLPRO

On la fait par nécessité fonctionnelle ou alors on ne la fait pas !

Que vient faire la fonctionnelle ici ???

Cela ne vous arrive jamais d'avoir à traiter un fichier qui a été verolé durant un transfert ?
Le problème de l'intégrité des données concerne la cohérence de votre base de données
Et c'est un aspect technique et non fonctionnelle.

Je crois surtout qu'il y a un problème de communication.

@+
06/06/2016, 16h35
Artemus24

Salut elsuket.

Citation:

Envoyé par elsuket

On ne peut dénormaliser qu'une fois que l'on a dénormalisé, et c'est un cas particulier.

Il y a un couac dans votre phrase. Au préalable, la base doit être normalisée avant de pouvoir par la suite la dénormaliser.

Citation:

Envoyé par elsuket

Dans la liste des tâches de l'administrateur de base de données, vous omettez (mais l'avez-vous fait exprès ?) la modélisation ...

Je ne l'ai pas fait exprès, mais là où je travaillais (sous IBM gros système et sur des grand comptes), la modélisation n'est pas de ressort du DBA mais d'une équipe d'analyste ayant la connaissance fonctionnelle de la banque ou de l'assurance.
Le DBA s'occupe du passage logique au physique ! Donc ce n'est pas lui qui prend la décision fonctionnelle de l'utilité de telle entités ou de telle relation dans le modèle logique.

Citation:

Envoyé par elsuket

Donc vous affirmez qu'on peut tout à faire mettre des tables monolithiques sur tout SGBDR et que ça marchera impeccablement, voire mieux que si l'on avait normalisé la même table.

Ou ai-je dis cela ? Et qu'est-ce que vous essayez de me faire dire ???

La modélisation sert à découper fonctionnellement votre base de données.
Cela ne résoud en rien les problèmes de performances, qui sont des problèmes techniques !

Donc d'après vous, plus aucune intervention pour résoudre des problèmes performance n'est nécessaire quand vous avez votre modèle logique des données ?
Un index ne vous sert à rien, et les jointures encore moins. Est-ce bien cela ?
On peut même, selon vous, se demander à quoi sert un DBA.

Citation:

Envoyé par elsuket

Le SQL descend de l'algèbre relationnelle, ...

Oui.

Citation:

Envoyé par elsuket

... donc un modèle mathématique ...

oui.

Citation:

Envoyé par elsuket

est sous-jacent à l'optimisation ...

Non, car l'optimisation est purement technique !

Si votre modèle logique découpe fonctionnellement vos données, cela n'indique pas comment vous allez faire pour y accéder.

Citation:

Envoyé par elsuket

MySQL est moins cher à l'achat que SQL Server, non ?

La version SQL Server Express 2014 est gratuite, alors aucun des deux est moins cher.

Citation:

Envoyé par elsuket

Mon test met à jour une table dans laquelle il y a 10, puis 100, puis 1000 lignes, et ainsi de suite suivant les puissances de 10.

Je ne le voie nulle part dans vos explications !

Je vais refaire le test, en tenant compte de ces insertions. Mais dans ce cas là, vous comparez quoi avec quoi ?

Citation:

Envoyé par elsuket

Donc pour le manque de rigueur, il faudra repasser.

On ne met pas des bouts de codes, mais un script intégral où aucune ambiguïté peut faire varier les résultats du test.

Citation:

Envoyé par elsuket

Nous préférons plutôt changer des petits morceaux, histoire que tout n'explose pas, et qu'on ne bloque pas l'implémentation d'autres fonctionnalités.

Je ne comprends pas bien, mais vous faites vos modifications dans l'existant, c'est-à-dire ce qui est en production ?????

Citation:

Envoyé par elsuket

Vous savez comme moi qu'une migration de données est une tâche très complexe.

Pas si complexe que cela. De plus, le basculement doit se faire en une seule fois, durant un week-end ou l'on bloque toute intervention sur l'ancien système.
Mais oui, cela prend du temps pour penser à tout ce qui doit être fait.

Citation:

Envoyé par elsuket

Ce choix ne devrait-il pas être fait avant de modéliser ?

Il n'existe pas une seul solution pour organiser vos données dans un modèle logique.
Si la solution que vous proposez est adaptée pour résoudre votre problème fonctionnelle, il se peut qu'elle ne soit pas du tout adapté à des questions de performances.
Car dans ces deux approches, vous êtes confronté à deux façons de raisonner, soit fonctionnellement, soit techniquement.
Or la modélisation ne répond qu'à l'aspect fonctionnelle !

Dans l'approche Merise, il y a aussi le modèle conceptuel des traitements qui est très souvent délaissés.
On ne s'intéresse à cette approche que si la base de données est créé.

Dans le meilleur des mondes, si tout devait se faire au moment de la conception, le DBA n'aurait plus rien à faire.

@+
06/06/2016, 17h22
escartefigue

Citation:

Envoyé par SQLpro

Mais même en mainframe IBM vends de moins en moins, et la plupart des gens préfèrent passer à Linux, même en conservant la solution chez IBM.
Beaucoup de grosses boîte migrent vers Linux ou dans le cloud en ce moment... Ce que IBM n'ravait pas prévu !
Mon voisin en Provence est un des gros vendeurs IBM de mainframe EMEA pour de l'hébergement data et à du se mettre au cloud et à Linux alors qu'il est à 2 ans de la retraite...

A +

C'est exact, enfin certaines d'entre elles en tout cas, mais il ne faut pas négliger l'inertie du marché Mainframe qui est très conséquente, je ne pense pas que cette archi disparaisse dans un horizon proche.
Mes condoléances à votre collègue, la reconversion a du être douloureuse :D

Fuseau horaire GMT +2. Il est actuellement 12h16.