Forums PostgreSQL.fr

Morby

Membre

encore un probleme de trigger

salut tout le monde, de retour avec un autre problème de trigger sur mon mini-wiki

j'ai une table d'utilisateurs :

DROP TABLE LGPUTILS CASCADE;
CREATE TABLE LGPUTILS
       (NUMUSER SERIAL,
        NOM VARCHAR(25) PRIMARY KEY,
	PASSWORD VARCHAR(25) NOT NULL,
	NBARTICLECREE INTEGER DEFAULT '0'
	);

-- droits d'accès pour la séquence (SERIAL)
GRANT USAGE on lgputils_numuser_seq to public;

--création d'une vue LGPUSERNEW qui permet de voir tous les attributs de la table
DROP VIEW LGPUSERNEW;
CREATE VIEW LGPUSERNEW AS SELECT * FROM LGPUTILS;

-- donne les droits sur la vue LGPUSERNEW
grant select on LGPUSERNEW to public;
grant insert on LGPUSERNEW to public;
grant update on LGPUSERNEW to public;
grant delete on LGPUSERNEW to public;


-- création d'une règle sur la vue "LGPUSERNEW"
-- permettant l'insertion de nouveaux utilisateurs avec automatisme : nom = compte utilisateur
DROP RULE usernew_insert on LGPUSERNEW CASCADE;
CREATE RULE usernew_insert AS ON INSERT TO LGPUSERNEW DO INSTEAD 
	INSERT INTO LGPUTILS VALUES 
	(default, 
	current_user, 
	NEW.PASSWORD);

une table de sujets :

DROP TABLE LGPSUJET CASCADE;
CREATE TABLE LGPSUJET
       (NUMSUJET SERIAL PRIMARY KEY,
	AUTEUR VARCHAR(25) REFERENCES LGPUTILS (NOM),
	TITRE VARCHAR NOT NULL,
	DATEPARUTION DATE DEFAULT current_date,
	DATEMODIF DATE DEFAULT current_date,
	AUTEURMODIF VARCHAR(25) REFERENCES LGPUTILS (NOM),
	NUMBACKUP INTEGER DEFAULT '1',
	NBVUE INTEGER DEFAULT '0',
	TEXTE VARCHAR NOT NULL,
	PROTECTION INTEGER DEFAULT '1'	);

-- droits d'accès pour la séquence (SERIAL)
GRANT USAGE on lgpsujet_numsujet_seq to public;

-- création d'une vue qui permet de voir tous les sujets de la table
DROP VIEW ALLSUJET;
CREATE VIEW ALLSUJET AS SELECT * FROM LGPSUJET;
grant select on ALLSUJET to public;
grant insert on ALLSUJET to public;

-- création d'une règle qui permet l'insertion de nouveaux sujets à travers la vue ALLSUJET
DROP RULE sujet_insert ON ALLSUJET;
CREATE RULE sujet_insert AS ON INSERT TO ALLSUJET DO INSTEAD
    INSERT INTO LGPSUJET (auteur, titre, texte) VALUES
    (current_user,
    NEW.TITRE,
    NEW.TEXTE);

et enfin une table pour les autorisations de modification des sujets :

DROP TABLE LGPAUTH CASCADE;	
CREATE TABLE LGPAUTH
       (NUMSUJET INTEGER REFERENCES LGPSUJET (NUMSUJET),
        AUTEUR VARCHAR(25) REFERENCES LGPUTILS (NOM),
	AUTORISEDTOMODIF VARCHAR(25));

-- création d'une vue sur la table LGPAUTH
CREATE VIEW AUTORIS AS SELECT * FROM LGPAUTH;
grant select on AUTORIS to public;
grant insert on AUTORIS to public;
grant delete on AUTORIS to public;

-- création d'une règle qui permet l'insertion de nouvelles autorisations
-- à placer dans un trigger "before", qui vérifie que auteur = current_user
DROP RULE donne_autorisation ON AUTORIS;
CREATE RULE donne_autorisation AS ON INSERT TO AUTORIS DO INSTEAD
    INSERT INTO LGPAUTH (NUMSUJET,AUTEUR,AUTORISEDTOMODIF) VALUES
	(NEW.NUMSUJET,
	current_user,
	NEW.AUTEUR);

