Trigger to delete the inserted record.

[Zargo]

Hi all,

I want to create a trigger when i'm getting an insert record when recipient_name = 'TEST', i only want to delete this record if the doc_id exists more then one or equals to 1. I don't know if i have write this correctly, because i'm getting a ORA-04091: table is mutating, trigger/function may not see it, somebody idea's??

Here an example:

CREATE OR REPLACE TRIGGER UPDATE_FLOW
AFTER INSERT OR UPDATE
ON FLOW_CURRENT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

DECLARE VDOCID VARCHAR2(10)



BEGIN
IF :NEW.RECIPIENT_NAME = 'TEST' THEN
SELECT COUNTNEW.DOC_ID) INTO VDOCID FROM FLOW_CURRENT;
ELSEIF v_DOCID => 1 THEN
DELETE FROM FLOW_cURRENT WHERE DOC_ID = :NEW.DOC_ID AND :NEW.RECIPIENT_NAME = 'TEST' ;
END IF;


END IF;
END;
/

 

[sem]

Do you need to delete that old record and not insert a new one? Or just replace that existing record? In fact you may find dozens of threads related to mutating error as well as ways to resolve this issue. The common idea is to move all manipulations with triggered table to statement-level trigger.

Regards, Dima

http://seminihin.narod.ru

 

[Zargo]

Hi Sem,

Yes i want to delete that old record and not insert a new one. I'm getting a record into the table flow_current

doc_id|recipient_name|
1234|TEST

the table flow_current has the following records:
1234|USERA
4434|USERB

When i insert this record i'm getting the following contents:
1234|USERA
4434|USERB
1234|TEST

Now i have already a doc_id 1234 so i want to delete only the record 1234|TEST(deleting always the record with recipient_name = TEST). How can i delete this when there is already an existing doc_id?

As result i want to have the following contents, without Oracle errors:

1234|USERA
4434|USERB

Any idea?

 

[sem]

According to your description and example you just don't want to insert a record with RECIPIENT_NAME='TEST' when another record with the same doc_id exist, right? So you need not delete it, you must prohibit inserting it by rasing an error.
Your trigger obviously is not suitable for this task. Besides mutating error which is well known and easy resolvable (see above) your code contains a number of syntax and logical errors, so my suggestion is to learn basics first (command delimiters, control structures, resolving names etc.). The number of errors in such small piece of code is too high to correct them individually.

Regards, Dima

http://seminihin.narod.ru

 

[Zargo]

Dear Sem,

This is not possible in the application. I always can get that record. When i'm getting this i want to evaluate if the doc_id exists in this table, when yes THEN i want to delete it(AFTER INSERT TRIGGER?) I have compiled this in Oracle wihtout errors?
DECLARE VDOCID VARCHAR2(10);
BEGIN
IF :NEW.RECIPIENT_NAME = 'TEST' THEN
SELECT COUNTNEW.DOC_ID) INTO VDOCID FROM FLOW_CURRENT;
IF vDOCID > 1 THEN
DELETE FROM FLOW_cURRENT WHERE DOC_ID = :NEW.DOC_ID AND :NEW.RECIPIENT_NAME = 'TEST' ;
COMMIT;
END IF;


END IF;
END;

Could somebody help me to write this smarter?

Thanks in advance.

 

[sem]

What do you expect from that

SELECT COUNT(:NEW.DOC_ID) INTO VDOCID FROM FLOW_CURRENT;

statement to be returned?

Do you think it makes any sence to add

...AND :NEW.RECIPIENT_NAME = 'TEST' ;

to

DELETE FROM FLOW_cURRENT WHERE DOC_ID = :NEW.DOC_ID...

when it's known for sure that :NEW.RECIPIENT_NAME = 'TEST' within this IF construct.

Compiled code <> working code



Regards, Dima

http://seminihin.narod.ru

 

[Zargo]

Hi,

I'm not so a proffesional and hope that the proffesinals here can help me out of these issue. A big star who can give the golden answer....

Rgrds,

Zargo

 

[Zargo]

SantaMufasa,

Could you pls check my issue??

THANKS IN ADVANCE!!!!

 

[SantaMufasa]

Zargo,

"Santa's First Law of Quality Data: Let bad/unwanted data live the shortest possible life."

This means if there are data that you don't want in your table(s), then don't let it into your table(s) in the first place. Rather than building a trigger to DELETE rows you don't want, why INSERT the rows at all? Sem's suggestions (as usual) are right on the money.

Do help solve your specific issue, could you please post your code that INSERTs the row(s) that you are trying to get rid of?


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[Zargo]

Dear Zargo,

Thanks for your reply. I want to illustrate the situation again.
I'm getting the this record(also with other columns but this is a test so i have used two columns)

