Discussion :

[AIDExpress]

Bonjour,

J'aimerais passer un parametre dans un trigger

Dans ma base j'ai les tables :

USERS avec les champs
us_id int auto-increment pour l'identifiant
us_civ int (pour la Civilitée) pour la civilité via une autre table
us_lastname nvarchar(50) pour le nom de famille
us_firstname nvarchar(50) pour le prénom
us_login nvarchar(50) pour le login
us_password varbinary(128) pour le mot de passe

USERSHISTORICS (pour historiser les action insert/update/delete sur la table USERS)
uh_id int auto-increment pour l'identifiant
us_id int pour connaitre l'utilisateur qui à été traité
uh_cdate datetime pour connaitre la date de traitement
uh_libelle nvarchar(50) pour indiquer le traitement effectué
us_id_change int pour savoir quel utilisateur à fait le traitement


J'ai fait une procedure stockée Insert user comme suit :

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
CREATE PROCEDURE [dbo].[PS_User_Insert]
 
/* Déclaration des variables */
@us_civ int,
@us_lastname nvarchar(50),
@us_firstname nvarchar(50),
@us_login nvarchar(50),
@us_password nvarchar(50),
@us_id_change int,
 
/* Déclaration de la variable de sortie */
@message nvarchar(250) OUTPUT
 
AS
 
/* Déclaration de la variable password */
DECLARE @password varbinary(128)
 
/* instanciation de la variable password */
SET @password = CONVERT(varbinary, @us_password)
 
BEGIN
	/* Si le login  demandé existe déjà dans la base */
	IF EXISTS (SELECT 1 FROM dbo.USERS WHERE us_login=@us_login )
		BEGIN
			/* Si le login  demandé existe déjà dans la base mais qu'il est 'Delete' dans l'historique on peut ajouter le user */
			IF EXISTS (SELECT 1 FROM dbo.USERS INNER JOIN dbo.USERSHISTORICS ON dbo.USERS.us_id = dbo.USERSHISTORICS.us_id WHERE us_login=@us_login AND dbo.USERSHISTORICS.uh_libelle = 'Deleted')
				BEGIN				
					INSERT INTO dbo.USERS(us_civ, us_lastname, us_firstname, us_login, us_password)
					VALUES (@us_civ, @us_lastname, @us_firstname, @us_login, @password)
					SET @message = 'The user was created'
					RETURN 0
				END
			/* Sinon on ne peut ajouter le user */
			ELSE
				BEGIN
					SET @message = 'This login is already in use'
					RETURN 1
				END
		END
	/* Sinon on ajoute l'utilisateur */
	ELSE
		BEGIN
			INSERT INTO dbo.USERS(us_civ, us_lastname, us_firstname, us_login, us_password)
			VALUES (@us_civ, @us_lastname, @us_firstname, @us_login, @password)
			SET @message = 'The user was created'
			RETURN 0			
		END
END

J'ai fait mon trigger comme suis en essayant FOR INSERT ou AFTER INSERT :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TRIGGER [dbo].[TG_User_Insert] ON [dbo].[USERS]
 
FOR INSERT
 
AS
 
/* Déclaration de la variable */
DECLARE @us_id_creator int
 
/* Instanciation de la variable */
SELECT @us_id_change = @us_id_change FROM inserted
 
/* Ajoute une entrée dans l'historique */
INSERT INTO dbo.USERSHISTORICS(us_id, uh_cdate, uh_libelle, us_id_change)
VALUES (@@IDENTITY, GETDATE(), 'Created', @us_id_change)

Mon problème c'est que cette variable ne s'instancie pas dans le trigger et reste null :

Code :

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

1
2
/* Instanciation de la variable */
SELECT @us_id_change = @us_id_change FROM inserted

Effectivement le paramètre passé à la procédure stockée (@us_id_change) n'est pas utilisé pendant l'insert alors comment dire à mon trigger de prendre le paramètre @us_id_change qui vient de la procédure stockée qui à déclenché l'insert et donc le trigger ?

