CODEWITHSUNDEEP
postgresqltriggers
Pablo Estrada
Pablo Estrada

Reputation: 3472

PostgreSQL Delete Trigger

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

Upvotes: 5

Views: 18264

Answers (1)

jpw
jpw

Reputation: 44921

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.

Upvotes: 16

Related Questions