Inserting old value into a column

[R. Santiago]

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

[Chris Leonard]

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" <su*****@rocketmail.com> wrote in message
news:f2**************************@posting.google.c om...

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

[bung ho]

su*****@rocketmail.com (R. Santiago) wrote in message news:<f2**************************@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

is there a reason why you don't want to just do something like

insert ... select ... decode(:new_response, null, (select response
from cc_rd_user_questions where <what you said>), :new_response) from
dual ?

however, what happens if that previous SEQ - 1 answer gets updated in
the future? do you need to look at the following answer and perhaps
change that as well? and could that cause a chain of new updates?
maybe it would be easier if a null RESPONSE was understood to mean
"see above" at query time, rather than filling it in at insert time.

[R. Santiago]

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_***********@hotmail.com> wrote in message news:<JW****************@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" <su*****@rocketmail.com> wrote in message
news:f2**************************@posting.google.c om...

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

[Mauro]

Hi.

I tried with this trigger and it works:

create or replace trigger T before insert on cc_rd_user_questions for each row
begin
if :new.response is null then
select response into :new.response from cc_rd_user_questions
where COMPANY_ID = :new.company_id
and PROJ_ID = :new.proj_id and QUEST_ID = :new.quest_id
and SEQ = :new.seq - 1;
end if;
exception
when no_data_found then null;
end;
/

Bye.

su*****@rocketmail.com (R. Santiago) wrote in message news:<f2**************************@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