Home » SQL & PL/SQL » SQL & PL/SQL » Compare current and previous record (Oracle 10 g)
Compare current and previous record [message #670187] Fri, 15 June 2018 08:52 Go to next message
Danny Freeman
Messages: 21
Registered: December 2015
Location: UK
Junior Member
Want to compare current and previous record and log column changes. There are 15 columns. Really want to avoid comparing each column and need to write a generic logic. I am using triggers but I have to compare each column either by taking it in a variable or maybe a cursor. But really want to avoid such a complex process in a trigger.Please help

[Updated on: Fri, 15 June 2018 08:56]

Report message to a moderator

Re: Compare current and previous record [message #670188 is a reply to message #670187] Fri, 15 June 2018 09:00 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Danny Freeman wrote on Fri, 15 June 2018 06:52
Want to compare current and previous record and log column changes. There are 15 columns. Really want to avoid comparing each column and need to write a generic logic. I am using triggers but I have to compare each column either by taking it in a variable or maybe a cursor. But really want to avoid such a complex process in a trigger.Please help
I, for one, find that I am not capable to write SQL when I don't know any table name or column name combined with the fact that I have no idea what the desired or expected results should be.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Compare current and previous record [message #670189 is a reply to message #670188] Fri, 15 June 2018 09:05 Go to previous messageGo to next message
Danny Freeman
Messages: 21
Registered: December 2015
Location: UK
Junior Member
Hi. I have not written any code yet. I was just thinking about it. I think it can be done using triggers but I am not sure about it. So, I want more ideas about how to achieve this.
Re: Compare current and previous record [message #670190 is a reply to message #670189] Fri, 15 June 2018 09:08 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
>Want to compare current and previous record and log column changes.
rows in any table have NO inherent order.
rows in a table are like balls in a basket with NO inherent order.

Which ball in the basket is the next ball?

What problem are you REALLY trying to solve?
Re: Compare current and previous record [message #670191 is a reply to message #670189] Fri, 15 June 2018 09:18 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Is database auditing what you want?
Re: Compare current and previous record [message #670192 is a reply to message #670191] Fri, 15 June 2018 09:43 Go to previous messageGo to next message
Danny Freeman
Messages: 21
Registered: December 2015
Location: UK
Junior Member
Actually, I want to log each column change as old and new value. The thing is it's not an update query it's an insert so I have to check current n previous record. How to achieve this?
Re: Compare current and previous record [message #670193 is a reply to message #670192] Fri, 15 June 2018 09:48 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Danny Freeman wrote on Fri, 15 June 2018 07:43
Actually, I want to log each column change as old and new value. The thing is it's not an update query it's an insert so I have to check current n previous record. How to achieve this?
BTW, tables do NOT have "record"; only rows.

for INSERT there is no :OLD value

for INSERT which is "previous" row?
Re: Compare current and previous record [message #670194 is a reply to message #670191] Fri, 15 June 2018 09:55 Go to previous messageGo to next message
JPBoileau
Messages: 88
Registered: September 2017
Member
If you're using version 12c, look at flashback archive. https://blogs.oracle.com/oraclemagazine/a-fresh-look-at-auditing-row-changes. Scroll down to "A better way".

JP
Re: Compare current and previous record [message #670195 is a reply to message #670194] Fri, 15 June 2018 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
JPBoileau wrote on Fri, 15 June 2018 07:55
If you're using version 12c, look at flashback archive. https://blogs.oracle.com/oraclemagazine/a-fresh-look-at-auditing-row-changes. Scroll down to "A better way".

JP
OP clearly posted (Oracle 10 g)
Re: Compare current and previous record [message #670196 is a reply to message #670193] Fri, 15 June 2018 09:58 Go to previous messageGo to next message
Danny Freeman
Messages: 21
Registered: December 2015
Location: UK
Junior Member
That's what I'm saying. If it's an insert trigger and I want to compare previous record and current record s columns and if there's a change from previous row, I want to log that column's value.
Re: Compare current and previous record [message #670197 is a reply to message #670196] Fri, 15 June 2018 10:05 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Danny Freeman wrote on Fri, 15 June 2018 07:58
That's what I'm saying. If it's an insert trigger and I want to compare previous record and current record s columns and if there's a change from previous row, I want to log that column's value.
STOP using "record"!

IMO, above is daft since TRIGGER has no memory or knowledge of "previous" row.

post SQL & results that show previous row.
Re: Compare current and previous record [message #670198 is a reply to message #670187] Fri, 15 June 2018 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As others said it is not clear what you want.
Create a small table with, for instance, 3 columns, fill it (of course provide the statements so we can do the same thing) and show us what you want in the end.



Re: Compare current and previous record [message #670221 is a reply to message #670196] Tue, 19 June 2018 12:55 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
Danny Freeman wrote on Fri, 15 June 2018 10:58
That's what I'm saying. If it's an insert trigger and I want to compare previous record and current record s columns and if there's a change from previous row, I want to log that column's value.
What is the previous record, for the 4th time?
Re: Compare current and previous record [message #670267 is a reply to message #670221] Wed, 20 June 2018 11:50 Go to previous message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
are you possibly saying that if a column in a row is updated, you want to log the change?
Previous Topic: SQL: Substring folder name based on numeric value
Next Topic: Difference between two dates
Goto Forum:
  


Current Time: Fri Sep 18 06:08:17 CDT 2020