Home » SQL & PL/SQL » SQL & PL/SQL » insert data (oracle 11g)
insert data [message #626926] Wed, 05 November 2014 06:30 Go to next message
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 #626927 is a reply to message #626926] Wed, 05 November 2014 06:35 Go to previous messageGo to next message
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
Senior Member
INSERT INTO table_2
   SELECT *
     FROM table_1
    WHERE request_id = 123 
      AND ROWNUM = 1
Re: insert data [message #626930 is a reply to message #626927] Wed, 05 November 2014 07:05 Go to previous messageGo to next message
thelearner
Messages: 133
Registered: April 2013
Location: INDIA
Senior Member
but if the status of the 3rd row was updated to 'C' in the table_1 then the above query will fail.. right?
My requirement if the Status of the any row was changed to 'C' then only the request id and status code should be inserted into Table_2
Re: insert data [message #626937 is a reply to message #626930] Wed, 05 November 2014 07:52 Go to previous messageGo to next message
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 #626946 is a reply to message #626926] Wed, 05 November 2014 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> select * from table_1;
REQUEST_ID STATUS
---------- ----------
       123 P
       124 D

2 rows selected.

SQL> select * from table_2;

no rows selected

SQL> create or replace trigger trg after update on table_1
  2  for each row when (new.status = 'C')
  3  begin
  4    insert into table_2 values (:new.request_id, :new.status);
  5  end;
  6  /

Trigger created.

SQL> update table_1 set status='C' where request_id=123; 

1 row updated.

SQL> select * from table_1;
REQUEST_ID STATUS
---------- ----------
       123 C
       124 D

2 rows selected.

SQL> select * from table_2;
REQUEST_ID STATUS
---------- ----------
       123 C

1 row selected.

Re: insert data [message #626948 is a reply to message #626946] Wed, 05 November 2014 08:31 Go to previous messageGo to next message
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 #626949 is a reply to message #626948] Wed, 05 November 2014 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As this is some logging feature, I (try to) log only if the actual statement succeeded.
This is just the way I am thinking, I don't think it makes a real difference.

Re: insert data [message #626952 is a reply to message #626949] Wed, 05 November 2014 09:00 Go to previous messageGo to next message
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 #626958 is a reply to message #626952] Wed, 05 November 2014 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
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.


Are you sure you are answering to this topic and not another one?
What "commit" has to do here?

Re: insert data [message #626984 is a reply to message #626952] Thu, 06 November 2014 03:03 Go to previous messageGo to next message
sandeep_orafaq
Messages: 88
Registered: September 2014
Member
May be for this particular case it does not matter having before or after clause. But there is lot difference between them.
Re: insert data [message #626985 is a reply to message #626984] Thu, 06 November 2014 03:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

OK, what about explain it?

Re: insert data [message #626989 is a reply to message #626985] Thu, 06 November 2014 04:12 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: how to convert code of sql server into oracle
Next Topic: how do associate array working
Goto Forum:
  


Current Time: Fri Mar 29 09:00:48 CDT 2024