I have a problem with my trigger:
CREATE OR REPLACE FUNCTION process_fillDerivedFrom_used() RETURNS TRIGGER AS $fillDerivedFrom_used$
DECLARE
prog varchar(255);
current varchar(255);
BEGIN
SELECT u.iduseentity as prog ,g.idCreatedEntity as current
FROM entity e
JOIN used u ON e.identity=u.iduseentity
JOIN activity a ON a.idactivity=u.idusedactivity
JOIN generatedby g ON g.idcreatoractivity=a.idactivity
INSERT INTO DERIVEDFROM VALUES (prog,current)
END;
$fillDerivedFrom_used$ LANGUAGE plpgsql;
CREATE TRIGGER fillDerivedFrom_used
AFTER INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW EXECUTE PROCEDURE process_fillDerivedFrom_used();
I use 3 tables, the query is correct so I think the error doesn't come from here. But when I copy past this trigger in my terminal nothing happen, no error message, I can write in terminal but I can't execute any query, like I have forgot something in my trigger so I think it's not completely created I think.
I think I mix 2 languages PL/SQL that I have learn at school and the postgresql.
Thank you for you help
I don't know what problems you are having. One is obvious. as is not used for variables; into is. Also, you should name variables so they do not conflict with column names:
CREATE OR REPLACE FUNCTION process_fillDerivedFrom_used() RETURNS TRIGGER AS $fillDerivedFrom_used$
DECLARE
prog v_varchar(255);
current v_varchar(255);
BEGIN
SELECT u.iduseentity as prog, g.idCreatedEntity as current
INTO v_proc, v_current
FROM entity e JOIN
used u
ON e.identity = u.iduseentity JOIN
activity a
ON a.idactivity = u.idusedactivity JOIN
generatedby g
ON g.idcreatoractivity = a.idactivity
INSERT INTO DERIVEDFROM VALUES (v_prog, v_current)
END;
Of course, this begs the question of why you are using variables at all:
CREATE OR REPLACE FUNCTION process_fillDerivedFrom_used() RETURNS TRIGGER AS $fillDerivedFrom_used$
BEGIN
INSERT INTO DERIVEDFROM (prog, current) -- or whatever the column names are
SELECT u.iduseentity as prog, g.idCreatedEntity as current
FROM entity e JOIN
used u
ON e.identity = u.iduseentity JOIN
activity a
ON a.idactivity = u.idusedactivity JOIN
generatedby g
ON g.idcreatoractivity = a.idactivity;
END;
I've done that and it's work, thank you for your help!
CREATE OR REPLACE FUNCTION process_fillDerivedFromGenby() RETURNS TRIGGER AS $fillDerivedFromgenby$
DECLARE
prog varchar(255);
curent varchar(255);
BEGIN
SELECT u.iduseentity , g.idCreatedEntity into prog,curent
FROM entity e
JOIN used u ON e.identity=u.iduseentity
JOIN activity a ON a.idactivity=u.idusedactivity
JOIN generatedby g ON g.idcreatoractivity=a.idactivity
WHERE g.idCreatedEntity =NEW.idCreatedEntity;
--raise notice 'curent: "%" prog by "%"', curent, prog;
INSERT INTO DERIVEDFROM VALUES(prog,curent);
return new;
END;
$fillDerivedFromgenby$ LANGUAGE plpgsql;
CREATE TRIGGER fillDerivedFromgenby AFTER INSERT ON GENERATEDBY
FOR EACH ROW EXECUTE PROCEDURE process_fillDerivedFromGenby();