This discussion is archived
0 Replies Latest reply: Nov 6, 2012 11:57 AM by 972880 RSS

Trigger failing on Update on one PC

972880 Newbie
Currently Being Moderated
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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points