0 Replies Latest reply: Nov 6, 2012 1:57 PM by 972880 RSS

    Trigger failing on Update on one PC

    972880
      Hi all,

      This is so bizzare that I have thrown my hands up. I can't think of anywhere else to go. There isn't anything on the net that remotely resembles this problem. Any help would be greatly appreciated.

      I have three workstations running 9.2 client. They appear to be set-up identically as far as I can tell.

      The tsnnames.ora files are identical and I've physically confirmed that they are all pointing the the same 9i database.

      The problem occurs when I run sqlplus from the PC and issue the following command

      'update inventory set dos_stat = 'V' where dos_serial_no = '012312';'

      The inventory table has a trigger as follows;

      **********************************
      CREATE OR REPLACE TRIGGER DMSDEV.INVENTORY_INV_AUDIT_BIUDR
      BEFORE INSERT OR UPDATE OR DELETE
      ON DMSDEV.INVENTORY REFERENCING OLD AS OLD NEW AS NEW
      FOR EACH ROW
      begin
      if inserting then
      insert into INVENTORY_AUDIT
      values(:NEW.DOS_SERIAL_NO,
      :NEW.TYPE_DOS,
      :NEW.TYPE_SERVICE,
      :NEW.SENS_INDEX_CHIP1,
      :NEW.SENS_INDEX_CHIP2,
      :NEW.DATE_CALIB,
      :NEW.REAS_CALIB,
      :NEW.READER_ID,
      :NEW.DOS_STAT,
      :NEW.DOS_STAT_DATE,
      :NEW.BATCH_NO,
      :NEW.POS_BATCH,
      :NEW.GROUP_NO,
      :NEW.CUM_EXPOS_TK,
      :NEW.CUM_EXPOS_TN,
      :NEW.READS_SINCE_PM,
      :NEW.EXP_TK_SINCE_PM,
      :NEW.EXP_TN_SINCE_PM,
      :NEW.DATE_LAST_PM,
      :NEW.TIMES_CALIB,
      :NEW.SCOPE_DOS,
      sys_context('userenv','os_user'),
      sysdate,
      :NEW.DOS_COMMENT);
      end if;

      if updating then
      insert into INVENTORY_AUDIT
      values(:NEW.DOS_SERIAL_NO,
      :NEW.TYPE_DOS,
      :NEW.TYPE_SERVICE,
      :NEW.SENS_INDEX_CHIP1,
      :NEW.SENS_INDEX_CHIP2,
      :NEW.DATE_CALIB,
      :NEW.REAS_CALIB,
      :NEW.READER_ID,
      :NEW.DOS_STAT,
      :NEW.DOS_STAT_DATE,
      :NEW.BATCH_NO,
      :NEW.POS_BATCH,
      :NEW.GROUP_NO,
      :NEW.CUM_EXPOS_TK,
      :NEW.CUM_EXPOS_TN,
      :NEW.READS_SINCE_PM,
      :NEW.EXP_TK_SINCE_PM,
      :NEW.EXP_TN_SINCE_PM,
      :NEW.DATE_LAST_PM,
      :NEW.TIMES_CALIB,
      :NEW.SCOPE_DOS,
      sys_context('userenv','os_user'),
      sysdate,
      :NEW.DOS_COMMENT);
      end if;

      if deleting then
      insert into INVENTORY_AUDIT
      values(:OLD.DOS_SERIAL_NO,
      :OLD.TYPE_DOS,
      :OLD.TYPE_SERVICE,
      :OLD.SENS_INDEX_CHIP1,
      :OLD.SENS_INDEX_CHIP2,
      :OLD.DATE_CALIB,
      :OLD.REAS_CALIB,
      :OLD.READER_ID,
      :OLD.DOS_STAT,
      :OLD.DOS_STAT_DATE,
      :OLD.BATCH_NO,
      :OLD.POS_BATCH,
      :OLD.GROUP_NO,
      :OLD.CUM_EXPOS_TK,
      :OLD.CUM_EXPOS_TN,
      :OLD.READS_SINCE_PM,
      :OLD.EXP_TK_SINCE_PM,
      :OLD.EXP_TN_SINCE_PM,
      :OLD.DATE_LAST_PM,
      :OLD.TIMES_CALIB,
      :OLD.SCOPE_DOS,
      sys_context('userenv','os_user'),
      sysdate,
      :OLD.DOS_COMMENT);
      end if;
      exception
      when others then
      raise_application_error (-20010, 'WARNING!!!... ERROR CAUSED BY THE INVENTORY_INV_AUDIT_BIUDR TRIGGER');
      end INVENTORY_INV_AUDIT_BIUDR;
      /
      **************************************************

      On two of the PC's I have no problem. The audit table is updated correctly and everything works as expected. On the other PC however, the trigger fails and I get the ''WARNING!!!... ERROR CAUSED BY THE INVENTORY_INV_AUDIT_BIUDR TRIGGER' message.

      I disable the trigger and the update works fine. i modify the trigger just to change a value in the inventory table (instead of inserting into another table) and it works fine. I try deleting a record and i get get the same error message as above.

      Am I losing my mind? how is this possible? What could be PC related that would cause such a simple trigger to fail?