Re: can a trigger on insert -> update other tables?

On Sat, Mar 03, 2001 at 02:08:18PM +0000, Bruce Richardson wrote:
> On Fri, Mar 02, 2001 at 03:13:19PM -0600, will trillich wrote:
> > i've got a "_rating" table that, when a new record is added,
> > i'd like to have propagate through some other tables to update
> > running totals:
> >
> > CREATE FUNCTION _rating_propagate( _rating ) RETURNS OPAQUE AS '
>
> Trigger functions shouldn't have parameters. And you don't need the NEW
> in this line:
> > FOR EACH ROW EXECUTE PROCEDURE _rating_propagate( NEW );
>
> The new variable is automatically made available to the trigger
> function.

well that's not something my 7.0.3 posgresql likes, apparently:

CREATE FUNCTION "_rating_propagate" ( ) RETURNS opaque AS '
DECLARE
opinion char(1) := upper(substring(NEW.rating from 1 for 1));
BEGIN
IF opinion = ''A'' THEN
-- A == excellent
UPDATE _student SET a = a + 1 WHERE _student.who = NEW.student;
UPDATE _faculty SET a = a + 1 WHERE _faculty.who = NEW.who AND _faculty.edu = NEW.edu;

[snippage]

END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER _rating_propagate
BEFORE INSERT ON _rating
FOR EACH ROW EXECUTE PROCEDURE _rating_propagate();

ERROR: NEW used in non-rule function

(this doesn't happen until an insert actually activates the
trigger, so that plpgsql finally 'sees' the code of the procedure
and tries to execute it.) so what else could i try?

> And remember to do
> RETURN new
> somewhere.

right. but now i have a different problem... :)

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
-- Isaac Asimov, 'The Genetic Code'

will(at)serensoft(dot)com
http://groups.yahoo.com/group/newbieDoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!