Contrainte d'intégrité et trigger

[new_wave]

Bonjour,
Je viens delire ces propos à propos des triggers de base de données

A la différence d'une contrainte d'intégrité, un déclencheur de base de données défini pour appliquer une règle d'intégrité ne vérifie pas les données
chargées dans une table. Il est donc conseillé d'utiliser des déclencheurs de base de données uniquement lorsqu'une règle d'intégrité ne peut pas
être appliquée par des contraintes d'intégrité.

J'avoue que je ne comprends pas grand chose à cette phrase.
Pouvez vous me dire pourquoi un trigger défini pour appliquer une règle d'intégrité ne vérifie pas les données chargées dans une table.
N'est ce pas là le rôle d'une contrainte d'intégrité?
Pouvez-vous me donner un exemple, je vous en remercie beaucoup, je pense que cela me parlera plus.

Mercie beaucoup à vous.
Cordialement.
new_wave

[mnitu]

Difficile à dire ce que la personne qui a dit voulait dire et ce que la personne qui a noté a compris de ces affirmations. Probablement qu'il s'agit de ces propos:
How Triggers and Constraints Differ

[Mohamed.Houri]

Utiliser un trigger pour valider une contrainte d'intégrité est une erreur fatale dans un environnement multi-user. Comme les selects ne bloquent pas les update/delete/insert/merge et vice versa (read consistency concept), lorsque vous vérifiez une contrainte d'intégrité dans un environnement concurrent les modifications de l'un peuvent ne pas être visibles par l'autre et la contrainte sera mal validée. Si vous insistez à utiliser le trigger dans ce contexte vous devriez alors sérialiser l'accès à votre table via un explicit "lock table". Cela rendrait votre application non performante et uni-user!!!

Je vous invite à lire ce lien pour plus de détails:

http://www.oracle.com/technetwork/is...om-101055.html

[Yanika_bzh]

Vrai pour les contraintes d'intégrité relationnelle, mais moins vrai pour les contraintes d'intégrité par condition booléenne (CHECK)

[Pomalaix]

Envoyé par new_wave

...
Je viens delire ces propos à propos des triggers de base de données

Et si veut on les lire nous aussi, de quel site Web ou livre s'agit-il ?

[mnitu]

Envoyé par Mohamed.Houri

Utiliser un trigger pour valider une contrainte d'intégrité est une erreur fatale dans un environnement multi-user. Comme les selects ne bloquent pas les update/delete/insert/merge et vice versa (read consistency concept), lorsque vous vérifiez une contrainte d'intégrité dans un environnement concurrent les modifications de l'un peuvent ne pas être visibles par l'autre et la contrainte sera mal validée. Si vous insistez à utiliser le trigger dans ce contexte vous devriez alors sérialiser l'accès à votre table via un explicit "lock table". Cela rendrait votre application non performante et uni-user!!!

Je vous invite à lire ce lien pour plus de détails:

http://www.oracle.com/technetwork/is...om-101055.html

Vous allez un peu trop vite et l’exemple du Tom Kyte parle de quelque chose d’assez particulière. Lisez aussi: Triggers for Ensuring Referential Integrity

[Mohamed.Houri]

Envoyé par mnitu

Vous allez un peu trop vite et l’exemple du Tom Kyte parle de quelque chose d’assez particulière. Lisez aussi: Triggers for Ensuring Referential Integrity

Marius,

Preferez vous une simple contrainte étrangère

Code :

1
2
3
 
ALTER TABLE emp ADD constraint emp_fk FOREIGN KEY(deptno) REFERENCES dept;
CREATE INDEX emp_fk_ind ON emp(deptno);

à un trigger avec plusieurs lignes de code ou de toute façons nous sommes obligés de sérialiser les modifications

Code :

1
2
3
4
5
 
CURSOR Dummy_cursor (Dn NUMBER) IS
    SELECT Deptno FROM dept
    WHERE Deptno = Dn
    FOR UPDATE OF Deptno;

C'est pour cela que je préfère toujours quand cela est possible d'utiliser les contraintes d'intégrité (check, fk, etc..) au lieu et place des triggers.

http://asktom.oracle.com/pls/asktom/...53473305799476

[Rams7s]

Envoyé par Mohamed.Houri

C'est pour cela que je préfère toujours quand cela est possible d'utiliser les contraintes d'intégrité (check, fk, etc..) au lieu et place des triggers.

