Mutation Trigger Error

[Anand]

Hi Tim,

I am facing following error during trigger execution:-

Error: Trigger TR_RC_RECON_MEASURE_KEYS_BK is mutting ,trigger/function may not see it.

-- Idea of trigger is to restrict the entry of those row in "RC_RECON_MEASURE_KEYS_BK" table
-- where operator id (i.e "REC_AGG_OPR_ID") is other than " = "(i.e. assignment) in the cases where dimension flag is not set
-- i.e RC_RECON_PASSES.REC_DIM_FLAG = 0

-- Query to produce the above error:-
INSERT INTO RC_RECON_MEASURE_KEYS_BK
(REC_ID,
REC_PASS_ID,
REC_SRC1_COL_ID,
REC_SRC2_COL_ID,
REC_AGG_OPR_ID,
SRC1_MEAS_TOLERANCE,
SRC2_MEAS_TOLERANCE,
REC_VARIANCE_THLD)
VALUES
(1, 2, 49, 9, 1, 0, 0, 0.00);

CREATE OR REPLACE TRIGGER TR_RC_RECON_MEASURE_KEYS_BK
AFTER INSERT ON RC_RECON_MEASURE_KEYS_BK
FOR EACH ROW
DECLARE
lv_operator RC_AGGREGATE_OPERATORS.REC_AGG_OPR%Type := '';
lv_rec_dim_flag RC_RECON_PASSES.REC_DIM_FLAG%TYPE := '';
--PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

SELECT REC_DIM_FLAG INTO lv_rec_dim_flag
FROM RC_RECON_PASSES
WHERE REC_ID = :NEW.REC_ID
AND REC_PASS_ID = :NEW.REC_PASS_ID;

IF lv_rec_dim_flag = 0 THEN
SELECT RAO.REC_AGG_OPR INTO lv_operator
FROM RC_RECON_MEASURE_KEYS_BK RMK,
RC_AGGREGATE_OPERATORS RAO
WHERE RMK.REC_AGG_OPR_ID = RAO.REC_AGG_OPR_ID
AND RAO.REC_AGG_OPR_ID = :NEW.REC_AGG_OPR_ID;

IF lv_operator <> '='THEN
RAISE_APPLICATION_ERROR(-20000,'Only assignment operator is allowed for pass without dimension flag set.');
END IF;
END IF;
END TR_RC_RECON_MEAS_KEYS_BK;

-- Table Involved in the trigger.
create table RVM_SCHEMA1.RC_RECON_PASSES
(
REC_ID NUMBER(5) not null,
REC_PASS_ID NUMBER(5) not null,
REC_PASS_DESC VARCHAR2(200),
REC_PASS_PLUGIN VARCHAR2(200),
REC_DIM_FLAG NUMBER(1) default 0 not null
);

CREATE TABLE RC_RECON_MEASURE_KEYS_BK
(
REC_ID NUMBER(5) NOT NULL,
REC_PASS_ID NUMBER(5) NOT NULL,
REC_SRC1_COL_ID NUMBER(5),
REC_SRC2_COL_ID NUMBER(5),
REC_AGG_OPR_ID NUMBER(3) NOT NULL,
SRC1_MEAS_TOLERANCE NUMBER(5) NOT NULL,
SRC2_MEAS_TOLERANCE NUMBER(5) NOT NULL,
REC_VARIANCE_THLD NUMBER(7,2) NOT NULL
);

CREATE TABLE RC_AGGREGATE_OPERATORS
(
REC_AGG_OPR_ID NUMBER(5) NOT NULL,
REC_AGG_OPR VARCHAR2(50) NOT NULL,
REC_AGG_DESC VARCHAR2(100)
);


insert into rc_recon_passes (REC_ID, REC_PASS_ID, REC_PASS_DESC, REC_PASS_PLUGIN, REC_DIM_FLAG)
values (1, 2, 'Second Pass to implicit summary matching without dimension.', '', 0);

INSERT INTO rc_aggregate_operators (REC_AGG_OPR_ID, REC_AGG_OPR, REC_AGG_DESC)
VALUES (1, 'COUNT', 'Used to return number of rows returned by the query.');

INSERT INTO rc_aggregate_operators (REC_AGG_OPR_ID, REC_AGG_OPR, REC_AGG_DESC)
VALUES (2, 'SUM', 'Used to return the sum of values of expression.');

INSERT INTO rc_aggregate_operators (REC_AGG_OPR_ID, REC_AGG_OPR, REC_AGG_DESC)
VALUES (3, '=', 'Used for equality comparison');

