Thread: Triggers in postgres

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?

Thanks

Rob

--
He who dies with the most toys ...

                    ... still dies

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

Foreign Keys should be able to do what you want.
Example, not at all tested:
CREATE TABLE product (    barcode serial,
                PRIMARY KEY (barcode));
CREATE TABLE catalog (    pk serial,
                fk int4,
                PRIMARY KEY (pk),
                FOREIGN KEY (fk) REFERENCES product
(barcode) ON DELETE CASCADE );

When an entery in the product table is deleted, any enteries in catalog with
fk=barcode should be deleted as well.
For more info see CREATE TABLE in the DOCs
(eg http://www.postgresql.org/idocs/index.php?sql-createtable.html )
Also you should be able to add foriegn keys using ALTER TABLE
Hope this helps,
- Stuart

> -----Original Message-----
> From: Rob [mailto:rob@obsidian.co.za]
> Sent: 19 March 2002 16:40
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] Triggers in postgres
>
>
> 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?
>
> Thanks
>
> Rob
>
> --
> He who dies with the most toys ...
>
>                     ... still dies
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>