Re: Can this be done with a trigger?
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 6 Aug 2008 17:34:53 +0200
Message-ID: <4899c497$0$49881$e4fe514c@news.xs4all.nl>
>> <artme..._at_yahoo.com> schreef in
>> berichtnews: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
Date: Wed, 6 Aug 2008 17:34:53 +0200
Message-ID: <4899c497$0$49881$e4fe514c@news.xs4all.nl>
<artmerar_at_yahoo.com> schreef in bericht news:90227953-a494-4cf6-9825-5c3857b76127_at_8g2000hse.googlegroups.com...
> On Aug 6, 9:18 am, "Shakespeare" <what..._at_xs4all.nl> wrote:
>> <artme..._at_yahoo.com> schreef in
>> berichtnews: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
>
> 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 Received on Wed Aug 06 2008 - 10:34:53 CDT