| PRAGMA AUTONOMOUS_TRANSACTION (merged) [message #386255] |
Fri, 13 February 2009 06:10  |
tamil
Messages: 22
Registered: April 2007
Location: chennai
|
Junior Member |
|
|
Two tables are created with the following structure
desc gra
Name Null? Type
--------------------- ------------------
SUPL_CODE CHAR(4)
GDN_CODE CHAR(4)
INDT_NO VARCHAR2(10)
IND_DATE DATE
GRA_NUM VARCHAR2(9)
GRA_DATE DATE
TOTALVALUE NUMBER(14,2)
TOTALMISSING NUMBER(14,2)
ENTERED CHAR(1)
MISMATCH CHAR(1)
desc grat
Name Null? Type
------------------- -------- ------------------
GRA_NUM VARCHAR2(9)
GRA_DATE DATE
IT_CODE VARCHAR2(
QTY_INDD NUMBER(4)
QTY_RECD_CASE NOT NULL NUMBER(4)
QTY_RECD_BOTT NUMBER(3)
QTY_MISG_CASE NUMBER(4)
QTY_MISG_BOTT NUMBER(5)
TOT_RECD_VALUE NUMBER(14,2)
TOT_MISS_VALUE NUMBER(14,2)
MISMATCH CHAR(1)
i have created a trigger which successfully complied.
CREATE OR REPLACE TRIGGER EXEC_TRIG BEFORE DELETE
ON GRA FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
GCNT pls_integer;
BEGIN
SELECT COUNT(INDT_NO) INTO GCNT FROM GRA WHERE INDT_NO=:OLD.INDT_NO;
IF GCNT = 1 THEN
UPDATE INDENT_PROX SET EXECUTED = 'N' WHERE IND_NO = :OLD.INDT_NO;
COMMIT;
END IF;
END;
Problem Note:
1)delete from gra where gra_date='11-feb-09' and gra_num='G02048933'
when i try to delete the data from the table gra the trigger worked fine.
2)delete from grat where gra_date='11-feb-09' and gra_num='G02048933'
delete from gra where gra_date='11-feb-09' and gra_num='G02048933'
when i try to delete the data from the table grat and then from gra table, a deadlock occurred.
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "EXEC_TRIG", line 7
ORA-04088: error during execution of trigger 'EXEC_TRIG'
Plz,let me know any mistake in the trigger and how can i rectify it.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: PRAGMA AUTONOMOUS_TRANSACTION (merged) [message #386287 is a reply to message #386275] |
Fri, 13 February 2009 07:39  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Good point - I misread the table name. I guess someone told them you could use an Autonomous Transaction to avoid mutating table errors.
It does mean that if you delete the last two rows in one DML, then the trigger won't fire.
|
|
|
|
| Re: PRAGMA AUTONOMOUS_TRANSACTION (merged) [message #386289 is a reply to message #386255] |
Fri, 13 February 2009 07:48 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| tamil wrote on Fri, 13 February 2009 07:10 |
delete from gra where gra_date='11-feb-09' and
delete from grat where gra_date='11-feb-09' and gra_num='G02048933'
|
These are not valid DELETE statements as you are comparing a DATE column to a character string. Just because it "may" work for you this one time, does not make it correct. Learn early from your mistakes as it easier before you become too rigid in your programming methods.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
