Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Need help with trigger to compare columns dynamically

Re: Need help with trigger to compare columns dynamically

From: Mike Proctor <mp_at_mg-tc.demon.co.uk>
Date: 1997/01/30
Message-ID: <eztmlfAKTJ8yEwhR@mg-tc.demon.co.uk>#1/1

In article <5c7vf9$rnp_at_lana.zippo.com>, Brian Wisniewski <bwisniewski_at_sprintmail.com> writes
>I don't know if this is possible or not and to date I haven't been
>able to figure this out and haven't found anything on the FAQ's
>that would answer my question. I'm also new to Oracle and am not sure
>of what its limitations/strengths are so please bear with me.
>
>I want to build a 'generic' trigger that I can create on several tables
>without having to make major modifications. It needs to figure out
>and concatenate the PK (which it does) and then read all the column
>names for that table and compare the old and new values (the values part
>is what I'm having problems with).
>
>I've included a simple example below to show what I'm currently
>trying to do. The variable COLUMN_NAME will hold the actual
>name of the column (last_name) from the table the trigger is firing on.
>
>cursor get_columns ...
>open ...
>loop
> fetch get_columns into COLUMN_NAME;
> if (:new.COLUMN_NAME <> :old.COLUMN_NAME) then ...
>
>When I try to create this trigger I get an error message stating that
>COLUMN_NAME in the 'IF' stmt is an invalid column. I didn't think
>this would work but was hoping COLUMN_NAME would get resolved.
>
>Anyone have an idea as to how to compare the old and new values without
>having to explicitly type IF (:new.last_name <> :old.last_name)...
>
>Thanks in advance.
>
>
>
>
>

I'm not sure exactly what your driving at actually doing here, but there is a package DBMS_SQL in Oracle that allows you to write trully dynamic SQL, which is, I think the crux of the problem?

Using DBMS_SQL you are able to write cursors that are able to accept variables at run time in any part of the SQL statement. That sounds a bit easier than it really is, but when you get the hang of it this is a very powerful package, and it *can* be no more difficult than that.

Without knowing exactly what you are trying to acheive, it's a bit difficult to give a coded solution/suggestion, but I can first suggest that if this sounds like it may help, study the package header for the DBMS_SQL package, if you have/can get sufficient priviledges.

There is also an exellent publication that includes a pretty good discussion on DBMS_SQL; Oracle PL/SQL, published by O'Reilly & Associates, written by Steven Feuerstein. If your new to Oracle and have to do any amount of PL/SQL then this is the best book I've seen to get started with and gets into some pretty advanced topics.

As you want this to be generic, using DBMS_SQL may be a good way of writing the 'trigger' once, in a package and using the individual triggers as 'stubs' to call this package giving the necessary parameters. Saves on code, and should run faster, because the package is likely to remain in the SGA longer if it's being called often.

If this sounds like what you need and you're having a problem with it or don't get a better answer from someone else, feel free to mail me explaining in greater detail, and I'll see if I can help.

Good luck.

-- 
Mike Proctor                    Database Consultants International (UK)

Mike_Proctor_at_realworld.com      Oracle vision.
                                Only Oracle.
                                All the time.
Received on Thu Jan 30 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US