Can this be done with a trigger?
in [Oracle Help]
|
From: artmerar on 6 Aug 2008 09:21 On Aug 6, 8:09 am, Aya the Vampire Slayer <ry...(a)gatech.rmv.this.part.edu> wrote: > artme...(a)yahoo.com wa: > > >On Aug 6, 3:23 am, "Shakespeare" <what...(a)xs4all.nl> wrote: > >> Do you look for inserting and updating as well in your trigger (since you > >> only posted part of your code)? Or else your triggers fires on an update and > >> performs an insert. > > >The trigger fires on insert or update. The actual trigger code is > >here. Do you see anything wrong with it? > >CREATE OR REPLACE TRIGGER customer_config_trg > > INSTEAD OF UPDATE OR INSERT ON customer_config_vw > > FOR EACH ROW > >BEGIN > > IF INSERTING THEN > > You also need to write your INSERT dml for the main table here before > inserting into the HIST table. An INSTEAD OF trigger doesn't do it > automatically like BEFORE/AFTER triggers do. > > INSERT INTO CUSTOMER_CONFIG > VALUES (...); > > > INSERT INTO customer_config_hist > > VALUES > >(:NEW.customer_id, :NEW.product_id, :NEW.format, :NEW.status, :NEW.source, > >SYSDATE); > > END IF; > > IF UPDATING THEN > > Same here, you also need to write the UPDATE dml for the main table here > before inserting an entry into the HIST table. > > UPDATE CUSTOMER_CONFIG > SET FOO = :new.FOO > (...) > where (...); > > > IF :NEW.format IS NOT NULL THEN > > INSERT INTO customer_config_hist > > VALUES > >(:NEW.customer_id, :NEW.product_id, :NEW.format, :OLD.status, :NEW.source, > >SYSDATE); > > ELSIF :NEW.status IS NOT NULL THEN > > INSERT INTO customer_config_hist > > VALUES > >(:NEW.customer_id, :NEW.product_id, :OLD.format, :NEW.status, :NEW.source, > >SYSDATE); > > END IF; > > END IF; > >END; > >/ > > -- > "Care must be exorcised when handring Opiticar System as it is apts to > be sticked by dusts and hand-fat." --Japanese Translators > > "Keep your fingers off the lens." --Elton Byington, English Translator This is VERY interesting. If you look above at my table definitions, the last column, SOURCE, is not in the original table that the view is based on, but is created as a column because it is needed in the second view. If I perform an insert using a NULL for the value of that column, as below, it works fine. But, if I put a value in for that column, it aborts: SQL> desc customer_config_vw Name Null? Type ----------------------------------------- -------- ---------------------------- CUSTOMER_ID NUMBER PRODUCT_ID VARCHAR2(10) FORMAT VARCHAR2(4) STATUS VARCHAR2(15) DATE_ADDED DATE DATE_MODIFIED DATE SOURCE VARCHAR2 insert into customer_config_vw values (1234,'PFP','Text','Active',SYSDATE,SYSDATE,NULL); 1 row created. insert into customer_config_vw values (67890,'PPM','HTML','Unsubscribe',SYSDATE,SYSDATE,'A'); insert into customer_config_vw * ERROR at line 1: ORA-03113: end-of-file on communication channel Any thoughts on this???
From: Shakespeare on 6 Aug 2008 10:18 <artmerar(a)yahoo.com> schreef in bericht news:d70b2a64-8d2c-49f4-b3d0-f1fa9389715a(a)56g2000hsm.googlegroups.com... > On Aug 6, 8:09 am, Aya the Vampire Slayer > <ry...(a)gatech.rmv.this.part.edu> wrote: >> artme...(a)yahoo.com wa: >> >> >On Aug 6, 3:23 am, "Shakespeare" <what...(a)xs4all.nl> wrote: >> >> Do you look for inserting and updating as well in your trigger (since >> >> you >> >> only posted part of your code)? Or else your triggers fires on an >> >> update and >> >> performs an insert. >> >> >The trigger fires on insert or update. The actual trigger code is >> >here. Do you see anything wrong with it? >> >CREATE OR REPLACE TRIGGER customer_config_trg >> > INSTEAD OF UPDATE OR INSERT ON customer_config_vw >> > FOR EACH ROW >> >BEGIN >> > IF INSERTING THEN >> >> You also need to write your INSERT dml for the main table here before >> inserting into the HIST table. An INSTEAD OF trigger doesn't do it >> automatically like BEFORE/AFTER triggers do. >> >> INSERT INTO CUSTOMER_CONFIG >> VALUES (...); >> >> > INSERT INTO customer_config_hist >> > VALUES >> >(:NEW.customer_id, :NEW.product_id, :NEW.format, :NEW.status, >> >:NEW.source, >> >SYSDATE); >> > END IF; >> > IF UPDATING THEN >> >> Same here, you also need to write the UPDATE dml for the main table here >> before inserting an entry into the HIST table. >> >> UPDATE CUSTOMER_CONFIG >> SET FOO = :new.FOO >> (...) >> where (...); >> >> > IF :NEW.format IS NOT NULL THEN >> > INSERT INTO customer_config_hist >> > VALUES >> >(:NEW.customer_id, :NEW.product_id, :NEW.format, :OLD.status, >> >:NEW.source, >> >SYSDATE); >> > ELSIF :NEW.status IS NOT NULL THEN >> > INSERT INTO customer_config_hist >> > VALUES >> >(:NEW.customer_id, :NEW.product_id, :OLD.format, :NEW.status, >> >:NEW.source, >> >SYSDATE); >> > END IF; >> > END IF; >> >END; >> >/ >> >> -- >> "Care must be exorcised when handring Opiticar System as it is apts to >> be sticked by dusts and hand-fat." --Japanese Translators >> >> "Keep your fingers off the lens." --Elton Byington, English Translator > > This is VERY interesting. If you look above at my table definitions, > the last column, SOURCE, is not in the original table that the view is > based on, but is created as a column because it is needed in the > second view. > > If I perform an insert using a NULL for the value of that column, as > below, it works fine. But, if I put a value in for that column, it > aborts: > > SQL> desc customer_config_vw > Name Null? Type > ----------------------------------------- -------- > ---------------------------- > CUSTOMER_ID NUMBER > PRODUCT_ID VARCHAR2(10) > FORMAT VARCHAR2(4) > STATUS VARCHAR2(15) > DATE_ADDED DATE > DATE_MODIFIED DATE > SOURCE VARCHAR2 > > insert into customer_config_vw > values (1234,'PFP','Text','Active',SYSDATE,SYSDATE,NULL); > > 1 row created. > > > insert into customer_config_vw > values (67890,'PPM','HTML','Unsubscribe',SYSDATE,SYSDATE,'A'); > insert into customer_config_vw > * > ERROR at line 1: > ORA-03113: end-of-file on communication channel > > Any thoughts on this??? > > Somehow something IS wrong here (I can't put my finger on it right now, but let's try): when you create the view, you define a default value for the SOURCE column. Then you perform an update on the view. Though the SOURCE column get's updated, it is not possible to retrieve its value, because of the fixed value in the view definition. As long as NEW.SOURCE is the same value as the default, there's no conflict. But when you update it with a new value, there is a conflict. I think your default is still NULL, right? You could try to replace your view definition with e.g. 'X' as a default value for SOURCE, and then test what happens a) when you update it to 'X' b) when you update it to 'A'. (It might work better because with a default of 'X' at least the type and length of the original and new column match). I have no opportunity to test it right now... Furthermore, you do not write your other values (other than SOURCE) to the original table, due to the INSTEAD OFF trigger. As Aya stated, no values will be written to the table ever. Shakespeare
From: artmerar on 6 Aug 2008 10:46 On Aug 6, 9:18 am, "Shakespeare" <what...(a)xs4all.nl> wrote: > <artme...(a)yahoo.com> schreef in berichtnews:d70b2a64-8d2c-49f4-b3d0-f1fa9389715a(a)56g2000hsm.googlegroups.com... > > > > > On Aug 6, 8:09 am, Aya the Vampire Slayer > > <ry...(a)gatech.rmv.this.part.edu> wrote: > >> artme...(a)yahoo.com wa: > > >> >On Aug 6, 3:23 am, "Shakespeare" <what...(a)xs4all.nl> wrote: > >> >> Do you look for inserting and updating as well in your trigger (since > >> >> you > >> >> only posted part of your code)? Or else your triggers fires on an > >> >> update and > >> >> performs an insert. > > >> >The trigger fires on insert or update. The actual trigger code is > >> >here. Do you see anything wrong with it? > >> >CREATE OR REPLACE TRIGGER customer_config_trg > >> > INSTEAD OF UPDATE OR INSERT ON customer_config_vw > >> > FOR EACH ROW > >> >BEGIN > >> > IF INSERTING THEN > > >> You also need to write your INSERT dml for the main table here before > >> inserting into the HIST table. An INSTEAD OF trigger doesn't do it > >> automatically like BEFORE/AFTER triggers do. > > >> INSERT INTO CUSTOMER_CONFIG > >> VALUES (...); > > >> > INSERT INTO customer_config_hist > >> > VALUES > >> >(:NEW.customer_id, :NEW.product_id, :NEW.format, :NEW.status, > >> >:NEW.source, > >> >SYSDATE); > >> > END IF; > >> > IF UPDATING THEN > > >> Same here, you also need to write the UPDATE dml for the main table here > >> before inserting an entry into the HIST table. > > >> UPDATE CUSTOMER_CONFIG > >> SET FOO = :new.FOO > >> (...) > >> where (...); > > >> > IF :NEW.format IS NOT NULL THEN > >> > INSERT INTO customer_config_hist > >> > VALUES > >> >(:NEW.customer_id, :NEW.product_id, :NEW.format, :OLD.status, > >> >:NEW.source, > >> >SYSDATE); > >> > ELSIF :NEW.status IS NOT NULL THEN > >> > INSERT INTO customer_config_hist > >> > VALUES > >> >(:NEW.customer_id, :NEW.product_id, :OLD.format, :NEW.status, > >> >:NEW.source, > >> >SYSDATE); > >> > END IF; > >> > END IF; > >> >END; > >> >/ > > >> -- > >> "Care must be exorcised when handring Opiticar System as it is apts to > >> be sticked by dusts and hand-fat." --Japanese Translators > > >> "Keep your fingers off the lens." --Elton Byington, English Translator > > > This is VERY interesting. If you look above at my table definitions, > > the last column, SOURCE, is not in the original table that the view is > > based on, but is created as a column because it is needed in the > > second view. > > > If I perform an insert using a NULL for the value of that column, as > > below, it works fine. But, if I put a value in for that column, it > > aborts: > > > SQL> desc customer_config_vw > > Name Null? Type > > ----------------------------------------- -------- > > ---------------------------- > > CUSTOMER_ID NUMBER > > PRODUCT_ID VARCHAR2(10) > > FORMAT VARCHAR2(4) > > STATUS VARCHAR2(15) > > DATE_ADDED DATE > > DATE_MODIFIED DATE > > SOURCE VARCHAR2 > > > insert into customer_config_vw > > values (1234,'PFP','Text','Active',SYSDATE,SYSDATE,NULL); > > > 1 row created. > > > insert into customer_config_vw > > values (67890,'PPM','HTML','Unsubscribe',SYSDATE,SYSDATE,'A'); > > insert into customer_config_vw > > * > > ERROR at line 1: > > ORA-03113: end-of-file on communication channel > > > Any thoughts on this??? > > Somehow something IS wrong here (I can't put my finger on it right now, but > let's try): > when you create the view, you define a default value for the SOURCE column. > Then you perform an update on the view. Though the SOURCE column get's > updated, it is not possible to retrieve its value, because of the fixed > value in the view definition. As long as NEW.SOURCE is the same value as the > default, there's no conflict. But when you update it with a new value, there > is a conflict. I think your default is still NULL, right? > You could try to replace your view definition with e.g. 'X' as a default > value for SOURCE, and then test what happens > a) when you update it to 'X' > b) when you update it to 'A'. > (It might work better because with a default of 'X' at least the type and > length of the original and new column match). I have no opportunity to test > it right now... > > Furthermore, you do not write your other values (other than SOURCE) to the > original table, due to the INSTEAD OFF trigger. As Aya stated, no values > will be written to the table ever. > > Shakespeare I changed the trigger. But I know it has something to do with adding that last column........see below. I also tried your suggestion with the column, see below. All I want is to create a view that contains the columns from the first table, along with one column in the second table. Then use the INSTEAD OF trigger to update both tables within the body of the trigger.......I'm sure that can be done. CREATE OR REPLACE VIEW CUSTOMER_CONFIG_VW (CUSTOMER_ID, PRODUCT_ID, FORMAT, STATUS, DATE_ADDED, DATE_MODIFIED, SOURCE) AS SELECT CUSTOMER_ID,PRODUCT_ID,FORMAT,STATUS,DATE_ADDED,DATE_MODIFIED, 'X' source FROM customer_config / SQL> insert into CUSTOMER_CONFIG_VW (source) values ('X'); * ERROR at line 1: ORA-01733: virtual column not allowed here CREATE OR REPLACE TRIGGER customer_config_trg INSTEAD OF UPDATE OR INSERT ON customer_email_config_vw FOR EACH ROW BEGIN IF INSERTING THEN INSERT INTO customer_config VALUES (:NEW.customer_id, :NEW.product_id, :NEW.format, :NEW.status, :NEW.source, SYSDATE); INSERT INTO customer_config_hist VALUES (:NEW.customer_id, :NEW.product_id, :NEW.format, :NEW.status, :NEW.source, SYSDATE); END IF; IF UPDATING THEN IF :NEW.format IS NOT NULL THEN UPDATE customer_config SET format = :NEW.format WHERE customer_id = :NEW.customer_id AND product_id = :NEW.product_id; INSERT INTO customer_config_hist VALUES (:NEW.customer_id, :NEW.product_id, :NEW.format, :OLD.status, :NEW.source, SYSDATE); ELSIF :NEW.status IS NOT NULL THEN UPDATE customer_config SET status = :NEW.status WHERE customer_id = :NEW.customer_id AND product_id = :NEW.product_id; INSERT INTO customer_config_hist VALUES (:NEW.customer_id, :NEW.product_id, :OLD.format, :NEW.status, :NEW.source, SYSDATE); END IF; END IF; END; /
From: Aya the Vampire Slayer on 6 Aug 2008 10:49 artmerar(a)yahoo.com wa: >This is VERY interesting. If you look above at my table definitions, >the last column, SOURCE, is not in the original table that the view is >based on, but is created as a column because it is needed in the >second view. >If I perform an insert using a NULL for the value of that column, as >below, it works fine. But, if I put a value in for that column, it >aborts: >SQL> desc customer_config_vw > Name Null? Type > ----------------------------------------- -------- >---------------------------- > CUSTOMER_ID NUMBER > PRODUCT_ID VARCHAR2(10) > FORMAT VARCHAR2(4) > STATUS VARCHAR2(15) > DATE_ADDED DATE > DATE_MODIFIED DATE > SOURCE VARCHAR2 >insert into customer_config_vw > values (1234,'PFP','Text','Active',SYSDATE,SYSDATE,NULL); >1 row created. >insert into customer_config_vw >values (67890,'PPM','HTML','Unsubscribe',SYSDATE,SYSDATE,'A'); >insert into customer_config_vw >* >ERROR at line 1: >ORA-03113: end-of-file on communication channel >Any thoughts on this??? Okay, now that I'm able to test this at work, apparently I find myself having mislead you. I was able to duplicate the error you got from the null column in the view that you mention above. I also checked through the pl/sql code I'd written over the past few years here at work and I can't find anywhere where that I used a null column to actually INSERT a passed-in value, even though I do use null columns in views on occasion for various front-end application quirks. So I apologize for suggesting something that doesn't quite work exactly as written. That said, I tried a few different things and managed to get it to work with something basically similar, but it does feel somewhat hackish. It should get the job done either way. This time I created my view like this (and kept the trigger the same). create or replace view &schema..VW_BLAHBLAH2 as select bb.*, 'apples' SOURCE from TB_BLAHBLAH2 bb ; If you pass in something that is the length of 'apples' or less as a varchar2, then it will insert it (otherwise you'll get a "value too large for column" error). INSERT INTO VW_BLAHBLAH2 ( ID, IDENT, NAME, INACTIVE, SOURCE ) VALUES ( sys_guid(), 'foo', 'foobar', 0, 'bananas'); * ERROR at line 3: ORA-01401: inserted value too large for column INSERT INTO VW_BLAHBLAH2 ( ID, IDENT, NAME, INACTIVE, SOURCE ) VALUES ( sys_guid(), 'foo', 'foobar', 0, 'banan'); 1 row created. -+- select * from TB_BLAHBLAH2; 01FE2D5D9C26458BB15DFB7F100738EE foo foobar 0 select * from TB_BLAHBLAH2_HIST; 083D80704D594785A529F7CD861FBA96 01FE2D5D9C26458BB15DFB7F100738EE foo foobar 0 banan -+- So um, yeah, in the view, just replace: 'apples' SOURCE ....with something that satisfies your length/char/byte requirement for the SOURCE field. If there is another field in your CUSTOMER_CONFIG that is the same size varchar2 as SOURCE in the HIST table, then just select that column twice and rename the second instance to SOURCE. Or otherwise, you can put a really long varchar2 constant for the SOURCE column, e.g.: ' ' SOURCE Again, sorry for giving you some bad information. I guess oracle interprets a null column as having no length, and gives a confusing and vague "catch all" error instead of something actually useful when you try to INSERT something into it, heh. -- "Care must be exorcised when handring Opiticar System as it is apts to be sticked by dusts and hand-fat." --Japanese Translators "Keep your fingers off the lens." --Elton Byington, English Translator
From: Shakespeare on 6 Aug 2008 11:34
<artmerar(a)yahoo.com> schreef in bericht news:90227953-a494-4cf6-9825-5c3857b76127(a)8g2000hse.googlegroups.com... > On Aug 6, 9:18 am, "Shakespeare" <what...(a)xs4all.nl> wrote: >> <artme...(a)yahoo.com> schreef in >> berichtnews:d70b2a64-8d2c-49f4-b3d0-f1fa9389715a(a)56g2000hsm.googlegroups.com... >> >> >> >> > On Aug 6, 8:09 am, Aya the Vampire Slayer >> > <ry...(a)gatech.rmv.this.part.edu> wrote: >> >> artme...(a)yahoo.com wa: >> >> >> >On Aug 6, 3:23 am, "Shakespeare" <what...(a)xs4all.nl> wrote: >> >> >> Do you look for inserting and updating as well in your trigger >> >> >> (since >> >> >> you >> >> >> only posted part of your code)? Or else your triggers fires on an >> >> >> update and >> >> >> performs an insert. >> >> >> >The trigger fires on insert or update. The actual trigger code is >> >> >here. Do you see anything wrong with it? >> >> >CREATE OR REPLACE TRIGGER customer_config_trg >> >> > INSTEAD OF UPDATE OR INSERT ON customer_config_vw >> >> > FOR EACH ROW >> >> >BEGIN >> >> > IF INSERTING THEN >> >> >> You also need to write your INSERT dml for the main table here before >> >> inserting into the HIST table. An INSTEAD OF trigger doesn't do it >> >> automatically like BEFORE/AFTER triggers do. >> >> >> INSERT INTO CUSTOMER_CONFIG >> >> VALUES (...); >> >> >> > INSERT INTO customer_config_hist >> >> > VALUES >> >> >(:NEW.customer_id, :NEW.product_id, :NEW.format, :NEW.status, >> >> >:NEW.source, >> >> >SYSDATE); >> >> > END IF; >> >> > IF UPDATING THEN >> >> >> Same here, you also need to write the UPDATE dml for the main table >> >> here >> >> before inserting an entry into the HIST table. >> >> >> UPDATE CUSTOMER_CONFIG >> >> SET FOO = :new.FOO >> >> (...) >> >> where (...); >> >> >> > IF :NEW.format IS NOT NULL THEN >> >> > INSERT INTO customer_config_hist >> >> > VALUES >> >> >(:NEW.customer_id, :NEW.product_id, :NEW.format, :OLD.status, >> >> >:NEW.source, >> >> >SYSDATE); >> >> > ELSIF :NEW.status IS NOT NULL THEN >> >> > INSERT INTO customer_config_hist >> >> > VALUES >> >> >(:NEW.customer_id, :NEW.product_id, :OLD.format, :NEW.status, >> >> >:NEW.source, >> >> >SYSDATE); >> >> > END IF; >> >> > END IF; >> >> >END; >> >> >/ >> >> >> -- >> >> "Care must be exorcised when handring Opiticar System as it is apts to >> >> be sticked by dusts and hand-fat." --Japanese Translators >> >> >> "Keep your fingers off the lens." --Elton Byington, English Translator >> >> > This is VERY interesting. If you look above at my table definitions, >> > the last column, SOURCE, is not in the original table that the view is >> > based on, but is created as a column because it is needed in the >> > second view. >> >> > If I perform an insert using a NULL for the value of that column, as >> > below, it works fine. But, if I put a value in for that column, it >> > aborts: >> >> > SQL> desc customer_config_vw >> > Name Null? Type >> > ----------------------------------------- -------- >> > ---------------------------- >> > CUSTOMER_ID NUMBER >> > PRODUCT_ID VARCHAR2(10) >> > FORMAT VARCHAR2(4) >> > STATUS VARCHAR2(15) >> > DATE_ADDED DATE >> > DATE_MODIFIED DATE >> > SOURCE VARCHAR2 >> >> > insert into customer_config_vw >> > values (1234,'PFP','Text','Active',SYSDATE,SYSDATE,NULL); >> >> > 1 row created. >> >> > insert into customer_config_vw >> > values (67890,'PPM','HTML','Unsubscribe',SYSDATE,SYSDATE,'A'); >> > insert into customer_config_vw >> > * >> > ERROR at line 1: >> > ORA-03113: end-of-file on communication channel >> >> > Any thoughts on this??? >> >> Somehow something IS wrong here (I can't put my finger on it right now, >> but >> let's try): >> when you create the view, you define a default value for the SOURCE >> column. >> Then you perform an update on the view. Though the SOURCE column get's >> updated, it is not possible to retrieve its value, because of the fixed >> value in the view definition. As long as NEW.SOURCE is the same value as >> the >> default, there's no conflict. But when you update it with a new value, >> there >> is a conflict. I think your default is still NULL, right? >> You could try to replace your view definition with e.g. 'X' as a default >> value for SOURCE, and then test what happens >> a) when you update it to 'X' >> b) when you update it to 'A'. >> (It might work better because with a default of 'X' at least the type and >> length of the original and new column match). I have no opportunity to >> test >> it right now... >> >> Furthermore, you do not write your other values (other than SOURCE) to >> the >> original table, due to the INSTEAD OFF trigger. As Aya stated, no values >> will be written to the table ever. >> >> Shakespeare > > I changed the trigger. But I know it has something to do with adding > that last column........see below. I also tried your suggestion with > the column, see below. All I want is to create a view that contains > the columns from the first table, along with one column in the second > table. Then use the INSTEAD OF trigger to update both tables within > the body of the trigger.......I'm sure that can be done. > > CREATE OR REPLACE VIEW CUSTOMER_CONFIG_VW > (CUSTOMER_ID, PRODUCT_ID, FORMAT, STATUS, DATE_ADDED, > DATE_MODIFIED, SOURCE) > AS > SELECT CUSTOMER_ID,PRODUCT_ID,FORMAT,STATUS,DATE_ADDED,DATE_MODIFIED, > 'X' source > FROM customer_config > / > > > SQL> insert into CUSTOMER_CONFIG_VW (source) values ('X'); > * > ERROR at line 1: > ORA-01733: virtual column not allowed here > > > > > CREATE OR REPLACE TRIGGER customer_config_trg > INSTEAD OF UPDATE OR INSERT ON customer_email_config_vw > FOR EACH ROW > > BEGIN > IF INSERTING THEN > INSERT INTO customer_config > VALUES > (:NEW.customer_id, :NEW.product_id, :NEW.format, :NEW.status, :NEW.source, > SYSDATE); > > INSERT INTO customer_config_hist > VALUES > (:NEW.customer_id, :NEW.product_id, :NEW.format, :NEW.status, :NEW.source, > SYSDATE); > END IF; > > IF UPDATING THEN > IF :NEW.format IS NOT NULL THEN > UPDATE customer_config > SET format = :NEW.format > WHERE customer_id = :NEW.customer_id AND product_id > = :NEW.product_id; > > INSERT INTO customer_config_hist > VALUES > (:NEW.customer_id, :NEW.product_id, :NEW.format, :OLD.status, :NEW.source, > SYSDATE); > ELSIF :NEW.status IS NOT NULL THEN > UPDATE customer_config > SET status = :NEW.status > WHERE customer_id = :NEW.customer_id AND product_id > = :NEW.product_id; > > INSERT INTO customer_config_hist > VALUES > (:NEW.customer_id, :NEW.product_id, :OLD.format, :NEW.status, :NEW.source, > SYSDATE); > END IF; > END IF; > END; > / Maybe it's a typo, but you created a trigger on customer_email_config_vw and inserted into CUSTOMER_CONFIG_VW . Without an insteadoff trigger, message ORA-1733 is correct. Shakespeare |