Thread: Question on setting up trigger.

I have the following table:

CREATE TABLE category (
        id int NOT NULL DEFAULT nextval('_base_id_seq'::text) PRIMARY
KEY,
        parentid int REFERENCES category ( id )
        name text,
);

This allows me to have any number of subcategories without worrying
about how many someone might have.

And I have a function that sets the parentid to the parents parentid
(used when deleting a category) that looks like this:

CREATE FUNCTION "move_catparent" (int4 )
RETURNS int4
AS 'UPDATE category
    SET parentid = ( SELECT parentid FROM category WHERE id = $1 )
    WHERE parentid = $1;
    SELECT 1 AS RESULT'
LANGUAGE 'SQL';

As it stands, I have to do the following when deleting a category:

SELECT move_catparent( <id of category being deleted> );
DELETE FROM category WHERE id=<id of category being deleted>;

I'd like to create a trigger so that I just have to delete the category
and the move_catparent function will be automatically called.  I've read
the CREATE TRIGGER documentation, but I'm just not seeing it.

Can anyone give me some pointers?  Thanks.

Alan

Alan Young wrote:
>
> I have the following table:
>
> CREATE TABLE category (
>         id int NOT NULL DEFAULT nextval('_base_id_seq'::text) PRIMARY
> KEY,
>         parentid int REFERENCES category ( id )
>         name text,
> );
>
> This allows me to have any number of subcategories without worrying
> about how many someone might have.
>
> And I have a function that sets the parentid to the parents parentid
> (used when deleting a category) that looks like this:
>
> CREATE FUNCTION "move_catparent" (int4 )
> RETURNS int4
> AS 'UPDATE category
>     SET parentid = ( SELECT parentid FROM category WHERE id = $1 )
>     WHERE parentid = $1;
>     SELECT 1 AS RESULT'
> LANGUAGE 'SQL';
>
> As it stands, I have to do the following when deleting a category:
>
> SELECT move_catparent( <id of category being deleted> );
> DELETE FROM category WHERE id=<id of category being deleted>;
>
> I'd like to create a trigger so that I just have to delete the category
> and the move_catparent function will be automatically called.  I've read
> the CREATE TRIGGER documentation, but I'm just not seeing it.
>
> Can anyone give me some pointers?  Thanks.

Looks to me like you should be able to:

CREATE FUNCTION move_catparent_trigger() RETURNS OPAQUE AS '
  DECLARE
    old_id INT4;
    old_parent_id INT4;
  BEGIN
    IF TG_OP = ''DELETE'' THEN
      deleted_id := OLD.id ;
      deleted_parentid := OLD.parentid ;
      UPDATE category SET parentid = deleted_parentid WHERE parentid =
deleted_id;
    END IF;
    RETURN NEW;
  END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER move_catparent_trigger
    AFTER DELETE ON category
    FOR EACH ROW EXECUTE PROCEDURE move_catparent_trigger();

I usually find that I end up writing trigger procedures in PLPGSQL
because you can make them so much more maintainable.

Hope this helps,
                    Andrew.

--
_____________________________________________________________________
           Andrew McMillan, e-mail: Andrew@catalyst.net.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267

Saluton,

On Tue, Jan 02, 2001 at 09:38:52AM -0700, Alan Young wrote:
> I have the following table:
>
> CREATE TABLE category (
>         id int NOT NULL DEFAULT nextval('_base_id_seq'::text) PRIMARY
> KEY,
>         parentid int REFERENCES category ( id )
>         name text,
> );
>
...
> CREATE FUNCTION "move_catparent" (int4 )
> RETURNS int4
> AS 'UPDATE category
>     SET parentid = ( SELECT parentid FROM category WHERE id = $1 )
>     WHERE parentid = $1;
>     SELECT 1 AS RESULT'
> LANGUAGE 'SQL';

I myself don't know much about triggers, but I think you need:

create trigger <trigger name> before delete on category
  for each row execute procedure move_catparent(old.id);

On the other hand, I suppose that a rule might be the better option.

I don't know much about this, so don't take this at face value.

Albert.


>
> As it stands, I have to do the following when deleting a category:
>
> SELECT move_catparent( <id of category being deleted> );
> DELETE FROM category WHERE id=<id of category being deleted>;
>
...

--

--------------------------------------------------------------------------
Albert Reiner                                   <areiner@tph.tuwien.ac.at>
Deutsch       *       English       *       Esperanto       *       Latine
--------------------------------------------------------------------------