After Update Trigger Delete Query not Working

Subhashkumar Yadav

9y1.4kOracle

Unable to delete record in trigger.

Can we write Delete statement in After Update Trigger in Oracale?

For Example : -

My Trigger :

Create or replace

TRIGGER TranxMaster_after_update

AFTER UPDATE ON Transaction

FOR EACH ROW

DECLARE vcrTranxID VARCHAR2(100);

BEGIN

INSERT INTO TRANSACTIONNew

( ID,

MOBILENO,

TrnTYPE,

AMOUNT,

TranxID)

VALUES

( :new.ID,

:new.MOBILENO,

:new.TrnTYPE,

:new.AMOUNT,

:new.TranxID)

SELECT TranxID INTO vcrTranxID FROM TransactionNew WHERE ID=:new.ID;

IF vcrTranxID IS NOT NULL THEN

DELETE FROM Transaction WHERE ID = :new.ID;

End IF;

END;

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rupali Shinde9y ago
HI,

Need to find out other solution to problem. if i get the solution, i will reply you back.
0
Subhashkumar Yadav9y ago
Based on your suggestion we have made the change in the trigger code. we are getting below error.

"Exception in Tranx:Oracle.DataAccess.Client.OracleException ORA-04091: table TRANSACTION is mutating, trigger/function may not see it
ORA-06512: at TRANX_AFTER_UPDATE", line 63
ORA-04088: error during execution of trigger 'TRANX'
ORA-06512: at line 1 at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at DBConnection.ExecuteQuery(OracleCommand nQuery)

+1
Rupali Shinde9y ago
Hi,

ID column need to have same value while inserting in another table.
Because while deleting records from old table, you want to delete same record which is just inserted into another table.
try to find out which value you get via old.id. if old.id gives you correct id then deletion will work correctly.

INSERT INTO TRANSACTIONNew (ID,MOBILENO,TrnTYPE,AMOUNT,TranxID)VALUES( :old.ID,:new.MOBILENO,:new.TrnTYPE,:new.AMOUNT,:new.TranxID)

Please refer below link
http://www.tech-recipes.com/rx/19839/oracle-using-the-after-insert-and-after-update-triggers/

if it help you out, please accept the ans
+1
Subhashkumar Yadav9y ago
As per my trigger, We are getting updating table ID inserting in new table(TransactionNew) but why it is not getting same ID while deletion time. Can you clarify ?
+1
Rupali Shinde9y ago
Hi,
As per my understanding regarding trigger or any database operation,
if you update any record, id column value remain same as it is primary key of record.
you need to check while updating any record, what is id column value and same column id getting passed for delete statement.
if id column mismatch then delete wont work.

if it help you out, please accept the ans
+1