cette table référence un numéro de sujet + le nom de son auteur + le nom de l'utilisateur autorisé à modifier le sujet en colonne 1

je désire donc créer un trigger qui va vérifier que le current_user qui fait l'insert dans cette table est bien l'auteur du n° de sujet à qui il veut donner l'autorisation de modification à une autre utilisateur
pour bien faire les choses, faudrait aussi vérifier que l'utilisateur à qui on veut donner l'autorisation est bien présent dans la table des utilisateurs...

voici le trigger que j'ai pondu :

DROP FUNCTION fonction_verif_autorisation() CASCADE;
CREATE FUNCTION fonction_verif_autorisation() RETURNS TRIGGER AS $$
BEGIN
IF (NEW.NUMSUJET = NUMSUJET FROM (SELECT NUMSUJET FROM ALLSUJET WHERE auteur=current_user))
THEN
INSERT INTO LGPAUTH (NUMSUJET,AUTEUR,AUTORISEDTOMODIF) VALUES
	(NEW.NUMSUJET,
	current_user,
	NEW.AUTEUR);
	RETURN NEW;
	END IF;
RETURN NULL;
	END;
$$ LANGUAGE plpgsql;

DROP TRIGGER fonction_verif_autorisation ON lgpsujet;
CREATE TRIGGER fonction_verif_autorisation BEFORE INSERT OR DELETE ON LGPAUTH
FOR EACH ROW EXECUTE PROCEDURE fonction_verif_autorisation();

