Re: Can this be done with a trigger?
Date: Wed, 6 Aug 2008 16:18:49 +0200
Message-ID: <4899b2c3$0$49845$e4fe514c@news.xs4all.nl>
<artmerar_at_yahoo.com> schreef in bericht
news:d70b2a64-8d2c-49f4-b3d0-f1fa9389715a_at_56g2000hsm.googlegroups.com...
> On Aug 6, 8:09 am, Aya the Vampire Slayer
> <ry..._at_gatech.rmv.this.part.edu> wrote:
>> artme..._at_yahoo.com wa: >> >> >On Aug 6, 3:23 am, "Shakespeare" <what..._at_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 Received on Wed Aug 06 2008 - 09:18:49 CDT