Re: Inserting old value into a column

Thanks for your suggestion Chris but this trigger needs to occur during an insert not during an update.

I tried it on an insert and received the "mutating table" error.

"Chris Leonard" <s_p_a_m_chris_at_hotmail.com> wrote in message news:<JWfgb.16$YV5.28370_at_news.uswest.net>...
> You can build your SQL so that it doesn't pass in the Response column if it
> is null. I think this is the best solution. However, it is also possible
> to use a trigger. It's a simple solution, and easy to implement, but it's
> not as efficient or elegant as making sure your app just doesn't update the
> Reponse column if it is null on the screen. Having said that, you don't
> need to run into the mutating table problem, just use the :OLD and :NEW
> aliases to set the value. For example:
>
> SQL> create table cc_rd_user_questions (
> 2 num number(25) default 1 not null,
> 3 company_id number(15) not null,
> 4 seq number(25) not null,
> 5 quest_id number(15) not null,
> 6 response varchar2(40),
> 7 create_date date default sysdate not null,
> 8 last_update date default sysdate not null);
>
> Table created.
>
> SQL> list
> 1 create or replace trigger cc_rd_user_response_fixup_trig
> 2 before update
> 3 on cc_rd_user_questions
> 4 for each row
> 5 begin
> 6 if :new.response is null then
> 7 :new.response := :old.response;
> 8 end if;
> 9* end;
> SQL> /
>
> Trigger created.
>
> SQL> insert into cc_rd_user_questions values (1, 2, 3, 4, 'Response One',
> sysdate, sysdate);
>
> 1 row created.
>
> SQL> select Response from cc_rd_user_questions where num = 1;
>
> RESPONSE
> ----------------------------------------
> Response One
>
> SQL> update cc_rd_user_questions set Response=NULL where NUM=1;
>
> 1 row updated.
>
> SQL> select Response from cc_rd_user_questions where num = 1;
>
> RESPONSE
> ----------------------------------------
> Response One
>
>
>
> --
> Cheers,
> Chris
>
> ___________________________________
>
> Chris Leonard, The Database Guy
> http://www.databaseguy.com
>
> Brainbench MVP for Oracle Admin
> http://www.brainbench.com
>
> MCSE, MCDBA, OCP, CIW
> ___________________________________
>
> "R. Santiago" <surfbot_at_rocketmail.com> wrote in message
> news:f28d4dcd.0310051246.610faf86_at_posting.google.com...
> > I have a table cc_rd_user_questions with the following columns:
> >
> > NUM NUMBER (25) DEFAULT 1 NOT NULL,
> > COMPANY_ID NUMBER (15) NOT NULL,
> > PROJ_ID NUMBER (15) NOT NULL,
> > SEQ NUMBER (25) NOT NULL,
> > QUEST_ID NUMBER (15) NOT NULL,
> > RESPONSE VARCHAR2 (40),
> > CREATE_DATE DATE NOT NULL,
> > LAST_UPDATE DATE NOT NULL
> >
> > This table contains the responses to questions found on a form. The
> > SEQ column is incremented each time a new row is inserted for a
> > specific company and specific project). If during an insert the new
> > RESPONSE column is null, what is the best method to populate the
> > RESPONSE column with the previous RESPONSE column value (the previous
> > row where COMPANY_ID, PROJ_ID and QUEST_ID are the same but SEQ = SEQ
> > - 1)? Should I use a trigger (I forsee a "mutating" table problem), a
> > temp table or SQL?
> >
> > Thanks in advance!
> >
> > - Rey
Received on Thu Oct 09 2003 - 05:35:55 CEST