CODEWITHSUNDEEP
oracle-databaseplsqltriggers
Sam
Sam

Reputation: 513

Case statement in Trigger in Oracle

I have a problem in Trigger while running it. It compiles successfully with no errors, but while trying to run it, it throws an error ORA-04091 table is mutating. I am actually trying to insert a row in the table(ATTDETAIL) when a new record is added to the table(ATTD_DETAILS). And have to replace the EADEMPID with a new format.

Trigger

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;

Upvotes: 0

Views: 3631

Answers (1)

user330315
user330315

Reputation:

Assuming you want to provide a value to emcode based on the just inserted row, you need to check the columns from the :NEW record. You can't select from the trigger table (in a row level trigger).

What I think you want is: 

CREATE OR REPLACE TRIGGER ATTENDANCE
AFTER INSERT ON ATTD_DETAILS
FOR EACH ROW
 DECLARE
    EMCODE VARCHAR2(50) := 'NA';
 BEGIN

     emcode := CASE
       WHEN :new.EADEMPID LIKE 'A%' THEN '1V' || :new.EADEMPID
       WHEN :new.EADEMPID LIKE 'C%' THEN 'LM' || :new.EADEMPID
       WHEN :new.EADEMPID LIKE 'S%' THEN 'LM' || :new.EADEMPID
       ELSE :new.EADEMPID
      END;

  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;

Upvotes: 2

Related Questions