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.
