hej mark.
Actually I get a syntax error if I put colons in the new , old when it's in the
WHEN clause.
There are no synonyms both tables or in the same schema..
Additionally when I attempt the following trigger...
CREATE OR REPLACE TRIGGER COST5_FDB
BEFORE INSERT OR UPDATE ON "COST5"
FOR EACH ROW
WHEN (new.page=1 and new.calcno=1)
BEGIN
--transfer Off Price incl.percentage and VAT to off price on main
IF (:new.addamo19<>:old.addamo19) THEN
insert into test (recno,addamo19,mainkey) values (:new.recno,:new.addamo19,:new.mainkey);
update styles set moc038=:new.addamo19 where mainkey=:new.mainkey;
END IF;
END;
/
The insert line completes successfully and in the test table I can see the correct information in the mainkey and addamo19 fields which should be used in the update statement. However, the update statement does not update the information on the row as it should. It does not change the moc038 value.
If I change the update to another field i.e.
update styles set mod001=sysdate where mainkey=:new.mainkey;
update styles set mon001=1.1 where mainkey=:new.mainkey;
Then the update suceeds.
In fact any varchar2 columns in this trigger do not get updated where as
date and number columns do.
I have similar triggers on other tables which work fine this way.
There are no other triggers on either table...
What could be going on here?
The table looks like:
----------------------------------------- -------- ------------------
KEY1 VARCHAR2(20)
KEY2 VARCHAR2(30)
KEY3 VARCHAR2(20)
KEY4 VARCHAR2(30)
KEY5 VARCHAR2(10)
KEY6 VARCHAR2(10)
MOC001 VARCHAR2(30)
MOC002 VARCHAR2(30)
MOC003 VARCHAR2(30)
MOC004 VARCHAR2(30)
MOC005 VARCHAR2(30)
MOC006 VARCHAR2(30)
MOC007 VARCHAR2(30)
MOC008 VARCHAR2(30)
MOC009 VARCHAR2(30)
MOC010 VARCHAR2(30)
MOC011 VARCHAR2(25)
MOC012 VARCHAR2(60)
MOC013 VARCHAR2(60)
MOC014 VARCHAR2(60)
MOC015 VARCHAR2(60)
MOC016 VARCHAR2(60)
MOC017 VARCHAR2(60)
MOC018 VARCHAR2(60)
MOC019 VARCHAR2(60)
MOC020 VARCHAR2(60)
MOC021 VARCHAR2(60)
MOC022 VARCHAR2(60)
MOC023 VARCHAR2(60)
MOC024 VARCHAR2(60)
MOC025 VARCHAR2(60)
MOC026 VARCHAR2(60)
MOC027 VARCHAR2(60)
MOC028 VARCHAR2(60)
MOC029 VARCHAR2(60)
MOC030 VARCHAR2(60)
MOC031 VARCHAR2(60)
MOC032 VARCHAR2(60)
MOC033 VARCHAR2(60)
MOC034 VARCHAR2(60)
MOC035 VARCHAR2(60)
MOC036 VARCHAR2(60)
MOC037 VARCHAR2(60)
MOC038 VARCHAR2(60)
MOC039 VARCHAR2(60)
MOC040 VARCHAR2(60)
MOC041 VARCHAR2(60)
MOC042 VARCHAR2(60)
MOC043 VARCHAR2(60)
MOC044 VARCHAR2(60)
MOC045 VARCHAR2(60)
MOD043 DATE
MOD001 DATE
MOD002 DATE
MOD003 DATE
MON001 NUMBER(15,3)
MON002 NUMBER(15,3)
MON003 NUMBER(15,3)
MON004 NUMBER(15,3)
MOM001 VARCHAR2(4000)
MOM002 VARCHAR2(4000)
PICTMODA01 VARCHAR2(254)
PICTMODA02 VARCHAR2(254)
PICTMODA03 VARCHAR2(254)
PICTMODA04 VARCHAR2(254)
PICTMODA05 VARCHAR2(254)
PICTMODA06 VARCHAR2(254)
MAINKEY NOT NULL VARCHAR2(125)
CHANGED NUMBER(10)
RECNO NOT NULL NUMBER(20)
USER_NAME VARCHAR2(15)
LASTEDIT DATE
FIRSTDATE DATE
FIRSTTIME VARCHAR2(8)
FIRSTINIT VARCHAR2(20)
CHANGEDATE DATE
CHANGETIME VARCHAR2(8)
CHANGEINIT VARCHAR2(20)
CHANGENUM NUMBER(5)
COMPANY VARCHAR2(40)
STYLEPACK VARCHAR2(60)
ORDERNO VARCHAR2(10)
CHK1 VARCHAR2(1)
CHK2 VARCHAR2(1)
CHK3 VARCHAR2(1)
CHK4 VARCHAR2(1)
CHK5 VARCHAR2(1)
CHK6 VARCHAR2(1)
CHK7 VARCHAR2(1)
CHK8 VARCHAR2(1)
CHK9 VARCHAR2(1)
CHK10 VARCHAR2(1)
CHK11 VARCHAR2(1)
CHK12 VARCHAR2(1)
CHK13 VARCHAR2(1)
CHK14 VARCHAR2(1)
CHK15 VARCHAR2(1)
CHK16 VARCHAR2(1)
CHK17 VARCHAR2(1)
CHK18 VARCHAR2(1)
CHK19 VARCHAR2(1)
CHK20 VARCHAR2(1)
CHK1INIT VARCHAR2(20)
CHK2INIT VARCHAR2(20)
CHK3INIT VARCHAR2(20)
CHK4INIT VARCHAR2(20)
CHK5INIT VARCHAR2(20)
CHK6INIT VARCHAR2(20)
CHK7INIT VARCHAR2(20)
CHK8INIT VARCHAR2(20)
CHK9INIT VARCHAR2(20)
CHK10INIT VARCHAR2(20)
CHK11INIT VARCHAR2(20)
CHK12INIT VARCHAR2(20)
CHK13INIT VARCHAR2(20)
CHK14INIT VARCHAR2(20)
CHK15INIT VARCHAR2(20)
CHK16INIT VARCHAR2(20)
CHK17INIT VARCHAR2(20)
CHK18INIT VARCHAR2(20)
CHK19INIT VARCHAR2(20)
CHK20INIT VARCHAR2(20)
CHK1DATE DATE
CHK2DATE DATE
CHK3DATE DATE
CHK4DATE DATE
CHK5DATE DATE
CHK6DATE DATE
CHK7DATE DATE
CHK8DATE DATE
CHK9DATE DATE
CHK10DATE DATE
CHK11DATE DATE
CHK12DATE DATE
CHK13DATE DATE
CHK14DATE DATE
CHK15DATE DATE
CHK16DATE DATE
CHK17DATE DATE
CHK18DATE DATE
CHK19DATE DATE
CHK20DATE DATE
CHK1TIME VARCHAR2(8)
CHK2TIME VARCHAR2(8)
CHK3TIME VARCHAR2(8)
CHK4TIME VARCHAR2(8)
CHK5TIME VARCHAR2(8)
CHK6TIME VARCHAR2(8)
CHK7TIME VARCHAR2(8)
CHK8TIME VARCHAR2(8)
CHK9TIME VARCHAR2(8)
CHK10TIME VARCHAR2(8)
CHK11TIME VARCHAR2(8)
CHK12TIME VARCHAR2(8)
CHK13TIME VARCHAR2(8)
CHK14TIME VARCHAR2(8)
CHK15TIME VARCHAR2(8)
CHK16TIME VARCHAR2(8)
CHK17TIME VARCHAR2(8)
CHK18TIME VARCHAR2(8)
CHK19TIME VARCHAR2(8)
CHK20TIME VARCHAR2(8)
CHK1MEMO VARCHAR2(4000)
CHK2MEMO VARCHAR2(4000)
CHK3MEMO VARCHAR2(4000)
CHK4MEMO VARCHAR2(4000)
CHK5MEMO VARCHAR2(4000)
CHK6MEMO VARCHAR2(4000)
CHK7MEMO VARCHAR2(4000)
CHK8MEMO VARCHAR2(4000)
CHK9MEMO VARCHAR2(4000)
CHK10MEMO VARCHAR2(4000)
CHK11MEMO VARCHAR2(4000)
CHK12MEMO VARCHAR2(4000)
CHK13MEMO VARCHAR2(4000)
CHK14MEMO VARCHAR2(4000)
CHK15MEMO VARCHAR2(4000)
CHK16MEMO VARCHAR2(4000)
CHK17MEMO VARCHAR2(4000)
CHK18MEMO VARCHAR2(4000)
CHK19MEMO VARCHAR2(4000)
CHK20MEMO VARCHAR2(4000)
LASTEDIT1 VARCHAR2(15)
LASTEDIT2 VARCHAR2(15)
Thanks