[Invité]

Je ne comprends pas : un déclencheur, par définition se déclenche suivant un évènement.
Alors pourquoi un déclencheur aurait-il besoin qu'on lui fournisse un paramètre ?

Qu'est ce que tu veux faire vraiment, avant d'aller plus loin, parce que 2 ddl de tables, ça ne nous donne aucun détail sur ton besoin et la finalité de la chose...

[AIDExpress]

Bonjour,

Merci de votre réponse rapide.
Comme j'ai fais quelques EDIT pour mieux expliquer je ne sais pas quelle explication vous avez lu.

Merci

[Invité]

Effectivement, si vous modifiez votre message initiale, ça va pas être facile d'être raccord...

Vous êtes sûr de ça ??

Code :

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

SELECT @us_id_change = @us_id_change FROM inserted

Ça serait pas plutôt

Code :

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

SELECT @us_id_change = us_id FROM inserted

Et que fait votre procédure là-dedans ??
Votre trigger ne traite pas les inserts multiples.
On ne sait toujours pas ce que vous cherchez vraiment à faire...

[elsuket]

Bonjour,

Outre le fait que l'affectation de la variable est incorrecte, de toute façon le trigger est faux puisqu'il n'est pas ensembliste.
S'il fonctionnera pour votre procédure parce que vous n'ajoutez qu'une seule ligne à chaque fois, rien ne garantit qu'il fonctionnera sur d'autre transactions où vous insérerez plusieurs utilisateurs.

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
CREATE TRIGGER [dbo].[TG_User_Insert]
	ON [dbo].[USERS] 
FOR INSERT 
AS
BEGIN
	SET NOCOUNT ON
 
	-- Ajoute une entrée dans l'historique
	INSERT INTO dbo.USERSHISTORICS
	(
		us_id
		, uh_cdate
		, uh_libelle
		, us_id_change
	)
	SELECT	us_id
		, GETDATE()
		, 'Created'
		, us_id
	FROM	INSERTED
END

En outre comme il s'agit d'une table historisation, il aurait mieux valu mettre une contrainte de valuation par défaut :

Code :

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

1
2
ALTER TABLE dbo.USERSHISTORICS
ADD CONSTRAINT DF_USERSHISTORICS__uh_cdate DEFAULT (GETDATE()) FOR uh_cdate

Ce qui permet de réaliser l'INSERT comme suit :

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
ALTER TRIGGER [dbo].[TG_User_Insert]
	ON [dbo].[USERS] 
FOR INSERT 
AS
BEGIN
	SET NOCOUNT ON
 
	-- Ajoute une entrée dans l'historique
	INSERT INTO dbo.USERSHISTORICS
	(
		us_id
		, uh_libelle
		, us_id_change
	)
	SELECT	us_id
		, 'Created'
		, us_id
	FROM	INSERTED
END

@++

[AIDExpress]

Ben je pensais pas que quelqu'un serrait aussi réactif donc je pensais avoir le temps de faire mes modifications ^^

Pour ce qui est de votre remarque sur le code vous avez raison.
En fait j'ai essayé (comme SQL Serveur ne m'a pas indiqué d'erreur et a bien enregistré le code tel que) en espérant que sous cette forme il prendrait bien le valeur passé en paramètre dans la procédure et pas le champ de la table (qui n'existe pas d'où mon problème)

Pour ce qui est de vos questions:
- Ce que je voudrais faire c'est avoir une table d'historique dans laquel est référencé toutes les modifications faite sur la table 'USERS' et surtout savoir qui a fait la modification (Insert, Update et Delete. Le Deleted est en fait un Update qui met la valeur du champ 'uh_libelle' de la table 'USERSHISTORICS' sur 'Deleted'. Ceci dans l'idée de ne jamais effacer une entrée dans la table 'USERS' pour ne pas avoir d'erreur dans d'autre table qui utilise us_id.
Sachant que le 'us_login' doit être unique dan ma table USERS, quand j'ajoute une nouvelle entrée je vais vérifier que le login n'existe pas. Si il existe, que dans l'historique il soit bien en 'Deleted' sans quoi je ne peut pas l'ajouter. Mais c'a c'est une autre histoire ^^)

