SQL & PL/SQL

with table1 as (
select '1' no,'1' id, 'N' flag,'' result from dual union all
select '2' no,'1' id, 'N' flag,'B' result from dual union all
select '3' no,'1' id, '' flag,'B' result from dual union all
select '4' no,'2' id, 'N' flag,'B' result from dual union all
select '5' no,'2' id, 'N' flag,'B' result from dual

)
select * from table1


I need to write a trigger with the condition that if Flag is set to 'Y', then all the values for the field 'result' for that particular ID should set a 'A'.

For the above table if I run the below query

update table1
set flag= 'Y' where no =1

The trigger result should be

with table1 as (
select '1' no,'1' id, 'Y' flag,'A' result from dual union all
select '2' no,'1' id, 'N' flag,'A' result from dual union all
select '3' no,'1' id, '' flag,'A' result from dual union all
select '4' no,'2' id, 'N' flag,'B' result from dual union all
select '5' no,'2' id, 'N' flag,'B' result from dual

)
select * from table1

I wrote the trigger as below...

CREATE OR REPLACE TRIGGER test
BEFORE UPDATE
ON table1 for each row
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN


if :new.flag = 'Y' then
update table1
set result = 'A'
where id = :new.id ;
commit;
end if;
end;

but giving follwing error.

ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "test"
ORA-04088: error during execution of trigger 'test'

Please help to correct my trigger.