PB de ROLLBACK dans un trigger, pb niveau de transaction

**cmako** · 10/02/2009, 14h09

Bonjour,

J'ai un pb avec un tout petit trigger.

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
 
create table A (
   C1             int                  not null,
   C2             int                  not null,
   C3             varchar(20)          null,
   constraint PK_A primary key  (C1, C2)
)
GO
 
CREATE TRIGGER TR_a
ON A
FOR INSERT, UPDATE, DELETE
AS
	PRINT '@@TRANCOUNT = ' + CONVERT(VARCHAR, @@TRANCOUNT)
	if exists(select * from deleted)
	BEGIN
		raiserror('no delete',16,1)
		ROLLBACK TRAN
	END
 
	PRINT '@@TRANCOUNT = ' + CONVERT(VARCHAR, @@TRANCOUNT)
	print 'TABLE A'
	SELECT * from A
 
	print 'inserted'
	select * from Inserted
 
	print 'deleted'
	select * from Deleted
GO
 
 
INSERT INTO A
VALUES(1,4,'123')
 
PRINT '@@TRANCOUNT = ' + CONVERT(VARCHAR, @@TRANCOUNT)
 
BEGIN TRAN TRA	-- début de transaction
 
PRINT '@@TRANCOUNT = ' + CONVERT(VARCHAR, @@TRANCOUNT)
DELETE FROM A
PRINT '@@TRANCOUNT = ' + CONVERT(VARCHAR, @@TRANCOUNT)
 
COMMIT TRAN TRA	-- problème

Mon trigger veut empêcher les delete sur ma table.
Le pb est que le ROLLBACK pose un problème pour ma transaction TRA qui essaie d'effacer ma table.

L'instruction de COMMIT TRAN TRA provoque une erreur, car la transaction est déjà rollbackée par le trigger.

Comment est ce que je peux faire:
- soit executer le trigger AVANT l'instruction?
- soit faire un ROLLBACK dans le trigger mais sans impacter sur la transaction TRA?

**SQLpro** · 10/02/2009, 14h33

Vous êtes contradictoire :

Mon trigger veut empêcher les delete sur ma table.

ma transaction TRA qui essaie d'effacer ma table.

Que voulez vous faire : empecher le delete ou le faire ???

De plus si vous voulez faire des transaction imbriquées, il faut mettre en place un stratégie asymétrique. Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/s...ns-imbriquees/

A +

**cmako** · 10/02/2009, 14h35

Envoyé par SQLpro

Vous êtes contradictoire :

Que voulez vous faire : empecher le delete ou le faire ???

De plus si vous voulez faire des transaction imbriquées, il faut mettre en place un stratégie asymétrique. Lisez l'article que j'ai écrit à ce sujet :
http://sqlpro.developpez.com/cours/s...ns-imbriquees/

A +

Je veux le faire de façon conditionnelle, par le trigger. J'ai pas mis la condition, juste pour simplifier le code.

j'ai lu l'article, je ne comprends pas à quoi sert

Code :

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

1
2
IF @@ROWCOUNT = 1
   ROLLBACK TRANSACTION

d'où viendra @@ROWCOUNT = 1?

**elsuket** · 10/02/2009, 14h47

Bonjour,

Code :

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

1
2
3
4
5
IF EXISTS(SELECT * FROM deleted)
	BEGIN
		raiserror('no delete',16,1)
		ROLLBACK TRAN
	END

Vous ne pouvez pas défaire la transaction ou une partie de celle-ci puis la valider ensuite.
Le mécanisme des transactions fonctionne suivant le tout ou rien :
- toute la transaction est réussie, donc elle est validée
- une partie de la transaction échoue, donc toute la transaction est défaite.

Cela permet de conserver dans un état consistant et cohérent une base de données.

- soit executer le trigger AVANT l'instruction?

Il est impossible sous SQL Server d'exécuter un trigger avant la transaction, en revanche vous pouvez spécifier un trigger INSTEAD OF

d'où viendra @@ROWCOUNT = 1?

Du nombre de lignes affectées dans la table par votre instruction DELETE

@++

**cmako** · 10/02/2009, 15h02

Envoyé par elsuket

Bonjour,

Vous ne pouvez pas défaire la transaction ou une partie de celle-ci puis la valider ensuite.
Le mécanisme des transactions fonctionne suivant le tout ou rien :
- toute la transaction est réussie, donc elle est validée
- une partie de la transaction échoue, donc toute la transaction est défaite.

Cela permet de conserver dans un état consistant et cohérent une base de données.

Oui, je sais. J'ai pris ce code, car j'au vu beaucoup d'exemples de triggers ou on fait
RAISEROR(..) puis
ROLLBACK TRAN
Le pb c'est que ça annule toute la transaction et non l'instruction qui a fait déclencher le trigger.

Il est impossible sous SQL Server d'exécuter un trigger avant la transaction, en revanche vous pouvez spécifier un trigger INSTEAD OF

Ca c'est nul par contre, je sais que sous Oracle, ça existe.
Je crois que je vais devoir passer par INSTED OF et ça m'énerve un peu.

Du nombre de lignes affectées dans la table par votre instruction DELETE

@++

