Thread: passing argument ro a trigger

hi all,
i have two tables, the first contains major numbers and their status, the
second contains major numbers, all their minors, and a action taken over the
major/minor

table 1
major    status
1    1
2    1
3    1
4    0
5    2
......


table 2
major    minor    action
1    0    2
1    1    0
1    2    1
2    0    4
2    1    0
2    2    2
2    3    2
2    4    1
...

so in the table 1, i have to do so:

CREATE FUNCTION align_status( int4 ) RETURNS int2 AS
'
UPDATE table1 SET status = (
 SELECT action FROM table2
 WHERE major = $1
 AND monir = (
  SELECT MAX( monir ) FROM table1 WHERE major = $1 )
 )
WHERE major = $1;

SELECT  status FROM table1 AS RESULT WHERE major = $1 ;
'
LANGUAGE 'SQL';
;


this works fine, i have only to pass to this function the value of the major
to be aligned. I'd like to connect this to a trigger that fires after an
insert in the table2... eg, i insert 2,5,7 in table2 and status in table1
status must be updated to 7.
The function require an argument, how can i pass the major number to it
trought the trigger?

CREATE TRIGGER trigger_align_status
AFTER INSERT ON table2
FOR EACH STATEMENT
EXECUTE PROCEDURE align_status ( UUUAAAAAAAARRGGHHH );

#!/usr/bin/perl
s/UUAARRGGHH/correct method/ if $you_can ;-)

TIA
Ivan





--
Ninety-Ninety Rule of Project Schedules:
    The first ninety percent of the task takes ninety percent of
    the time, and the last ten percent takes the other ninety percent.

Il Friday 22 February 2002 23:43, pur dolorante per l' artrite alle
dita, scrivesti....
> > CREATE TRIGGER trigger_align_status
> > AFTER INSERT ON table2
> > FOR EACH STATEMENT
> > EXECUTE PROCEDURE align_status ( UUUAAAAAAAARRGGHHH );
>
> Trys this:
>
> Execute Procedure align_status ((select max(major) from
> table1));
> ** note 2 parentesis...

the major modified is not necessarily the last.... but this is an idea!
the number of the major modified can be found looking at insert time :>
Execute Procedure align_status ((
   select major from table1 where major_time = max( major_time )
));
maior_time stores the time of insertion of the touple
thanks for the hint... this solves my problem! but the general idea was:
there is a way for the trigger activated in AFTER mode to know ( and
refer to, and use ) its activator touple ?
the smartest way could be
 CREATE TRIGGER trigger_align_status
 AFTER INSERT ON table2
 FOR EACH STATEMENT
 EXECUTE PROCEDURE align_status ( pointer_to_activator_touple.field );

TIA

--
   (@_  Ivan Fabris, S.Sofia (FC) Powered by Linux Debian Woody   _*)
   //\      www.darthxiong.net   setiathome.ssl.berkeley.edu      /\\
   V_/_     www.folug.linux.it   pgp key @ www.keyserver.net     _\_V