De 1 c'est le role du SGBDR d'assurer ce genre de chose avec les contraintes. Sinon on peut prendre un NoSQL.
Et pis oracle utilise les contraintes dans le CBO, donc c'est mieux de les utiliser.

[Waldar]

Envoyé par Rams7s

Et pis oracle utilise les contraintes dans le CBO, donc c'est mieux de les utiliser.

Pas toutes malheureusement, à ma connaissance et sur 11gR1 il n'utilise pas les contraintes CHECK (contrairement au concurrent de chez Microsoft) !

Ou alors avec une astuce que je n'ai pas trouvée !

[pachot]

Bonjour,

Envoyé par mnitu

Vous allez un peu trop vite et l’exemple du Tom Kyte parle de quelque chose d’assez particulière. Lisez aussi: Triggers for Ensuring Referential Integrity

Oui, les triggers présentés dans Triggers for Ensuring Referential Integrity fonctionnent, mais c'est une catastrophe pour un système multi-user.
L'exemple 'Foreign Key Trigger for Child Table' empêche de faire 2 inserts en même temps lorsqu'ils ont le même parent. Les inserts seront sérialisés.

En fait, coder de l'intégrité qui fait référence à autre chose que l'enregistrement en cours obligera la plupart du temps à vérouiller très large.

Cordialement,
Franck.

[pachot]

Bonjour,

Envoyé par Waldar

Pas toutes malheureusement, à ma connaissance et sur 11gR1 il n'utilise pas les contraintes CHECK (contrairement au concurrent de chez Microsoft) !

Ou alors avec une astuce que je n'ai pas trouvée !

Un exemple:

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
 
SQL> CREATE TABLE test ( a number PRIMARY KEY , b number );
TABLE created.
 
SQL> ALTER TABLE test ADD constraint c CHECK ( a = b ) ;
TABLE altered.
 
SQL> INSERT INTO test SELECT rownum,rownum FROM dual connect BY level <= 100;
100 rows created.
 
SQL> SET autotrace ON EXPLAIN;
SQL> SELECT * FROM test WHERE b=1;
	 A	    B
---------- ----------
	 1	    1
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2233651525
 
--------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		   |	 1 |	26 |	 2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST	   |	 1 |	26 |	 2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN	    | SYS_C0014551 |	 1 |	   |	 1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - filter("B"=1)
   2 - access("A"=1)

L'index sur A est utilisé alors que le prédicat est sur B grace à la contrainte check: l'optimiseur a rajouté le prédicat qu'il a déduit.

Cordialement,
Franck.

[Waldar]

Merci pachot pour cette démonstration, j'avais en tête ce genre de contrainte CHECK :

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
CREATE TABLE test2 ( a number );
-- CREATE TABLE réussi.
 
ALTER TABLE test2 ADD constraint c2 CHECK ( a > 0 ) ENABLE VALIDATE;
-- ALTER TABLE test2 réussi.
 
INSERT INTO test2 ( a ) SELECT level FROM dual connect BY level <= 1e8;
-- 100000000 lignes inserées
 
COMMIT;
-- commit réussi.
 
SET autotrace ON EXPLAIN;
-- Enregistrement de la trace automatique activé
 
SELECT * FROM test2 WHERE a = -1;
 
A                      
---------------------- 
 
0 rows selected
 
----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    13 |     0   (0)|          |
|*  1 |  FILTER            |       |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| TEST2 |    17 |   221 |   473  (10)| 00:00:06 |
----------------------------------------------------------------------------
 
Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   1 - filter(NULL IS NOT NULL)
   2 - filter("A"=(-1))

C'est dommage qu'il aille lire la table alors que la contrainte suffit à retourner NULL tant qu'elle est validée.

[Mohamed.Houri]

Envoyé par Waldar

C'est dommage qu'il aille lire la table alors que la contrainte suffit à retourner NULL tant qu'elle est validée.

Waldar,

Non il ne va pas lire du tout la table.

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
 
mhouri > SELECT /*+ gather_plan_statistics */
  2  * FROM test2
  3  WHERE a = -1;
 
no rows selected
 
d102@o102d00d.world> SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT                                                                                                       
----------------------------------------------------------------------------------------
SQL_ID  ct6b4bdh21uu0, child number 0                                                                                   
-------------------------------------                                                                                   
SELECT /*+ gather_plan_statistics */ * FROM test2 WHERE a = -1                                                          
 
