Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ajayram.

Asked: March 30, 2017 - 2:47 pm UTC

Last updated: February 05, 2021 - 2:24 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hello Tom,

I had an Issue with one of my Trigger pasted below with error message, Can you let me know for any other alternatives.

CREATE TABLE TEST (ID NUMBER,NAME VARCHAR2(20),COMMENTS VARCHAR2(20));

INSERT INTO TEST(ID,NAME,COMMENTS) VALUES (1,'AJAY','INSERT');

CREATE OR REPLACE TRIGGER TEST_TRG
AFTER INSERT OR UPDATE OR DELETE ON TEST
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

IF UPDATING THEN
UPDATE TEST SET COMMENTS='UPDATING FROM TRIGGER' WHERE ID=:NEW.ID;
COMMIT;
END IF;

END;

BEGIN
UPDATE TEST SET NAME='AJAYRAM',COMMENTS='UPDATE' WHERE ID=1;
COMMIT;
END;

Error report -
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "BELLCAN1.TEST_TRG", line 6
ORA-04088: error during execution of trigger 'BELLCAN1.TEST_TRG'
ORA-06512: at line 2
00060. 00000 - "deadlock detected while waiting for resource"

and Connor said...

You can't update the table you are triggering (you'll get a mutating table error), and the resolution to this is *definitely* not an autonomous transaction.

It looks to me like you just want:

CREATE OR REPLACE TRIGGER TEST_TRG 
BEFORE UPDATE ON TEST
FOR EACH ROW
BEGIN
  :New.COMMENTS :='UPDATING FROM TRIGGER';
END;


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Can this please be Explained?

Rey, January 27, 2021 - 12:55 am UTC

How this solution works and how would this update the table?
Chris Saxon
January 27, 2021 - 8:42 am UTC

Whenever you run an update on the trigger's table, it will fire. When this happens, the trigger sets the value of the comments column to 'UPDATING FROM TRIGGER'

How is it Different?

Rey, January 27, 2021 - 9:12 pm UTC

Thanks for the clarification. A few more queries -

1) Why and how is this different from a simple update query?

2) How can I use your example using a "where" clause a filter for example, if in table test I have multiple rows and I only want to update the values for Name and Comments columns where ID in (3,5,7)?

Thanks a Ton in Advance. :)



Connor McDonald
January 28, 2021 - 9:45 am UTC

It sounds to me like you just want to have a simple update statement and not bother with the trigger at all.

I'm not really following what you're asking

How is it Different?

Rey, February 03, 2021 - 2:15 am UTC

Hi Connor, thanks for responding back.

My ask is just that if in a table I am having multiple rows with unique ID's and I want to update the column values for more than one row, i.e

update TEST set COMMENT = 'Odd Number ID.' where ID in (3,5,7,9);

So given the example above as a solution to the original query, how can I achieve this for multi-row update through a trigger to avoid ORA-00060 (deadlock) or even the ORA-04091 (Mutating Table) errors?

In other words, how to use the -
"where ID in (3,5,7,9);"

while using, in a trigger -
" :New.COMMENTS :='Odd Number ID.';"

Thanks & regards.
Connor McDonald
February 04, 2021 - 3:11 am UTC

But if you run:

update TEST set COMMENT = 'Odd Number ID.' where ID in (3,5,7,9);

then the trigger will fire 4 times, and each time, you'll set COMMENTS for each row that you touch

How is it Different?

Rey, February 05, 2021 - 6:00 am UTC

Hi Conner, thanks for the response. But, looks like I am not able to explain, what I am trying to ask? Let me try a different way -

The solution suggested to the original reviewer's query -

CREATE OR REPLACE TRIGGER TEST_TRG
BEFORE UPDATE ON TEST
FOR EACH ROW
BEGIN
:New.COMMENTS :='UPDATING FROM TRIGGER';
END;

What I want to know is, How can I achieve doing something in a similar fashion for multi-row updates based on a condition, like -

CREATE OR REPLACE TRIGGER TEST_TRG
BEFORE UPDATE ON TEST
FOR EACH ROW
BEGIN
:New.COMMENTS :='UPDATING FROM TRIGGER' where id in (3,5,7,9);
END;

Do i have to introduce,

if (ID in (3,5,7,9) ) then
:New.COMMENTS :='UPDATING FROM TRIGGER';
End if;

Or, is there a better / correct way to do so?

:)
Thanks & regards
Vikas.
Chris Saxon
February 05, 2021 - 2:24 pm UTC

It depends on what your update is and the logic behind setting the comment.

If you only change odd rows, use the update as Connor suggested:

update TEST set COMMENT = 'Odd Number ID.' where ID in (3,5,7,9);


If you update odd and even rows, but only want to set the comment for odd rows, then you need to check within the trigger:

CREATE OR REPLACE TRIGGER TEST_TRG
BEFORE UPDATE ON TEST
FOR EACH ROW
BEGIN
  if mod ( :new.id, 2 ) = 1 then
   :New.COMMENTS :='UPDATING FROM TRIGGER'; 
  end if;
END;


This does assume you're changing values in other columns for every row.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library