Thread: can a trigger on insert -> update other tables?
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
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!
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
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!