PostgreSQL Delete Trigger
11,127
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.
Author by
Pablo Estrada
Updated on June 26, 2022Comments
-
Pablo Estrada almost 2 years
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