Re: Can I compare records in PLSQL?

From: Steve Baldwin <steven.baldwin_at_hancorp.com.au>
Date: Thu, 16 Sep 1999 15:59:45 +1000
Message-ID: <7rq10g$gqn$1@perki.connect.com.au>

Neil,

Not sure if this is of much use to you, but this is the approach we took to a similar problem ...

On each (appropriate) table we defined a column we called RowVersion CHAR(1) with a default value of '0'. We also defined a trigger as follows :

CREATE OR REPLACE TRIGGER {table}_rv01

    BEFORE UPDATE ON {table}

        FOR EACH ROW
BEGIN
    :NEW.rowversion :=

        CHR (MOD (ASCII (:OLD.rowversion) + 1
             - ASCII ('0'), 50) + ASCII ('0')) ;

END ; This ensures for any update on that table the value of the RowVersion column will increase by 1. It is then a very simple matter of comparing the RowVersion before calling your external procedure with the 'current' version. If they are different then it means an update occurred.

Hope this helps

Steve Baldwin

Neil W. Garside <nwgarside_at_my-deja.com> wrote in message news:7rpv20$mcv$1_at_nnrp1.deja.com...
> Does anyone know how I can compare 2 records in a table? In my Forms
> 6.0 application I am calling a external application which may (or may
> not) alter a record's data. I need to be able to compare the record's
> data from before and after the call to the external application. I've
> tried the following PL/SQL:
>
> DECLARE
> CURSOR curBefore IS
> SELECT * FROM MyTable
> WHERE id = 10;
> curBeforeRec curBefore%ROWTYPE;
>
> CURSOR curAfter IS
> SELECT * FROM MyTable
> WHERE id = 10;
> curAfterRec curAfter%ROWTYPE;
>
> BEGIN
> OPEN curBefore;
> FETCH curBefore INTO curBeforeRec;
> CLOSE curBefore;
>
> --Call external application...might amend this record's data!
>
> OPEN curAfter;
> FETCH curAfter INTO curAfterRec;
> CLOSE curAfter;
>
> IF curBeforeRec = curAfterRec THEN
> MESSAGE('They are the same!');
> ELSE
> MESSAGE('They are different!');
> END IF;
> END;
>
> However, this doesn't compile - I get the message "wrong number or
> types of arguments in call to '='"for the "IF curBeforeRec = ..." line.
>
> I could compare the record column by column but the table in question
> has at least 200 columns and is often having columns added or removed
> which would make a column-by-column comparison difficult.
>
> Anyone any ideas?
>
> Neil W. Garside (Brisbane, Australia)
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Thu Sep 16 1999 - 00:59:45 CDT