Home » SQL & PL/SQL » SQL & PL/SQL » How can we get that Which row is updated or deleted in a table (Oracle)
How can we get that Which row is updated or deleted in a table [message #340593] Wed, 13 August 2008 06:24 Go to next message
rakesh_gupta
Messages: 2
Registered: August 2008
Junior Member
Hi Friends
I have one queries:
Scenario is like, There are 2 tables in which records can be modified(First delete and then insert)/Insert/Deleted.
If it is deleted then we get the information like
id=123
Flag=Delete
If it is Inserted then
Id=123
Flag=New

If it is modified (Note--> Here in modify means first records is deleted then inserted the new one.)
Id-123
Flag-MODIFY

And these 2 records have to inserted into new table in ABC.
I have done using trigger and in trigger i called a Procedure where it will get the information from the trigger that it is inserted or deleted or updated.. Now using this it will set the status and insert it into the table ABC.

If again data is modify then it will get the info of that data and update the change the status in table ABC.

I have question that IS it an efficient solution??
Is there are any other solution like to create some temp table as using that we can identify that new records is new or old one is modified or it is deleted.

Please help me in this...
Thanks
Rakesh



Re: How can we get that Which row is updated or deleted in a table [message #340662 is a reply to message #340593] Wed, 13 August 2008 13:35 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Unless you do the same thing in the logic, a trigger is the only way to do it.
Re: How can we get that Which row is updated or deleted in a table [message #340723 is a reply to message #340662] Thu, 14 August 2008 00:44 Go to previous messageGo to next message
rakesh_gupta
Messages: 2
Registered: August 2008
Junior Member
Hi Thanks for your quick response,
My question is if i will create another same temp table of the original one and store the previous records. We just compare the each field of a prticular records say id=123 of Original table to the each filed of the same records of id=123 of temp table.
And if we find any changes then we will say that it is modified.

If there is large amount of data say 10-20 Million then
Is this solution is efficient to the previous one..???
Please suggest me.
Thanks
Rakesh

Re: How can we get that Which row is updated or deleted in a table [message #340724 is a reply to message #340723] Thu, 14 August 2008 00:49 Go to previous message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
i think it will not improve performance.

For each row you will scan the temp table for the id and then
do the insert update and delete.

use merge statement in trigger if you don't wan't multiple entries in temp table.

Regards,
Rajat
Previous Topic: Using Bind Variable in IN Clause
Next Topic: Issues with MV refresh
Goto Forum:
  


Current Time: Tue Dec 06 04:28:44 CST 2016

Total time taken to generate the page: 0.22069 seconds