Plan hash value: 763754846                                                                                              
 
----------------------------------------------------------------------------                                            
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   |                                            
----------------------------------------------------------------------------                                            
|*  1 |  FILTER            |       |      1 |        |      0 |00:00:00.01 |                                            
|*  2 |   TABLE ACCESS FULL| TEST2 |      0 |      1 |      0 |00:00:00.01 |                                            
----------------------------------------------------------------------------                                            
 
Predicate Information (IDENTIFIED BY operation id):                                                                     
---------------------------------------------------                                                                     
 
   1 - filter(NULL IS NOT NULL)                                                                                         
   2 - filter("A"=(-1))                                                                                                 
 
Note                                                                                                                    
-----                                                                                                                   
   - dynamic sampling used FOR this statement                                                                                                                                                                                                  
 
23 rows selected.

Observez l'information Starts qui correspond à l'opération 2 TABLE ACCESS FULL. Elle est égale à 0 ce qui veut dire que l'opération 2 n'a jamais été executée par le CBO. Ceci est d'ailleurs confirmé par l'information A-Rows = 0 qui veut dire aucune ligne sélectionée. Le filter s'applique sur A-Rows = 0

[mnitu]

Envoyé par Mohamed.Houri

Marius,

Preferez vous une simple contrainte étrangère

...

Ce n’est pas ça que je vous ai dit mais simplement qu’il y des cases qui ne sont pas gérable avec les contraints.

[Waldar]

Envoyé par Mohamed.Houri

Observez l'information Starts qui correspond à l'opération 2 TABLE ACCESS FULL. Elle est égale à 0 ce qui veut dire que l'opération 2 n'a jamais été executée par le CBO. Ceci est d'ailleurs confirmé par l'information A-Rows = 0 qui veut dire aucun ligne sélectionée. Le filter s'applique sur A-Rows = 0

Mohamed : , je ne savais pas utiliser cette information.

[mnitu]

Envoyé par Waldar

Mohamed : , je ne savais pas utiliser cette information.

set autotrace on et regarder les Statistiques I/O donne aussi une bonne information.

[mnitu]

Je me dis parfois qu’il ne sera pas mal si Oracle revisitera ses documentations pour les mettre à jour

How Triggers and Constraints Differ

Both triggers and constraints can constrain data input, but they differ significantly.

A constraint applies to both existing and new data. For example, if a database column has a NOT NULL constraint, then its existing data is NOT NULL and no DML statement can violate the NOT NULL constraint.

A trigger applies only to new data. For example, a trigger can prevent a DML statement from inserting a NULL value into a database column, but the column might contain NULL values that were inserted into the column before the trigger was defined or while the trigger was disabled.

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
 
Connected TO Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 
Connected AS mni
 
SQL> CREATE TABLE t_cons (
  2  a integer
  3  )
  4  /
 
TABLE created
 
SQL> INSERT INTO t_cons VALUES (NULL)
  2  /
 
1 row inserted
 
SQL> commit
  2  /
 
Commit complete
 
SQL> SELECT a FROM t_cons
  2  /
 
                                      A
---------------------------------------
 
SQL> ALTER TABLE t_cons MODIFY ( a integer NOT NULL enable novalidate)
  2  /
 
TABLE altered
 
SQL> INSERT INTO t_cons VALUES (NULL)
  2  /
 
INSERT INTO t_cons VALUES (NULL)
 
ORA-01400: impossible d'insérer NULL dans ("MNI"."T_CONS"."A")
 
SQL> select a from t_cons
  2  /
 
                                      A
---------------------------------------
 
SQL>

[nessing]

Bonsoir , désolé de m' incruster dans la discussion ,
je voudrais savoir si les tables sont distantes ou sur un autre user !!
et si c'est le cas comment le trigger procède-t-il pour effectuer la contrainte d'intégrité ?

[al1_24]

Qu'entends-tu par effectuer la contrainte d'intégrité ?

[nessing]

merci pour les corrections ,
d'après ce lien http://download-uk.oracle.com/docs/c...05itg.htm#1628 le trigger résout le problème de la clé étrangère d'une table dans un autre compte ou base de donnée distante .
j'ai pas trop compris comment résoudre le problème avec le trigger!!