1 Reply Latest reply: Nov 27, 2012 4:22 PM by rp0428 RSS

    Trigger inserts null in old values?

    976535
      Hi,

      Here is the code for Trigger.

      create or replace
      TRIGGER
      XXX.PI_DIM_EMPLOYEE_SDW_TGR AFTER INSERT OR DELETE OR UPDATE
      ON XXX.DIM_EMPLOYEE FOR EACH ROW
      DECLARE
      command VARCHAR2(12);
      BEGIN
      IF INSERTING THEN
      command := 'insert';
      ELSIF DELETING THEN
      command := 'delete';
      ELSIF UPDATING THEN
      command := 'update';
      IF NVL(:OLD.firstname,' ') = NVL(:NEW.firstname,' ') AND
      NVL(:OLD.lastname,' ') = NVL(:NEW.lastname,' ') AND
      NVL(:OLD.EMAIL,' ')      = NVL(:NEW.EMAIL,' ')

      THEN
      RETURN;
      END IF;
      END IF;
      insert into XXX.PI_DIM_EMPLOYEE_sdw values ( command, :old.EMPLOYEEID, :old.LASTNAME, :old.FIRSTNAME, :new.EMPLOYEEID, :new.LASTNAME, :new.FIRSTNAME,:new.EMAIL, XXX.vtPollConnector.nextval,'','N');
      END;

      *************************************************************************************************************************************
      Issue: I get "null" value in "command" column and all old values are "null" in target table XXX.PI_DIM_EMPLOYEE_sdw. However, new values are valid values. Not sure, what event is triggering these nulls. Command field shoul be one of insert/update/delete values. But, it is null. How is this possible?

      Plese help.
        • 1. Re: Trigger inserts null in old values?
          rp0428
          >
          Here is the code for Trigger.
          >
          This forum is, as the title says, for SQL Developer questions only and not for general SQL/PLSQL questions. So either explain how any of this is related to SQL Developer or mark the question ANSWERED and repost it in the SQL and PL/SQL forum.
          PL/SQL

          When you repost provide your 4 digit Oracle version, and add \
           on the line before and the line after the code to preserve formatting. See the FAQ for details. Triggers take action based on the type of DML being done and the values of the data being used. You didn't provide ANY information about the query being used, whether you are doing an INSERT, UPDATE or DELETE or the data values involved.