New/Old variable in SQL update statement

Question

How can i update the old row value. I have used the :old variable but it does not work for me. Herein my code, if PID already exists in project table i.e. (varProjectExists = 1) then i update the payment term. Now, if payment term is null , i want to update with previous paymnent term value else new payment term that is flown

CREATE OR REPLACE TRIGGER TRIG_PROJECT_INSERT AFTER
  INSERT ON TEST_SYN_EAI_PROJECT_IN FOR EACH row DECLARE varError_Msg NVARCHAR2(100);
  varSucceeded NVARCHAR2(1);
  varActive_YN NVARCHAR2(50);
  varProject_Id    INT;
  varPid           INT ;
  varPay_Term      VARCHAR2(200);
  varPay_Term1     VARCHAR2(200);
  varError_id      INT;
  varCurr_activeyn INT;
  varProjectExists NUMBER;
  BEGIN
    varError_Msg            := 'No error';
    varSucceeded            := 'Y';
    varError_id             := 0;
    varProjectExists        := 0;
    varPID                  := :new.pid;
    varPay_Term             := :new.ATTRIBUTE1;
    varPay_Term1            := :old.ATTRIBUTE1;
    varActive_YN            := :new.active_yn;
    varProject_ID           := :new.project_id;
    IF (NVL(varProject_Id,0) = 0 ) THEN
      varError_Msg          := 'project ID can not be null';
      varSucceeded          := 'N';
      varError_id           := 1;
    END IF;
    SELECT
      CASE
        WHEN (UPPER(varActive_YN) = 'ACTIVE'
        OR UPPER(varActive_YN)    = 'Y')
        THEN 1
        WHEN (UPPER(varActive_YN) = 'INACTIVE'
        OR UPPER(varActive_YN)    = 'N')
        THEN 0
        ELSE varcurr_activeyn
      END
    INTO varActive_YN
    FROM Dual;
    SELECT COUNT(1)
    INTO varProjectExists
    FROM project
    WHERE ProjectUniversalID = varProject_ID;
    IF (varProjectExists     = 1) THEN
      UPDATE project
      SET PID       = varPID,
        PAYMENTTERM =
        CASE
          WHEN varPay_Term = 'NULL'
          THEN varPay_Term1
          WHEN varPay_Term IS NULL
          THEN varPay_Term1
          ELSE varPay_Term
        END
     ELSE .....

Answer 1

The OLD pseudo-record refers to the pre-update state of the current row, and so is only meaningful during an update or delete operation. If you're inserting a new record there is no 'old' state to refer to.

From your comment you want to use the previous payment term value that already exists in the PROJECT table. That would't be available in OLD anyway as it isn't the table the trigger is against. If that is the case then you need to retrieve it at the same time you check that it exists. Something like:

CREATE OR REPLACE TRIGGER TRIG_PROJECT_INSERT
AFTER INSERT ON TEST_SYN_EAI_PROJECT_IN
FOR EACH ROW
DECLARE
  ...
BEGIN
  ...
  varPay_Term             := :new.ATTRIBUTE1;
  -- varPay_Term1            := :old.ATTRIBUTE1; -- not valid
  varActive_YN            := :new.active_yn;
  ...

  -- this doesn't need to select from dual, you can assign directly
  varCurr_activeyn := CASE
    WHEN (UPPER(varActive_YN) = 'ACTIVE'
      OR UPPER(varActive_YN)  = 'Y')
      THEN 1
    WHEN (UPPER(varActive_YN) = 'INACTIVE'
      OR UPPER(varActive_YN)  = 'N')
      THEN 0
    ELSE varCurr_activeyn
  END CASE;

  -- get the current term as you check if a record exists
  SELECT COUNT(1), MAX(PAYMENTTERM)
  INTO varProjectExists, varPay_Term1
  FROM project
  WHERE ProjectUniversalID = varProject_ID;

  IF (varProjectExists = 1) THEN
    UPDATE project
    SET PID       = varPID,
      PAYMENTTERM =
      CASE
        WHEN varPay_Term = 'NULL'
        THEN varPay_Term1
        WHEN varPay_Term IS NULL
        THEN varPay_Term1
        ELSE varPay_Term
      END
...

The MAX() is needed because you're already using an aggregate COUNT(), to avoid a no-data-found error if the ID doesn't exist. Assuming that ID is unique, it doesn't matter if you use MAX or MIN, the result is the same. (If it isn't unique then you'd have to decide which term value to use, so it's probably a safe assumption).