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

can a trigger on insert -> update other tables?

From
will trillich
Date:
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 '
    DECLARE

        n ALIAS FOR $1;
        opinion   char(1) := upper(substring(n.rating from 1 for 1));

    BEGIN

        UPDATE sometable  SET fld = fld + 1 WHERE id = .... ;
        UPDATE othertable SET fld = fld + 1 WHERE id = .... ;
    [yada yada]
    END;
' LANGUAGE 'plpgsql';

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

INSERT INTO _rating VALUES ( ... );

the insert into _rating works fine, but none of the other tables
are actually UPDATED. who's got the clue stick?

--
will@serensoft.com
http://www.dontUthink.com/

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

From
brichard@cafod.org.uk (Bruce Richardson)
Date:
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.

And remember to do
    RETURN new
somewhere.


--
Bruce

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

From
will trillich
Date:
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@serensoft.com
http://groups.yahoo.com/group/newbieDoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

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

From
Jan Wieck
Date:
will trillich wrote:
> [...]
>
> 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... :)

    Can you add the line

        #option dump

    at the very top of your function (before DECLARE) and send me
    the debug output from the Postmaster?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


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

From
will trillich
Date:
On Sun, Mar 04, 2001 at 04:52:16AM -0500, Jan Wieck wrote:
> will trillich wrote:
> > [...]
> >
> > 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... :)
>
>     Can you add the line
>
>         #option dump
>
>     at the very top of your function (before DECLARE) and send me
>     the debug output from the Postmaster?

sure! if you like, i can post the source SQL for the ratings
table and its views and functions and triggers and rules...

-- begin LOGFILE --

Execution tree of successfully compiled PL/pgSQL function _rating_propagate:

Functions data area:
    entry 0: REC new
    entry 1: REC old
    entry 2: VAR tg_name          type name (typoid 19) atttypmod -1
    entry 3: VAR tg_when          type text (typoid 25) atttypmod -1
    entry 4: VAR tg_level         type text (typoid 25) atttypmod -1
    entry 5: VAR tg_op            type text (typoid 25) atttypmod -1
    entry 6: VAR tg_relid         type oid (typoid 26) atttypmod -1
    entry 7: VAR tg_relname       type name (typoid 19) atttypmod -1
    entry 8: VAR tg_nargs         type int4 (typoid 23) atttypmod -1
    entry 9: VAR found            type bool (typoid 16) atttypmod -1
    entry 10: VAR opinion          type bpchar (typoid 1042) atttypmod 5
    entry 11: VAR courseID         type int4 (typoid 23) atttypmod -1
    entry 12: VAR topicID          type int4 (typoid 23) atttypmod -1
    entry 13: VAR eduID            type int4 (typoid 23) atttypmod -1
    entry 14: VAR profID           type int4 (typoid 23) atttypmod -1
    entry 15: RECFIELD rating           of REC 0
    entry 16: ROW *internal*       fields courseID=var 11
    entry 17: RECFIELD course           of REC 0
    entry 18: ROW *internal*       fields topicID=var 12
    entry 19: ROW *internal*       fields eduID=var 13
    entry 20: ROW *internal*       fields profID=var 14
    entry 21: RECFIELD who              of REC 0
    entry 22: RECFIELD student          of REC 0
    entry 23: RECFIELD who              of REC 0
    entry 24: RECFIELD student          of REC 0
    entry 25: RECFIELD who              of REC 0
    entry 26: RECFIELD student          of REC 0
    entry 27: RECFIELD who              of REC 0
    entry 28: RECFIELD student          of REC 0
    entry 29: RECFIELD who              of REC 0
    entry 30: RECFIELD rating           of REC 0
    entry 31: RECFIELD student          of REC 0
    entry 32: RECFIELD who              of REC 0
    entry 33: RECFIELD who              of REC 0
    entry 34: RECFIELD student          of REC 0

Functions statements:
  8:BLOCK <<*unnamed*>>
  9:  ASSIGN var 10 := 'SELECT  upper(substring( $1  from 1 for 1)) {$1=15}'
 10:  SELECT 'SELECT  _course.id WHERE _course.id =  $1  {$1=17}'
        target = 16 *internal*
 12:  SELECT 'SELECT  _topic.id WHERE _topic.id = _course.topic AND _course.id =  $1  {$1=11}'
        target = 18 *internal*
 14:  SELECT 'SELECT  _edu.id WHERE _edu.id = _topic.edu AND _topic.id =  $1  {$1=12}'
        target = 19 *internal*
 17:  SELECT 'SELECT  _prof.who WHERE _prof.who =  $1  AND _prof.course =  $2  {$1=21, $2=11}'
        target = 20 *internal*
 19:  IF 'SELECT  NOT  $1  {$1=9}' THEN
 20:    RAISE ''Unable to match professor to class, for rating''
      ELSE
      ENDIF
 22:  IF 'SELECT   $1  = 'A' {$1=10}' THEN
 24:    EXECSQL 'UPDATE _course SET a = a + 1 WHERE _course.id =  $1  {$1=11}'
 25:    EXECSQL 'UPDATE _topic SET a = a + 1 WHERE _topic.id =  $1  {$1=12}'
 26:    EXECSQL 'UPDATE _edu SET a = a + 1 WHERE _edu.id =  $1  {$1=13}'
 27:    EXECSQL 'UPDATE _student SET a = a + 1 WHERE _student.who =  $1  {$1=22}'
 28:    EXECSQL 'UPDATE _faculty SET a = a + 1 WHERE _faculty.who =  $1  AND _faculty.edu =  $2  {$1=23, $2=13}'
 29:    EXECSQL 'UPDATE _prof SET a = a + 1 WHERE _prof.who =  $1  AND _prof.course =  $2  {$1=14, $2=11}'
      ELSE
 32:    IF 'SELECT   $1  = 'C' {$1=10}' THEN
 33:      EXECSQL 'UPDATE _course SET c = c + 1 WHERE _course.id =  $1  {$1=11}'
 34:      EXECSQL 'UPDATE _topic SET c = c + 1 WHERE _topic.id =  $1  {$1=12}'
 35:      EXECSQL 'UPDATE _edu SET c = c + 1 WHERE _edu.id =  $1  {$1=13}'
 36:      EXECSQL 'UPDATE _student SET c = c + 1 WHERE _student.who =  $1  {$1=24}'
 37:      EXECSQL 'UPDATE _faculty SET c = c + 1 WHERE _faculty.who =  $1  AND _faculty.edu =  $2  {$1=25, $2=13}'
 38:      EXECSQL 'UPDATE _prof SET c = c + 1 WHERE _prof.who =  $1  AND _prof.course =  $2  {$1=14, $2=11}'
        ELSE
 41:      IF 'SELECT   $1  = 'D' {$1=10}' THEN
 42:        EXECSQL 'UPDATE _course SET d = d + 1 WHERE _course.id =  $1  {$1=11}'
 43: Couldn't open /var/lib/postgres/data/pg_options
