Re: Inserting old value into a column

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

-- 
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