Home » SQL & PL/SQL » SQL & PL/SQL » PRAGMA AUTONOMOUS_TRANSACTION (merged)
PRAGMA AUTONOMOUS_TRANSACTION (merged) [message #386255] Fri, 13 February 2009 06:10 Go to next message
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(Cool
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 #386259 is a reply to message #386255] Fri, 13 February 2009 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a Test case: create table and insert statements. Use SQL*Plus and copy and paste your session.

Have you any trigger on the other table?

Regards
Michel
Re: PRAGMA AUTONOMOUS_TRANSACTION (merged) [message #386261 is a reply to message #386255] Fri, 13 February 2009 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
NEVER use PRAGMA AUTONOMOUS_TRANSACTION in a trigger but for a logging or the like purpose.

If Oracle does not allow you to create/execute a trigger there is a good reason, don't try to workaround it with silly pragma statement.

It is normal you have this error.

Regards
Michel
Re: PRAGMA AUTONOMOUS_TRANSACTION (merged) [message #386266 is a reply to message #386255] Fri, 13 February 2009 06:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't think you want (or need) an autonomous transaction for what you're doing.

The way you've got it coded, if the Delete fails, or is rolled back, the update on Indent_Prox will stay.

Re: PRAGMA AUTONOMOUS_TRANSACTION (merged) [message #386275 is a reply to message #386255] Fri, 13 February 2009 06:53 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think the autonomous_transaction is there to allow the SELECT COUNT to work (I agree it shouldn't be used).

Think you've got a dodgy schema design.

What's INDENT_PROX.EXECUTED supposed to represent?
Re: PRAGMA AUTONOMOUS_TRANSACTION (merged) [message #386287 is a reply to message #386275] Fri, 13 February 2009 07:39 Go to previous messageGo to next message
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 Go to previous message
joy_division
Messages: 4642
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.
Previous Topic: Web Page and PL/SQL
Next Topic: Performance issue while runningthe query
Goto Forum:
  


Current Time: Wed Dec 07 14:19:40 CST 2016

Total time taken to generate the page: 0.05849 seconds