- Mes procédures entrent les données dan la table 'USER' ce qui déclanche mon trigger insert ou update qui vas ajouter des entrées dans la table 'USERSHISTORICS'.

- Effectivement je n'ai pas ajouter un control sur mon trigger en cas d'entrée multiple.

Résolution de mon problème:
Au final, mais je pense que ce n'est pas propre, j'ai ajouté un champ 'last_us_id_modificator' dans ma table 'USER'. De cette facon mon trigger peut récupéré une valeur à ma variable @us_id_modificator et donc ajouter une entré dans ma table 'USERSHISTORICS'.
J'avais aussi pensé, si je ne rajoutais pas un champ supplémentaire à ma table 'USERS', d'appellé une autre procédure qui aurait ajouté les entrées dans la tables 'USERHISTORICS'. Cela m'aurais permis de me plus utiliser de trigger. Mais je ne crois pas que cette solution soient propre.

Bref, j'ai fait quelques tests et pour le moment tout fonctionne comme je le voulais.
Si vous avez compris mon besoin et avez des suggestions pour le faire plus proprement je suis à votre écoute.

Merci

[AIDExpress]

Bonjour elsuket.

Merci pour vos informations.

Je ne rappelais pas des contraintes de validation (ma formation sur SQL date un peut ^^).

Je vais étudier cela pour améliorer ma base.

[AIDExpress]

Petite intérogation qui me vient....

Quand mon application appel une procédure stocké de ma base de données, elle ne peut passer qu'une seul valeur par paramètre de cette procédure.

Alors comment puis-je avoir une transaction multiple ?
(y'a un truc que je confond ou que j'oublie ^^)

[AIDExpress]

Rebonjour elsuket.

J'ai un problème avec le code que vous m'avez indiqué.

L'utilisateur qui fait des insert ou des update n'est pas forcément le même que l'utilisateur qui est ajouté ou modifié.

Donc le code dans le trigger
...
INSERT INTO dbo.USERSHISTORICS(us_id, uh_libelle, us_id_change)
SELECT us_id, 'Created', us_id
FROM INSERTED

n'est pas correcte car mes champs 'us_id' et 'us_id_change' on toujours les même valeurs. ^^

[elsuket]

Quand mon application appel une procédure stocké de ma base de données, elle ne peut passer qu'une seul valeur par paramètre de cette procédure.

C'est exact, mais le trigger est là pour gérer tous les cas d'insertion de lignes dans la table dbo.USERS, aussi bien par la procédure stockée que vous donnez, que par une autre ou par un INSERT manuel
Si ce n'est pas le comportement que vous souhaitez, et si le seul moyen d'ajouter des lignes dans cette table est la procédure stockée que vous donnez, alors codez l'historisation dans la même procédure stockée

n'est pas correcte car mes champs 'us_id' et 'us_id_change' on toujours les même valeurs. ^^

Je comprend, mais vous n'avez pas encore donné la façon d'obtenir le us_id_change.

Enfin si vous êtes sous SQL Server 2008, vous pouvez penser aux audits de sécurité

@++

[AIDExpress]

Bonjour elsuket

us_id_change est l'utilisateur identifié qui utilise l'application.
Cet utilisateur est dans table USERS.
Évidement j'ai du entré un premier utilisateur administrateur dans ma table avant de mettre en place les trigger etc... ^^

Merci pour vos indications. Je comprend mieux ce qui n'était pas clair pour moi.

Mon système fonctionne actuellement mais je vais pourvoir le rendre plus propre et l'optimisé grâce a vous.

Bon weekend =)

[elsuket]

Bon courage et bon week-end à vous aussi

@++