La fonction me fait cette erreur quand j'essaye de l'enregistrer :
ERREUR:  erreur de syntaxe sur ou près de « FROM »
LIGNE 3 : IF (NEW.NUMSUJET = NUMSUJET FROM (SELECT NUMSUJET FROM ALLSU...
                                                              ^

donc problème de syntaxe sur ma requete mais je ne m'en sort pas
tout coup de main est bienvenu, merci

rjuju

Administrateur

Re : encore un probleme de trigger

Bonjour.

La syntaxe est fausse, on ne peut pas écrire "NUMSUJET FROM (SELECT ...", il faut utiliser une syntaxe sql.
Cependant, ce select peut renvoyer plusieurs lignes, il serait préférable de faire un requête qui vérifie directement le bon auteur je pense.

Exemple :
DECLARE
ok boolean;
SELECT (current_user = auteur) INTO ok FROM  ALLSUJET WHERE NUMSUJET = NEW.NUMSUJET;
IF ok THEN
...

---

Julien.
https://rjuju.github.io/

Morby

Membre

Re : encore un probleme de trigger

merci pour ton aide
effectivement je n'avais pas du tout pensé à utiliser un booléen, et quand bien même cette syntaxe m'est complètement inconnue

voici donc la nouvelle mouture de mon trigger :

CREATE FUNCTION fonction_verif_autorisation() RETURNS TRIGGER AS $$
DECLARE
ok boolean;
BEGIN
SELECT (current_user = auteur) INTO ok FROM  ALLSUJET WHERE NUMSUJET = NEW.NUMSUJET;
IF ok THEN
INSERT INTO AUTORIS (NUMSUJET,AUTEUR,AUTORISEDTOMODIF) VALUES
	(NEW.NUMSUJET,
	current_user,
	NEW.AUTEUR);
	RETURN NEW;
	END IF;
RETURN NULL;	
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER fonction_verif_autorisation BEFORE INSERT OR DELETE ON LGPAUTH
FOR EACH ROW EXECUTE PROCEDURE fonction_verif_autorisation();

et maintenant j'ai une nouvelle erreur :

PL/pgSQL function "fonction_verif_autorisation" line 7 at instruction SQL
instruction SQL « INSERT INTO AUTORIS (NUMSUJET,AUTEUR,AUTORISEDTOMODIF) VALUES
(NEW.NUMSUJET,
current_user,
NEW.AUTEUR) »

j'ai essayé de modifier la fin de la requete en :
...
RETURN NEW;
ELSE
RETURN NULL;
END IF;   
END;

mais rien n'y fait, je ne trouve pas d'où vient l'erreur

Marc Cousin

Membre

Re : encore un probleme de trigger

Je ne suis pas rentré dans le code, mais faire une rule sur une vue pour rediriger un insert sur une table pour déclencher un trigger, c'est quand même compliqué pour à mon avis pas grand chose…

Pour alléger tout ça, deux solutions:

- Faire tout simplement une fonction à appeler pour faire l'insertion, genre «select insere_nouvelle_autorisation(param1,param2);»
- Ne supporter que PostgreSQL 9.1 et supérieur (c'est un nouveau développement non ?). Si oui, on peut mettre en 9.1 le trigger directement sur la vue (c'est un trigger INSTEAD OF), ce qui est quand même bien plus élégant.

---

Marc.

Morby

Membre

Re : encore un probleme de trigger

merci mon cher mais vous me parlez un langage qui m'est en grande partie étranger ^^

en effet je suis bien sous PostgreSQL 9.1 et c'est un nouveau développement, dans le cadre d'un projet que je dois remettre la semaine prochaine et qui comptera pour ma note finale en mars prochain (formation accélérée). En ce qui me concerne, je suis grand débutant, ya encore 3 mois j'ignorais tout des bases de données. En cours, on a pas du tout abordé les triggers sous Postgres, on a eu juste 2h de cours sur les triggers sous Oracle + un TP que je ne suis pas parvenu à faire ayant du retard sur les précédant. ça explique surement en grande partie mon inélégance dans mon approche pour résoudre mon problème.

Donc d'après vous je peux me passer de la règle qui autorise les inserts sur la vue ?
et seulement faire un trigger/fonction qui gèrera à la fois l'autorisation d'insert sur la vue et la vérification auteur=current_user ?

puis-je abuser de votre gentillesse et vous demander un exemple de code svp ?
car pour moi, un petit dessin vaut souvent mieux qu'un long discours ^^

Marc Cousin

Membre

Re : encore un probleme de trigger

Si vous êtes en 9.1, oui, oubliez la RULE. Déclarez directement un trigger sur la vue, avec instead of.

Pour ce qui est des exemples, il y en a un dans la doc:
http://docs.postgresql.fr/9.1/plpgsql-trigger.html (cherchez INSTEAD OF dans la page), c'est l'exemple 39.5

---

Marc.

Morby

Membre

Re : encore un probleme de trigger

j'ai suivi vos conseils et me suis référé à la doc, effectivement ça simplifie les choses de zapper la RULE

j'ai donc modifié ma fonction/trigger comme ceci :

CREATE FUNCTION fonction_insert_autorisation() RETURNS TRIGGER AS $$
DECLARE
ok boolean;
BEGIN
SELECT (current_user = auteur) INTO ok FROM  ALLSUJET WHERE NUMSUJET = NEW.NUMSUJET;
IF ok THEN
INSERT INTO AUTORIS (NUMSUJET,AUTEUR,AUTORISEDTOMODIF) VALUES
	(NEW.NUMSUJET,
	current_user,
	NEW.AUTEUR);
	RETURN NEW;
	END IF;
RETURN NULL;	
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER fonction_insert_autorisation ON AUTORIS;
CREATE TRIGGER fonction_insert_autorisation 
INSTEAD OF INSERT ON AUTORIS
FOR EACH ROW EXECUTE PROCEDURE fonction_insert_autorisation();

il semblerait que je sois obligé de faire un autre trigger/fonction en cas de DELETE sur la table (avec un return OLD si j'ai bien tout compris)

ceci étant, j'ai toujours le même problème d'erreur au déclenchement du trigger lorsque j'essaye de faire un INSERT sur la vue :

PL/pgSQL function "fonction_insert_autorisation" line 7 at instruction SQL
instruction SQL « INSERT INTO AUTORIS (NUMSUJET,AUTEUR,AUTORISEDTOMODIF) VALUES
(NEW.NUMSUJET,
current_user,
NEW.AUTEUR) »

ce message d'erreur semble boucler sur lui même, il rempli toute la console, j'en déduis que c'est ma boucle qui déconne.

comme décrit précédemment, j'ai essayé de modifier la fin de la requete en :
...
RETURN NEW;
ELSE
RETURN NULL;
END IF;   
END;

mais ça ne change rien au résultat

Marc Cousin

Membre

Re : encore un probleme de trigger

Vous n'êtes pas 'obligé' de faire un trigger différent en cas de delete: vous pouvez très bien utiliser la variable TG_OP qui vous permet de savoir si vous êtes en cours de INSERT/DELETE/UPDATE/TRUNCATE. C'est juste un choix: 4 triggers plus simples, mais avec des risques de duplication de code ou un trigger qui fait tout.

Pour ce qui est de l'erreur, c'est normal: vous réinsérez sur la vue dans un trigger sur elle-même. Vous partez donc en boucle.

---

Marc.

Morby

Membre

Re : encore un probleme de trigger

j'ai intégré la fonction TG_OP

CREATE FUNCTION fonction_insert_autorisation() RETURNS TRIGGER AS $$
DECLARE
ok boolean;
BEGIN
SELECT (current_user = auteur) INTO ok FROM  ALLSUJET WHERE NUMSUJET = NEW.NUMSUJET;
IF ok THEN
INSERT INTO LGPAUTH (NUMSUJET,AUTEUR,AUTORISEDTOMODIF) VALUES
	(NEW.NUMSUJET,
	current_user,
	NEW.AUTEUR);
		IF (TG_OP = 'UPDATE') THEN
		RETURN NEW;
		ELSIF (TG_OP = 'DELETE') THEN
		RETURN OLD;
		END IF;
	ELSE RETURN NULL;
	END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER fonction_insert_autorisation 
INSTEAD OF INSERT OR DELETE ON AUTORIS
FOR EACH ROW EXECUTE PROCEDURE fonction_insert_autorisation();

EDIT : j'ai finalement compris pourquoi je partais en boucle...l'insertion effectuée par le trigger a pour effet de redéclencher le meme trigger ^^
en mettant le nom de la table originale plutôt que le nom de la vue pour l'insert ça corrige le problème

reste maintenant un problème de syntaxe foirée pour mon TG_OP...
ERREUR:  le contrôle a atteint la fin de la procédure trigger sans RETURN
CONTEXTE : fonction PL/pgsql « fonction_insert_autorisation »

Dernière modification par Morby (19/01/2012 11:30:42)

Marc Cousin

Membre

Re : encore un probleme de trigger

Si c'est dans le cas d'un INSERT, c'est normal, il n'est pas traité. Un INSERT ne passera sur aucun des RETURN

---

Marc.

Morby

Membre

Re : encore un probleme de trigger

quel boulet je suis...
Bourvil aurait dit un truc du genre : effectivement ça va marcher bcp moins bien ^^

le trigger est maintenant opérationnel et fonctionnel, encore merci pour ta patience

encore une petite question, d'ordre cosmétique cette fois :
pour le cas de figure ou current_user est différent de new.auteur, est-il possible d'afficher dans le prompt un message de type "nous n'êtes pas autorisé à donner des droits d'accès sur ce n° de sujet " ?

Marc Cousin

Membre

Re : encore un probleme de trigger

Oui, il suffit de faire un RAISE, pour ça. Soit une trace de warning, qui n'annulera pas le traitement, soit carrément déclencher l'arrêt brutal avec une exception.

Ça donne :

RAISE WARNING 'L''utilisateur % n'est pas autorisé à donner des droits d''accès sur le numéro de sujet %', current_user, NEW.numsujet;

---

Marc.

Morby

Membre

Re : encore un probleme de trigger

super génial, ça fonctionne parfaitement
tout mes remerciements pour ce forumo-guidage de qualité