disable AFTER INSERT trigger from the BEFORE insert trigger

[pitz77]

OK, here's what I'm doing:

I want to disable my AFTER INSERT trigger by checking if the new row is a duplicate or is new (based on the criteria in the stored procedure). The BEFORE INSERT trigger calls a stored procedure which will enable or disable the AFTER INSERT trigger. The problem: I was getting "can't use COMMIT statements" error. So, I added the "pragam autonomous transaction" to the stored procedure. Now, whenever I do an insert, the system just hangs.

Any ideas?

The stored procedure:

CREATE OR REPLACE  PROCEDURE SP_ALTER_TRIGGER (
 triggerName IN varchar2, 
 enableDisable IN varchar2
)
AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  strSQL varchar2(100);
begin

IF enableDisable = 'ENABLE' THEN
  strSQL := 'ALTER TRIGGER ' || triggerName || ' ENABLE';
  EXECUTE IMMEDIATE (strSQL);
ELSE
  strSQL := 'ALTER TRIGGER ' || triggerName || ' DISABLE';
  EXECUTE IMMEDIATE (strSQL);
 END IF;
END;

The before insert trigger:

CREATE OR REPLACE TRIGGER CLIN_DTL_B_I_R BEFORE
INSERT ON CLIN_DETAILS FOR EACH ROW DECLARE
  rowCount int;
  strSQL varchar2(100);

BEGIN
--the sequence
If :new.CLIN_DTL_ID Is Not Null Then
	Raise_APPLICATION_ERROR(-20000, 'PK cannot be specified in query');
Else
	Select S_CLIN_DTL.nextVal into :new.CLIN_DTL_ID From dual;
End If;

--check if this CLIN already exists.  If so, disable the AFTER insert trigger

SELECT Count(*) into rowCount 
FROM CLIN_DETAILS 
WHERE CLIN_NUM = :NEW.CLIN_NUM 
AND CTRT_ID = :NEW.CTRT_ID; 

IF rowCount = 0 THEN
  SP_ALTER_TRIGGER ('CLIN_DTL_A_I_R', 'ENABLE');
ELSE
  SP_ALTER_TRIGGER ('CLIN_DTL_A_I_R', 'DISABLE');
END IF;

END;

 

[LKBrwnDBA]

Why not just create a unique index on (CLIN_NUM, CTRT_ID) and forget the ENABLE/DISABLE triggers stuff.

A unique index will guarantee no duplicate rows are inserted!



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[sem]

pitz77, I do apologise, but your solution is really ugly. Triggers have been designed especially to work in any circumstances, disabling them is a DDL operation that should be performed in rare cases and must not be part of an ordinary processing. BTW your trigger also raises well known mutating error.
In your implementation insert hangs because autonomous transaction waits for exclusive lock on table to perform DDL operation while the main one doesn't allow that and waits for control from caled procedure. A typical deadlock.
Why don't you just add some additional if statement to AFTER trigger to check some package variable set in BEFORE?

Regards, Dima

http://seminihin.narod.ru

 

[pitz77]

Well, i checked around and a BEFORE INSERT trigger will not cause a mutating error - and it doesn't, i've tested it.

"duplicate" row is something that will occur on the table and needs to occur on the table. "duplicate" to me, means that the CLIN_NUM is duplicated. If it is duplicated, i do not need the AFTER INSERT trigger to be fired.

So, how do I check TableX for a "duplicate" and decide to NOT fire the trigger on TableX????

 

[SantaMufasa]

Pitz,

Why not add your condition check at the very beginning of the trigger that determines whether or not the code should exectute...any if it shouldn't, just exit the trigger?

Mufasa
(aka Dave of Sandy, Utah, USA)
@ 16:24 (22Oct04) UTC (aka "GMT" and "Zulu"),
@ 09:24 (22Oct04) Mountain Time

 

[pitz77]

the check itself is:

SELECT COUNT(*) FROM TableX WHERE...

The problem: The AFTER INSERT trigger is on TableX.

You can't select anything from TableX within the AFTER INSERT trigger on TableX, because it causes a MUTATION ERROR!!

 

[sem]

In some cases single-row insert doesn't raise mutating error. The error occurs when for some reason at some time more than 1 row is inserted and an author already has another job

Regards, Dima

http://seminihin.narod.ru

 

[pitz77]

Sem - your idea of creating a package variable sounds good. I've only been using Oracle for a few weeks. So, one question: how do I create a package variable for a select count(*) statement?

Thanks
Lisa

 

[sem]

create package tmp_vals
is
  cnt number;
end;



CREATE OR REPLACE TRIGGER CLIN_DTL_B_I_R BEFORE
INSERT ON CLIN_DETAILS FOR EACH ROW DECLARE
  rowCount int;
  strSQL varchar2(100);

BEGIN
--the sequence
If :new.CLIN_DTL_ID Is Not Null Then
    Raise_APPLICATION_ERROR(-20000, 'PK cannot be specified in query');
Else
    Select S_CLIN_DTL.nextVal into :new.CLIN_DTL_ID From dual;
End If;

SELECT Count(*) into tmp_vals.cnt 
FROM CLIN_DETAILS 
WHERE CLIN_NUM = :NEW.CLIN_NUM 
AND CTRT_ID = :NEW.CTRT_ID; 

END;

Then in AFTER INSERT:

  ...
  if tmp_vals.cnt > 0 then
    return;
  end if;

But again, the whole idea to query "mutating" table is wrong (at least your implementation) and may work only for single-row inserts. You should either read about resolving mutating error or redesign your application, as in most cases such needs are the result of weak design.

Regards, Dima

http://seminihin.narod.ru

 

[pitz77]

YAY!! Thanks Sem. Now that I know how packages can be so useful, my world as just gotten so much easier!

Thanks for your help.

If you have any really good online resources for Oracle/plsql, can you send them along to me?

Thanks again!!!