INSERT triggers
I am trying to create a trigger to duplicate a table into another.
The reason a DBA procedure is used is that the second table is to be on a
separate machine, accessed through I-STAR. I do not want to go about
creating host equivalences on both the machines. By using a DBA procedure,
I should be able to just set the equivalence for the DBA account ( I hope ).
The problem is what do you do with the return values from the SPL
when it is called from a INSERT trigger ? The storage of the procedures
work fine, but on inserting a row, the following appears :
684: Procedure (tilh.copy_awbtrack) returns too many values.
Error in line 14Near character position 9
---------------------------
drop procedure copy_awbtrack;
drop trigger copy_trigger;
CREATE DBA PROCEDURE copy_awbtrack (
v_track_time DATETIME YEAR TO MINUTE ,
v_source CHAR(20),
v_stn CHAR(6),
v_trace_ref CHAR(12) ,
v_awb_str CHAR(10),
v_rem CHAR(120)
) RETURNING INT;
DEFINE status INTEGER;
ON EXCEPTION
SET status
END EXCEPTION
INSERT INTO awbtrack_copy (
track_time, source, stn, trace_ref, awb_str, rem
) VALUES (
v_track_time, v_source, v_stn, v_trace_ref, v_awb_str, v_rem);
RETURN status;
END PROCEDURE;
----------------------
CREATE TRIGGER copy_trigger
INSERT ON awbtrack
REFERENCING NEW AS new_rec
FOR EACH ROW (EXECUTE PROCEDURE copy_awbtrack(
new_rec.track_time,
new_rec.source,
new_rec.stn,
new_rec.trace_ref,
new_rec.awb_str,
new_rec.rem) )