Discussion :

[miyoku]

Bonjour, je souhaite actuellement réalisé un trigger sur une table vérifiant lors d'un update ou d'un insert que la colonne update ou inserer correspond bien, j'entend par la qu'elle dépend d'une autre colonne. Je m'explique, voici ma table

Code :

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

1
2
3
4
5
6
7
8
9
 
CREATE TABLE ROUTE
    (ROUTE_ID INT PRIMARY KEY IDENTITY,
    TAG VARCHAR(6),
    STATUS VARCHAR(20), -- prend OK ou KO si la route fonctionne
    COMMENT VARCHAR(100),
    TYPE VARCHAR(10),     --détermine le type 'internet' ou GSM de external_id 
    EXTERNAL_ID VARCHAR(50), --clé étrangère relié à deux autre table
);

Le problème est justement que lorsque l'on update ou insère quelque chose dans external_id il faut vérifier en fonction du statut (si il est ok on vérifie sinon on s'en fout) et du type pour bien vérifier que la personne a mis la bonne chose

De mon côté (je vais pas vous demander de le faire^^) j'avais pensé à ça (le trigger est déja crée):

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
 
ALTER TRIGGER [dbo].[sms_route_src].[T_SMS_ROUTE_SRC] 
   ON  [dbo].[sms_route_src]  
   after INSERT,UPDATE
AS
 
BEGIN
 
	if not exists(select external_id from ROUTE where route_id=(select ROUTE_ID from inserted))
	BEGIN
 
		DECLARE CurIns CURSOR
		FOR
			SELECT status, type, external_id
			FROM INSERTED
			IF @@error <> 0 GOTO LBL_ERROR
			DECLARE @status varchar(50), @type VARCHAR(20), @external_id varchar (50)
 
		OPEN CurIns
		IF @@error <> 0 GOTO LBL_ERROR
 
		FETCH CurIns INTO @status, @type,@external_id
		WHILE @@Fetch_Status = 0
		BEGIN
			if @status='' or @type=''
			BEGIN
			   FETCH CurIns INTO @status, @type,@external_id
			   CONTINUE
			END
 
			if @status='ok' and @type like '%net'
			BEGIN
				if @external_id like '+%'
				begin
					rollback transaction
				end
				IF @@error <> 0 GOTO LBL_ERROR
				FETCH CurIns INTO @status, @type,@external_id
			END
			else if @status='OK' and @type ='GSM'
			BEGIN
				if @external_id not like '+%'
				begin
					rollback transaction
				end
				IF @@error <> 0 GOTO LBL_ERROR
				FETCH CurIns INTO @status, @type,@external_id
			END
		END
		CLOSE CurIns
		DEALLOCATE CurIns
 
 
	LBL_ERROR:
	PRINT 'AN ERROR OCCURED WHILE INSERTING OR UPDATING ROUTE'
	END
 
END
 
GO

Je précise que je teste '%+' pour les GSM car lorsque le type est GSM l'external_id est censé être quelque chose du genre +335466878, bref un numéro tandis qu'un type internet est une simple chaine, pour ça que je teste le fait que ça ne soit pas un numéro. Toutefois ce code ne marche absolument pas et je ne vois pas du tout pourquoi, il me semble logique. Si vous avez des réponses n'hésitez pas

Je suis sous microsoft sql server management studio 2008 avec windows XP

[Kropernic]

Ouch...

Utilises les balises codes. C'est illisible actuellement.
Quant à la définition de ta table, poste plutôt le code DDL. Ce sera plus clair pour tout le monde.

[aieeeuuuuu]

Bonjour,

Je n'ai pas tout lu en détail, et je ne comprend pas l'interet de votre premier IF NOT EXISTS, mais j'ai surtout l'impression que vous pouvez remplacer de trigger par une contrainte de domaine (CHECK...)

ce sera beaucoup plus performant...

Au pire, vous pouvez au moins vous passer de votre curseur je pense...

[miyoku]

C'est vrai que les contraintes pourrait être plus pratique. Mais est-ce que ça ne va pas me limiter
par exemple en mettant

Code :

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

1
2
3
4
5
 
ALTER TABLE ROUTE
ADD CONSTRAINT chk_GSM CHECK (status='ok' AND type='GSM' AND external_id like '+%')
ALTER TABLE ROUTE
ADD CONSTRAINT chk_internet CHECK (status='ok' AND type like '%net' and external_id not like '+%')

est-ce que je peux encore update avec des valeur status='ko' (ce qui est possible) et qui peux tout chambouler par la suite, on peut avoir
status='ko', type='GSM' et une external_id='une chaine', justement parce que la route ne fonctionne pas
donc est-ce que ça ne me limite pas trop?

[aieeeuuuuu]

Non, c'est juste qu'il faut bien penser la contrainte.

Telle que vous avez proposé, en effet, vous ne pourrez même plus rien insérer, car aucune donnée ne pourra vérifier les deux contraintes simultanément.

Il faut donc créer une seule contrainte qui prévoit tous les cas. Sans avoir relu en détail votre besoin, quelque chose du genre :

Code :

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

1
2
3
4
5
6
7
8
 
ALTER TABLE ROUTE
ADD CONSTRAINT chk_external_id (
    STATUS <> 'ok' --pour ne vérifier la contrainte que si le statut est OK
    OR STATUS IS NULL -- <-- si la colonne est nullable, sinon pas nécéssaire
    OR (type='GSM' AND external_id LIKE '+%') 
    OR (type LIKE '%net' AND external_id NOT LIKE '+%')
)

Cette contrainte vérifie que :
"Si le statut est OK, alors le type doit etre GSM et la route doit commencer par le caractére '+' OU BIEN le type doit finir par 'net' et la route ne doit pas commencer par '+'"

[miyoku]

merci de m'avoir répondu, effectivement, une contrainte marche bien.