En faite ça viens de l'article de SQLPro

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
-- partie à rajouter à TOUTES les procédures (finalisation) :
 
-- succès
COMMIT TRANSACTION
RETURN (0)
 
-- échec
LBL_ERROR:
IF @@TRANCOUNT > 1
   COMMIT TRANSACTION
IF @@ROWCOUNT = 1
   ROLLBACK TRANSACTION
RETURN (-1)

Et c'est là que je ne comprends pas pourquoi @@ROWCOUNT = 1?

**elsuket** · 10/02/2009, 15h26

Ca c'est nul par contre, je sais que sous Oracle, ça existe.

Effectivement ça existe sous Oracle, mais plus vous écrirez de triggers sous SQL Server, et plus vous vous rendez compte que ça ne sert pas à grand chose ...

Le pb c'est que ça annule toute la transaction et non l'instruction qui a fait déclencher le trigger.

Sous SQL Server, toute instruction est une transaction.

Je crois que je vais devoir passer par INSTED OF et ça m'énerve un peu.

Pourquoi ?

@++

**cmako** · 10/02/2009, 15h46

Envoyé par elsuket

Effectivement ça existe sous Oracle, mais plus vous écrirez de triggers sous SQL Server, et plus vous vous rendez compte que ça ne sert pas à grand chose ...

Pourquoi, ca ne sert pas à grand chose. Moi je veux les incohérences, par injection SQL à la mano.
Beaucoup de monde utilise ici la BDD et beaucoup de modifs sont fait manuellement en SQL, ce qui introduit plein d'incohérences.

Sous SQL Server, toute instruction est une transaction.

Oui, mais comment s'appelle la transaction créée par l'instruction qui a fait déclencher le trigger?
BEGIN TRAN nom le nom c'est quoi???

Pourquoi ?

Parce que je dois retaper la requête insert avec tous les champs. Ce qui implique si on rajoute un champ à la table il faudra également modifier le trigger.
Ca fait un double travail.
En plus, au niveau perf, ca dois etre surement plus lent.

**elsuket** · 10/02/2009, 16h18

Beaucoup de monde utilise ici la BDD et beaucoup de modifs sont fait manuellement en SQL, ce qui introduit plein d'incohérences.

C'est ce qui se fait souvent en entreprise parce que SQL Server propose une interface graphique pour faire cela, mais son utilité est très discutable !
A vous de mettre les contraintes

BEGIN TRAN nom le nom c'est quoi???

Une transaction explicite.
Une instruction est une transaction implicite.

Parce que je dois retaper la requête insert avec tous les champs. Ce qui implique si on rajoute un champ à la table il faudra également modifier le trigger.
Ca fait un double travail.

C'est de la maintenance de base de données, c'est le travail de tout développeur de bases de données

En plus, au niveau perf, ca dois être surement plus lent.

Je ne vois pas pourquoi.
Dans tous les cas un trigger est gourmand en ressources.

@++

**Antoun** · 10/02/2009, 16h24

Envoyé par cmako

En plus, au niveau perf, ca dois etre surement plus lent.

En fait, les triggers de SQL Server sont ensemblistes, ils sont donc plus rapides que des triggers FOR EACH ROW.

**SQLpro** · 10/02/2009, 16h41

Le
@@ROWCOUNT
dans l'article est une erreur, c'est
@@TRANCOUNT
qu'il faut mettre.

A +

**zoltix** · 17/02/2009, 19h26

Envoyé par SQLpro

Le
@@ROWCOUNT
dans l'article est une erreur, c'est
@@TRANCOUNT
qu'il faut mettre.

A +

Grand merci car je cherchais la raison depuis 1h .........je ne comprenais pas. Je sais que cette article est la depuis longtemps, mais ne faudrait il pas le changer ?

Pour ne pas chercher a comprendre une erreur...comme moi.

Si je comprends bien ce serait un truc du style

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 
COMMIT TRANSACTION	
print @@TRANCOUNT 
RETURN (0)
 
 
LBL_ERROR:
		print   @@TRANCOUNT
		IF @@TRANCOUNT > 1
			COMMIT TRANSACTION
		Else IF @@TRANCOUNT = 1
			ROLLBACK TRANSACTION
RETURN (-1)

A+

**cmako** · 18/02/2009, 09h05

Envoyé par zoltix

Grand merci car je cherchais la raison depuis 1h .........je ne comprenais pas. Je sais que cette article est la depuis longtemps, mais ne faudrait il pas le changer ?

Pour ne pas chercher a comprendre une erreur...comme moi.

Si je comprends bien ce serait un truc du style

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 
COMMIT TRANSACTION	
print @@TRANCOUNT 
RETURN (0)
 
 
LBL_ERROR:
		print   @@TRANCOUNT
		IF @@TRANCOUNT > 1
			COMMIT TRANSACTION
		Else IF @@TRANCOUNT = 1
			ROLLBACK TRANSACTION
RETURN (-1)

A+

Ca non plus c'est pas bon, car dès que tu fais un COMMIT TRAN ou ROLLBACK TRAN sans nommer ta transaction le @@trancount retombe à 0.

PB de ROLLBACK dans un trigger, pb niveau de transaction

Développement SQL Server

Discussions similaires

Partager

Partager