insert data [message #626926] |
Wed, 05 November 2014 06:30 |
|
thelearner
Messages: 133 Registered: April 2013 Location: INDIA
|
Senior Member |
|
|
Hi,
My requrirement is
1) I have two tables Table_1, Table_2
1)In table 1
I have 3 columns request_id,status
in request_id(data will dynamically come)
status(either 'E','P','D','W','C')
Status column will change automatically to 'P' to 'W' or 'C'
In table 2
I have to insert data when status is in 'C' only.
create table Table_1(request_id number, status varchar2(10));
create table Table_2(request_id number, status varchar2(10));
insert into table_1 values(123,'P');
insert into table_1 values (124,'D');
commit;
select * from table_1;
request_id status
123 P
124 D
select * from table_2;
No records found.
when I updated the table_1 then
update table_1 set status='C' where request_id=123;
commit;
select * from table_1;
request_id status
123 C
124 D
select * from table_2;
request_id status
123 C
|
|
|
|
|
Re: insert data [message #626937 is a reply to message #626930] |
Wed, 05 November 2014 07:52 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
@mist598, how does it answers OP's question? Can you explain your logic of the insert statement?
@OP, you can use a before update trigger on table_1, such that whenever the status is updated to 'C', insert the required values into table_2.
|
|
|
|
Re: insert data [message #626948 is a reply to message #626946] |
Wed, 05 November 2014 08:31 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Michel, precisely what I mentioned. But there is a slight difference, I suggested before update and you demonstrated after update. I am just thinking(before a coffee break) if it really makes any difference in OP's case.
|
|
|
|
Re: insert data [message #626952 is a reply to message #626949] |
Wed, 05 November 2014 09:00 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Thanks for the reply. We are on same platform.
However, just to make sure that OP is not confused with before and after trigger, there is no "on commit" trigger in Oracle. So, it really doesn't matter having before or after clause.
|
|
|
|
|
|
Re: insert data [message #626989 is a reply to message #626985] |
Thu, 06 November 2014 04:12 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Actually there's one good reason for having that code in an after trigger - you can have multiple before triggers.
Say you have two before row triggers, one modifies status and the other does the insert. The result would depend on which trigger runs first, and until later versions of oracle there's no way to guarantee that.
Now obviously good coding practice would demand a single before row trigger to avoid such confusion, but really if you want code that does stuff based on what columns get set to - after is the way to go.
Also having it in after means that if you hit a constraint error then it's one less thing to roll back.
|
|
|
Re: insert data [message #626992 is a reply to message #626985] |
Thu, 06 November 2014 05:08 |
|
sandeep_orafaq
Messages: 88 Registered: September 2014
|
Member |
|
|
Functionally the situation might be different when we use before or after clause.
Before clause is mainly used when we need to perform validations on the values to be inserted or updated, also it is helpful to derive other attributes(e.g. last update user, lastupdate timestamp etc) when they are not supplied in insert/update statement.
After clause is mainly used when we need to to perform some log activity based on insert/update statement as you explained in your earlier post.
[Updated on: Thu, 06 November 2014 05:08] Report message to a moderator
|
|
|
Re: insert data [message #627002 is a reply to message #626992] |
Thu, 06 November 2014 07:54 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
@Michel, I got mixed up with the other topic about trigger. Sorry for the confusion.
My point in this case is that, with the plain insert, after/before won't make any difference. And, I agree to the point you made. After update trigger would be more appropriate to avoid any side effects.
|
|
|