PostgreSQL Delete Trigger

Question

I´m creating a trigger on PGAdminIII where I want to delete the rows that have the foreign key on the other table. However I´m getting a Syntax error and I can´t find where the problem is:

CREATE TRIGGER clienteDelete
BEFORE DELETE ON cliente
FOR EACH ROW
BEGIN
    DELETE FROM contacto WHERE contacto.id = OLD.contacto_idcontacto;
END;

ERROR:  syntax error at or near "BEGIN"
LINE 4: BEGIN
        ^

********** Error **********

ERROR: syntax error at or near "BEGIN"
SQL state: 42601
Character: 68

I´m not used to the syntax of triggers on Postgres but that´s what I know according to the SQL standard. Any help will be highly apreciated

what error are you getting.

errata
– errata

2015-05-16 23:41:11 +00:00
Commented May 16, 2015 at 23:41 — errata
– errata, Commented May 16, 2015 at 23:41

jpw · Accepted Answer · 2015-05-16 23:56:18Z

16

So Postgresql triggers has some limitations, for instance

PostgreSQL only allows the execution of a user-defined function for the triggered action.

So to accomplish what you want you need to define a function and make the trigger fire that. Something like this should work:

CREATE FUNCTION clienteDelete() RETURNS TRIGGER AS $_$
BEGIN
    DELETE FROM contacto WHERE contacto.id = OLD.contacto_idcontacto;
    RETURN OLD;
END $_$ LANGUAGE 'plpgsql';

And the trigger:

CREATE TRIGGER delete_contacto 
BEFORE DELETE ON cliente 
FOR EACH ROW 
EXECUTE PROCEDURE clienteDelete();

I'm no Postgresql expert though so expect the code above to not be perfect.

answered May 16, 2015 at 23:56

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL Delete Trigger

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related