PL/SQL Why I get DEADLOCK on trigger action?

Question

I have the following trigger

    CREATE OR REPLACE TRIGGER LAST_EDIT 
    BEFORE UPDATE ON MESSAGES
    FOR EACH ROW
    DECLARE
    pragma autonomous_transaction;
    BEGIN
     if :NEW.TEXT <> :OLD.TEXT THEN
      UPDATE MESSAGES set MESSAGES.LAST_EDITED=(select USERS.EMAIL from USERS inner join LAST_EDITED_TABLE on users.ID=LAST_EDITED_TABLE.USER_ID  where 
      LAST_EDITED_TABLE.MESSAGE_ID=(select MESSAGE_ID from LAST_EDITED_TABLE where DATE_MESSAGE=(select max(DATE_MESSAGE) from LAST_EDITED_TABLE )));
    ENDIF;
COMMIT;
END;

And I get the following error

ORA-06512: at "DIP.MESSAGEPACKAGE", line 35
00060. 00000 -  "deadlock detected while waiting for resource"
*Cause:    Transactions deadlocked one another while waiting for resources.
*Action:   Look at the trace file to see the transactions and resources
           involved. Retry if necessary

I'm trying to update the row which says who make the last update ( one row from same table) . Can anyone please help me?

Answer 1

Isn't it what you really need?

CREATE OR REPLACE TRIGGER LAST_EDIT 
BEFORE UPDATE ON MESSAGES
FOR EACH ROW
DECLARE
  email USERS.EMAIL%type;
BEGIN     
 if :NEW.TEXT <> :OLD.TEXT THEN
  select USERS.EMAIL into email 
    from USERS inner join LAST_EDITED_TABLE on users.ID=LAST_EDITED_TABLE.USER_ID  
     where LAST_EDITED_TABLE.MESSAGE_ID = (select MESSAGE_ID from LAST_EDITED_TABLE where DATE_MESSAGE = (select max(DATE_MESSAGE) from LAST_EDITED_TABLE ));
  :NEW.LAST_EDITED = email;
END IF;
COMMIT;
END;
/

Also logic in query that looks for email is too complicated. For sure you can simplify it.