010304.13:07:18.501 [15216] NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "_student"
010304.13:07:18.513 [15216] NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "_student"
010304.13:07:18.519 [15216] NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "_course"
010304.13:07:18.533 [15216] NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "_course"
010304.13:07:18.547 [15216] NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "_who"
010304.13:07:18.566 [15216] NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "_who"
010304.13:07:18.577 [15216] NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "l_rating"
010304.13:07:18.590 [15216] NOTICE:  DROP TABLE implicitly drops referential integrity trigger from table "l_rating"
010304.13:07:18.684 [15216] NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index '_rating_pkey' for table
'_rating'
010304.13:07:18.684 [15216] NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
       EXECSQL 'UPDATE _topic SET d = d + 1 WHERE _topic.id =  $1  {$1=12}'
 44:        EXECSQL 'UPDATE _edu SET d = d + 1 WHERE _edu.id =  $1  {$1=13}'
 45:        EXECSQL 'UPDATE _student SET d = d + 1 WHERE _student.who =  $1  {$1=26}'
 46:        EXECSQL 'UPDATE _faculty SET d = d + 1 WHERE _faculty.who =  $1  AND _faculty.edu =  $2  {$1=27, $2=13}'
 47:        EXECSQL 'UPDATE _prof SET d = d + 1 WHERE _prof.who =  $1  AND _prof.course =  $2  {$1=14, $2=11}'
          ELSE
 50:        IF 'SELECT   $1  = 'F' {$1=10}' THEN
 51:          EXECSQL 'UPDATE _course SET f = f + 1 WHERE _course.id =  $1  {$1=11}'
 52:          EXECSQL 'UPDATE _topic SET f = f + 1 WHERE _topic.id =  $1  {$1=12}'
 53:          EXECSQL 'UPDATE _edu SET f = f + 1 WHERE _edu.id =  $1  {$1=13}'
 54:          EXECSQL 'UPDATE _student SET f = f + 1 WHERE _student.who =  $1  {$1=28}'
 55:          EXECSQL 'UPDATE _faculty SET f = f + 1 WHERE _faculty.who =  $1  AND _faculty.edu =  $2  {$1=29, $2=13}'
 56:          EXECSQL 'UPDATE _prof SET f = f + 1 WHERE _prof.who =  $1  AND _prof.course =  $2  {$1=14, $2=11}'
            ELSE
 58:          ASSIGN var 30 := 'SELECT  'B''
 60:          EXECSQL 'UPDATE _course SET b = b + 1 WHERE _course.id =  $1  {$1=11}'
 61:          EXECSQL 'UPDATE _topic SET b = b + 1 WHERE _topic.id =  $1  {$1=12}'
 62:          EXECSQL 'UPDATE _edu SET b = b + 1 WHERE _edu.id =  $1  {$1=13}'
 63:          EXECSQL 'UPDATE _student SET b = b + 1 WHERE _student.who =  $1  {$1=31}'
 64:          EXECSQL 'UPDATE _faculty SET b = b + 1 WHERE _faculty.who =  $1  AND _faculty.edu =  $2  {$1=32, $2=13}'
 65:          EXECSQL 'UPDATE _prof SET b = b + 1 WHERE _prof.who =  $1  AND _prof.course =  $2  {$1=14, $2=11}'
            ENDIF
          ENDIF
        ENDIF
      ENDIF
 70:  EXECSQL 'delete from trak'
 71:  EXECSQL 'insert into trak values( $1 , $2 , $3 , $4 , $5 ) {$1=13, $2=12, $3=11, $4=33, $5=34}'
 72:  RETURN record 0
    END -- *unnamed*

End of execution tree of function _rating_propagate

-- end LOGFILE --

of course, these tables are intricately related to others not
mentioned here... but i'm hoping you can rub your eyeballs over
this and think to yourself "what a newbie! if he'd only ditch the
clavis and use the frammistat, it'd be so simple..." :)

if so, bring it on!

--
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@serensoft.com
http://groups.yahoo.com/group/newbieDoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!