Thread: Basic Trigger Question

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?

TIA

ps If I need to use a function to do this, pls show me how to do the
same with a function.

--
Ron St.

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';