Re: Basic Trigger Question

On Fri, 8 Nov 2002, Aragorn wrote:

> I've used triggers before in another DB  where the total functionality
> was included in the CREATE TRIGGER statement, totally written in SQL. I
> understand that postgresql can't do that (correct?).
>
> I want to delete a row in table 2 when I update table 1, with something
> along the lines of :
> CREATE TRIGGER mytrigger AFTER UPDATE ON table1
>     FOR EACH ROW
>            DELETE FROM table2
>     WHERE table1.id = table2.id
>
> Afterwards I will re-insert any necessary info into table 2.
>
>
> Can anyone help me with this?

I assume that the relationship is something other than that of a foreign
key (otherwise I'd say use a foreign key with on delete cascade).

Since you asked for a trigger answer, I think you'd need a function like
(untested)
CREATE FUNCTION mytriggerfunc() returns OPAQUE AS '
begin
 DELETE FROM table2 where table2.id = OLD.id;
 RETURN NULL;
end;' language 'plpgsql';