Variable whose content is the name of a column

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
Robson Costa
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 155
Joined: Fri, 17 Oct 2008 6:05 pm
Location: Campo Grande - MS

Hello,

Is it possible to use the contents of a variable that stores the name of a column of a table and use as a reference to pick up the value of this column?

Example; Let's say I want to compare the old and new values ??of the columns of a table. Not to have multiple IFS for each column, I would like to use a variable that would receive the name of the column that I want to compare the new and anti-values ??of an update. Thus:

Select all

          var iavel coluna varchar2; 
 
           coluna := 'NM_CLIENTE' 
 
           if (:old.<coluna> <> :new.<coluna>)...
DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

What do you really intend to do?

Create a trigger that compares the fields (ancient and new) from a variable ???

How would you inform the field that you want to compare?

If you can tell which field you want to compare, would not you do your process before the trigger ??
Robson Costa
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 155
Joined: Fri, 17 Oct 2008 6:05 pm
Location: Campo Grande - MS

The names of the columns I pick up from the table user_tab_columns

is that I have to check all the columns of several tables and I did not want to make several IFs.
User avatar
stcoutinho
Moderador
Moderador
Posts: 850
Joined: Wed, 11 May 2011 5:15 pm
Location: são Paulo - SP

Robson,

The suggestion can be a bit complex, but maybe you can develop a queries with the help of the user_tab_columns and the syntax of the triggers creation DDL command.

Search a little more on the subject in these Asktom links, which is the site of an Oracle Technical Director named Tom Kyte:
http://asktom.oracle.com/pls/asktom/f?p ... 9412348055 http://asktom.oracle.com/pls/apex/f?p=1 ... 4825535375 ]

maybe the solution that the tone presents in these links (the first of it already presents the complete solution) is as close as possible than you want.

Another suggestion, much more "laborious", would be you use Erwin and the macros language of the same to create these triggers. Only in this case you would need a little time to study how the macro language works on Erwin. It seems at first a little complicated but when "you get the way", you can develop the DDL codes quickly and reliably, from a small amount of macro command lines. And it is best that these macros guarantee you that you will not have future problems by changing the structure of the table. The macro reads this structure and dynamically rides the trigger code in real time. You do not spend any time trying to maintain / update the triggers code. And since you have managed to develop a trigger macro for a table, you can reuse this macro to generate the trigger DDLs of the other system tables (and here no matter how many columns the table has).

I understand that it is a paid product, but you can download the Erwin Community Edition from the CA website (an evaluation version that allows you to work with up to 20 tables) and to make reverse engineering One of your bank's tables. Study the macros and then try to develop a trigger. Obtaining a macro, you can import table the table from your Erwin and reuse the macro to generate the table trigger DDL.

Hugs and good luck,

Sergio Coutinho
Robson Costa
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 155
Joined: Fri, 17 Oct 2008 6:05 pm
Location: Campo Grande - MS

It worked. Thanks
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest