Обсуждение: Update trigger help

Поиск
Список
Период
Сортировка

Update trigger help

От
A Gilmore
Дата:
Hello,

I have a main table (t1), with several other tables referencing the ID
of t1.  When I update a row on t1 Id like to remove any rows that
reference it in the other tables.  The typical way of doing this Id
assume is using ON UPDATE trigger, which Ive written below.  Is this all
correct?  In the past Ive just been using plpgsql for returning rows in
which a VIEW wasn't sufficient, so Id like to just make sure Im doing
this correctly.

CREATE OR REPLACE FUNCTION t1_onUpdate() RETURNS NULL AS '
    DECLARE
    BEGIN
        id = NEW.id;
        DELETE FROM t2 WHERE t1_id = id;
        DELETE FROM t3 WHERE t1_id = id;
        DELETE FROM t4 WHERE t1_id = id;
        DELETE FROM t5 WHERE t1_id = id;
        DELETE FROM t6 WHERE t1_id = id;
    END;
'LANGUAGE plpgsql;

CREATE TRIGGER t1_onUpdate_trig BEFORE UPDATE ON t1
    FOR EACH ROW EXECUTE PROCEDURE t1_onUpdate();

Thank you in advance,
- A Gilmore

Re: Update trigger help

От
A Gilmore
Дата:
A Gilmore wrote:
> Hello,
>
> I have a main table (t1), with several other tables referencing the ID
> of t1.  When I update a row on t1 Id like to remove any rows that
> reference it in the other tables.  The typical way of doing this Id
> assume is using ON UPDATE trigger, which Ive written below.  Is this all
> correct?  In the past Ive just been using plpgsql for returning rows in
> which a VIEW wasn't sufficient, so Id like to just make sure Im doing
> this correctly.

Er, rather here is the function had I proof read my email :

CREATE OR REPLACE FUNCTION t1_onUpdate() RETURNS TRIGGER AS '
     DECLARE
         id INT;
     BEGIN
         id := NEW.id;
         DELETE FROM t2 WHERE t1_id = id;
         DELETE FROM t3 WHERE t1_id = id;
         DELETE FROM t4 WHERE t1_id = id;
         DELETE FROM t5 WHERE t1_id = id;
         DELETE FROM t6 WHERE t1_id = id;
         RETURN NEW;
     END;
'LANGUAGE plpgsql;

CREATE TRIGGER t1_onUpdate BEFORE UPDATE ON t1
     FOR EACH ROW EXECUTE PROCEDURE t1_onUpdate();


Thank you,
- A Gilmore