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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Reply via email to