Re: Triggers in postgres

On Tue, 2002-03-19 at 16:40, Rob wrote:
> Hi,
>
> I have a product table with a column barcode.  After a product has been
> deleted, I want to create a trigger to delete references to that product
> in other tables.  I've got the following trigger
>
> CREATE TRIGGER
> AFTER DELETE
> ON TABLE product
> FOR EACH ROW
>
> Now, what I would ideally like to do is issue a simple sql delete command,
> but I think I actually have to define a function (is this true?).  Say I
> do, then the rest of my trigger would look like
>
> EXECUTE PROCEDURE someProc();
>
> Now I need to pass somProc the barcode of the product I've just deleted.
> How do I do this?

You should use referential integrity to link the tables together; then
what you want will be done for you.

CREATE TABLE product (barcode VARCHAR(10) PRIMARY KEY,
                      ... etc ...);

CREATE TABLE deptable (id INTEGER PRIMARY KEY,
                       barcode VARCHAR(10) NOT NULL
                                           REFERENCES product (barcode)
                                           ON UPDATE CASCADE
                                           ON DELETE SET NULL
                       ... etc ...);

This will automatically create triggers to do what you want (ON DELETE
SET NULL).  You could also delete the dependent records entirely (ON
DELETE CASCADE).  It will also have the advantage that you will not be
able to insert barcodes in deptable unless they already exist in
product.  Finally, if you change a barcode in product, the change will
be reflected in the dependent tables (ON UPDATE CASCADE).


But if you want to roll your own, write the trigger function in plpgsql
and see the section in its part of the manual on writing triggers
(Programmer's Guide section 23.9).

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     " And God shall wipe away all tears from their eyes;
      and there shall be no more death, neither sorrow, nor
      crying, neither shall there be any more pain: for the
      former things are passed away."
                          Revelations 21:4