title: Case statement in Trigger in Oracle
body: <p>I have a problem in <strong>Trigger</strong> while running it. It compiles successfully with no errors, but while trying to run it, it throws an error <code>ORA-04091 table is mutating</code>. I am actually trying to insert a row in the table(<code>ATTDETAIL</code>) when a new record is added to the table(<code>ATTD_DETAILS</code>).
  And have to replace the <code>EADEMPID</code> with a new format.</p>
  
  <p><strong>Trigger</strong></p>
  
  <pre><code>CREATE OR REPLACE TRIGGER ATTENDANCE
  AFTER INSERT ON ATTD_DETAILS
  FOR EACH ROW
   DECLARE
      EMCODE VARCHAR2(50) := 'NA';
   BEGIN
  
        SELECT CASE
       WHEN EAD.EADEMPID LIKE 'A%' THEN
        '1V' || EADEMPID
       WHEN EAD.EADEMPID LIKE 'C%' THEN
        'LM' || EADEMPID
       WHEN EAD.EADEMPID LIKE 'S%' THEN
        'LM' || EADEMPID
        ELSE EAD.EADEMPID  
        END INTO EMCODE
      FROM ATTD_DETAILS EAD
      WHERE EAD.EADEMPID = :NEW.EADEMPID;
  
    IF (:NEW.EADREMARKS = 'Successful' OR :NEW.EADREMARKS = 'SUCCESSFUL') THEN
    INSERT INTO ATTDETAIL
    (CODE,
     CARDID,
     ATT_DATE,
     ATT_TIME,
     EMPID,
     PROCODE)
     VALUES
     (:NEW.EADSITECODE,
     :NEW.EADEMCARDID,
     :NEW.EADATT_DATE,
     :NEW.EADATT_TIME,
     EMCODE,
     '880');
    END IF;
    END;
  </code></pre>