doc_id|recipient_name|
1234|TEST

the table flow_current has the following records:
1234|USERA
4434|USERB

When i insert this record i'm getting the following contents:
1234|USERA
4434|USERB
1234|TEST

Now i have already a doc_id 1234 so i want to delete only the record 1234|TEST(deleting always the record with recipient_name = TEST). How can i delete this when there is already an existing doc_id?

BUT NOW THE IMPORTANT STEP. WHEN I HAVE NOT THE SAME DOC_ID IN IT I MUST UPDATE ANOTHER TABLE. So summarized: A always can get this insert statement(insert into Flow_current (doc_id,recipient_name) values ('123zh','TEST'). The doc_id is always different. So when i get this i must check if the doc_id exists in this table IF YES then delete the current inserted record with recipient_name = 'TEST'. If the doc_id not exists in this table i must update another table and delete also this record. How to write this? I must get this insert statement so i do something in other tables. Hope it will now be clearly enough. THANKS A LOTTT!!!!

 

[Zargo]

By the way. I get this statement maybe 1 time a week. So i can have the following insert statement on the table:

insert statement(insert into Flow_current (doc_id,recipient_name) values ('123zh','usera').
insert statement(insert into Flow_current (doc_id,recipient_name) values ('123xx','userb').
insert statement(insert into Flow_current (doc_id,recipient_name) values ('123xx','TEST').

I must fire the trigger only when i'm getting the recipient_name = 'TEST'. I this situation i must delete that recipient_name = 'TEST' statement. SO the contents of the table =

DOC_ID|RECIPIENT_NAME
123zh|usera
123xx|userb

If i have an empty table and the next insert statements:
insert statement(insert into Flow_current (doc_id,recipient_name) values ('123zh','usera').
insert statement(insert into Flow_current (doc_id,recipient_name) values ('123xx','TEST').

I see that i'm getting a recipient_name = 'TEST' record and could see that the table only has

DOC_ID|RECIPIENT_NAME
123zh,usera

The doc_id what i want to insert (123xx) doesnt exist in the table so again i must delete this statement AND UPDATE another table to change a status....

 

[Turkbear]

Hi,
"What we have here is a failure to communicate...."

Is what you want to do:
prevent any record from getting into the target table
IF the DOC_ID already exists in the target AND the UserId = 'TEST'

OR

REMOVE any record from the target table
IF the Incoming DOC_ID already exists in the target AND its UserId = 'TEST' in the TARGET

Or Both?










To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Zargo]

The first one, i always want to prevent the record :recipient_name = 'TEST'. But i must check when this record is coming into the table if the doc_id exists i must delete this. If the doc_id doesn't exists yet (before the insert) then i must update another table and delete THIS record. Summarized i must always delete this record(or prevent to insert) but when the doc_id doesnt exist i must update another table, otherwise delete this record...

 

[Zargo]

I mean with"But i must check when this record is coming into the table if the doc_id exists i must delete this."

THIS = the incoming record with :Recipient_name = 'TEST', i do not delete any existing records in the table!

 

[Zargo]

Turkbear, SantaMufasa and other Guru's,

Anybody know how to realise this?

TIA

 

[SantaMufasa]

Zargo,

The problem that we are all facing in our attempts to assist you is that Oracle does not allow you to INSERT, UPDATE, or DELETE rows from a table from within a trigger that that is responding to an INSERT, UPDATE, or DELETE on that same table. This causes the error that you already know all too well, the "mutating table" error.

Therefore, my suggestion is to rethink your logic (perhaps using one of more of the ideas that appear earlier in this thread), or come up with a new "outside-the-box" idea that achieves your objective without attempting to INSERT, UPDATE, or DELETE some other row in the triggering table.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[Zargo]

SantaMufasa,

Thanks for your kind reply. I will think about it, when i have new idea's, i will come to tell it here.

Thanks a lot people!!

 

[sem]

Oracle doesn't allow manipulating data in triggered table from row-level triggers, though allows it from statement-level ones.

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Zargo,

Sem's suggestion sounds very promising. I would attempt building a statement-level trigger to do what you want (i.e., one that does not say "FOR EACH ROW") and resolve your problem in that fashion.

Let us know your findings. And if it works, be sure to give Sem a Purple Star for his great suggestion.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[Zargo]

Hm, ok. It sounds good. SantaMufasa could you give an example how to do this. At the moment i have put For each row on insert delete from flow_current where recipient_name = 'TEST', now it works fine, but when i got 1 record in this table with the same doc_id i want to update another table, this is the last thing what must happen.

Hope to hear from you soon.

Kind rgrds,

Zargo