I don't want to write a separate package for resolving this Mutation table error.
Could you please suggest me a alternate way of resolving this issue.
*******Help through code would be most welcomed.

Please assist.

Thanks in advance.

Regards,
Anand Kumar Ojha

[Tim...]

Hi.

This article shows the typical solutions to this issue.

http://www.oracle-base.com/articles/9i/ ... ptions.php

You will need to use a combination of statement and row level triggers to solve it. It's best to use the temporary table approach as it remains transactional. If you use package variables or autonomous transactions you risk screwing everything up if Oracle performs a rerun of the DML, which it does surprisingly often.

Cheers

Tim...

[Anand]

Hi Tim,

First of all thanks for your reply.

Furthermore , I want to ask you that "how can I replace the trigger (posted earlier) with COMPOUND TRIGGER",so that I can resolve the mutation trigger problem.I am asking this as we are using 11g and senior collegue of mine has instructed me to write COMPOUND TRIGGER in place of normal trigger to
resolve this mutation table error.

I tried myself and trigger compiled successfuly but Mutation problem still persist. Here is the code for your kind reference.

CREATE OR REPLACE TRIGGER TR_RC_RECON_MEASURE_KEYS_BK
FOR INSERT ON RC_RECON_MEASURE_KEYS_bk
COMPOUND TRIGGER
lv_operator RC_AGGREGATE_OPERATORS.REC_AGG_OPR%Type := '';
lv_rec_dim_flag RC_RECON_PASSES.REC_DIM_FLAG%TYPE := '';

BEFORE EACH ROW IS
BEGIN
SELECT REC_AGG_OPR INTO lv_operator
FROM RC_AGGREGATE_OPERATORS
WHERE REC_AGG_OPR_ID = :NEW.REC_AGG_OPR_ID;
dbms_output.put_line(lv_operator);
IF LV_OPERATOR <> 'COUNT' AND (:NEW.REC_SRC1_COL_ID IS NULL OR :NEW.REC_SRC2_COL_ID IS NULL) THEN

RAISE_APPLICATION_ERROR(-20000,'Measure Key columns REC_SRC1_COL_ID and REC_SRC2_COL_ID cannot be NULL
for operator other then COUNT.');
END IF;
SELECT REC_DIM_FLAG INTO lv_rec_dim_flag
FROM RC_RECON_PASSES
WHERE REC_ID = :NEW.REC_ID
AND REC_PASS_ID = :NEW.REC_PASS_ID;
dbms_output.put_line(lv_rec_dim_flag);
END BEFORE EACH ROW;

AFTER EACH ROW IS
BEGIN
IF lv_rec_dim_flag = 0 THEN
dbms_output.put_line(:NEW.REC_AGG_OPR_ID);
SELECT RAO.REC_AGG_OPR INTO lv_operator
FROM RC_RECON_MEASURE_KEYS_bk RMK,
RC_AGGREGATE_OPERATORS RAO
WHERE RMK.REC_AGG_OPR_ID = RAO.REC_AGG_OPR_ID
AND RAO.REC_AGG_OPR_ID = :NEW.REC_AGG_OPR_ID;
IF lv_operator <> '='THEN
RAISE_APPLICATION_ERROR(-20000,
'Only assignment operator (for one-to-one matching) is allowed for pass without dimension flag set.');
END IF;
ELSE
SELECT RAO.REC_AGG_OPR INTO lv_operator
FROM RC_RECON_MEASURE_KEYS_bk RMK,
RC_AGGREGATE_OPERATORS RAO
WHERE RMK.REC_AGG_OPR_ID = RAO.REC_AGG_OPR_ID
AND RAO.REC_AGG_OPR_ID = :NEW.REC_AGG_OPR_ID;
IF lv_operator = '='THEN
RAISE_APPLICATION_ERROR(-20000,'Assignment operator is not allowed when dimension flag is set.');
END IF;
END IF;
END AFTER EACH ROW;
END TR_RC_RECON_MEASURE_KEYS_BK;

Please suggest what alternation I should do in the above compound trigger to resolve mutation problem.

Your prompt help will be highly appreciated.

Regards,
Anand Kumar Ojha

[Tim...]

Hi.

Row level triggers should do virtually nothing. They should just be storing the information for the statement level triggers to work with.

The statement level triggers are the ones that do the work, since the statement level triggers do not suffer from mutating table errors. You can see exactly how I would approach this in the article I linked too.

http://www.oracle-base.com/articles/9i/ ... ptions.php

As I say in the article, using the global temporary table is the safer approach.

Cheers

Tim...