ASMS 2009
updating a value with a trigger
Document Actions
i just had the problem that I want to update a field on a table when ever somebody inserts something in this table. So my first aproach was to just make a select to get the value of the version
SELECT count("bin_id") INTO tmpVar FROM bin;
and afterwards update the table
UPDATE....
and thats the problem you will allways get an mutating error....
to avoid this you must set the actual value to the buffer!
this is the solution:
CREATE OR REPLACE TRIGGER QUANTIFICATION
BEFORE INSERT
ON BINBASE.QUANTIFICATION
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
tmpVar := 0;
SELECT count("bin_id") INTO tmpVar FROM bin;
:new."version" := tmpVar;
END quantification;
/
SELECT count("bin_id") INTO tmpVar FROM bin;
and afterwards update the table
UPDATE....
and thats the problem you will allways get an mutating error....
to avoid this you must set the actual value to the buffer!
this is the solution:
CREATE OR REPLACE TRIGGER QUANTIFICATION
BEFORE INSERT
ON BINBASE.QUANTIFICATION
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpVar NUMBER;
BEGIN
tmpVar := 0;
SELECT count("bin_id") INTO tmpVar FROM bin;
:new."version" := tmpVar;
END quantification;
/
Created by
zwluxx
Last modified 2005-08-05 04:41 PM
Last modified 2005-08-05 04:41 PM
