Thread: sql/trigger question...arguments?

hello.

i am new to triggers.  i am trying to write one that inserts a row into a
table (RESOURCE) everytime a new person (PERSON) is added to my db.  the
problem is that i want to include some data from the PERSON insert in the
RESOURCE insert.  (for example, the value of the PersonID field will be
entered into the ResourceID field.)  how do i pass info to a trigger?

thanks
chris

hello.

this is a follow-up to my previous question (shown below).  i want to do the
following...when a row is inserted into my PERSON table, i want to add a row
to the RESOURCE table.  i checked the documentation and tried many
things...none seem to work.

questions:

1. can a postgres trigger contain a insert/select/update/delete statement or
can it contain only a function call?

2. my procedure, if i have to write one, does not return anything.  how do i
handle that in the RETURNS clause?  if i tell it to return, for example, an
int4, then try to create my trigger, it tells me that the sp has to return a
value of type OPAQUE.

3. back to the original question, how do i pass variables?  is it the
:new.PersonID notation?

thanks a lot.

chris

-----Original Message-----
From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On
Behalf Of chris markiewicz
Sent: Friday, September 29, 2000 2:48 PM
To: Postgres (E-mail)
Subject: [GENERAL] sql/trigger question...arguments?


hello.

i am new to triggers.  i am trying to write one that inserts a row into a
table (RESOURCE) everytime a new person (PERSON) is added to my db.  the
problem is that i want to include some data from the PERSON insert in the
RESOURCE insert.  (for example, the value of the PersonID field will be
entered into the ResourceID field.)  how do i pass info to a trigger?

thanks
chris

On Fri, 29 Sep 2000, chris markiewicz wrote:

> this is a follow-up to my previous question (shown below).  i want to do the
> following...when a row is inserted into my PERSON table, i want to add a row
> to the RESOURCE table.  i checked the documentation and tried many
> things...none seem to work.
>
> questions:
>
> 1. can a postgres trigger contain a insert/select/update/delete statement or
> can it contain only a function call?

Well, it calls a function, although that could be a pl function that does
lots of other things.

> 2. my procedure, if i have to write one, does not return anything.  how do i
> handle that in the RETURNS clause?  if i tell it to return, for example, an
> int4, then try to create my trigger, it tells me that the sp has to return a
> value of type OPAQUE.

You can make a function return opaque.  (Use return NEW; to end the
function) -- see below

> 3. back to the original question, how do i pass variables?  is it the
> :new.PersonID notation?

Triggers get the new row passed to it.  The details depend
somewhat on the language in question.  In pl/pgsql, you can use
NEW.<column> to refer to a column in the new row.

There's some details in sections 10 (11, 12) of the user's guide including
a pl/pgsql example trigger:

CREATE TABLE emp (
    empname text,
    salary int4,
    last_date datetime,
    last_user name);

CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS
    BEGIN
        -- Check that empname and salary are given
  
        IF NEW.empname ISNULL THEN
            RAISE EXCEPTION ''empname cannot be NULL value'';
        END IF;
        IF NEW.salary ISNULL THEN
            RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname;
        END IF;

        -- Who works for us when she must pay for?
        IF NEW.salary < 0 THEN
            RAISE EXCEPTION ''% cannot have a negative salary'',
NEW.empname;
        END IF;

        -- Remember who changed the payroll when
        NEW.last_date := ''now'';
        NEW.last_user := getpgusername();
        RETURN NEW;
    END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();