: NEW or OLD in triggers using EXECUTE IMMEDIATE

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc

Poston Tue, 08 Nov 2005 9:36 am

Good morning guys, I am having a problem, if someone could help me ...

I'm creating a trigger that takes the table columns to automatically checks whether it's insert or update, assemble the query, and then of an execute immediate, however, appears the message that \"not all variables are limited '.

I do a select on table and pried the all_tab_columns columns: columns: = i. column_name;
and I caught the values: col_dados: = ': NEW. ' ||i. column_name;
and that's why I have this error message. But I just couldn't find a way to make work.

Anyone know of anything to get around that?
something like name_in that exists in forms or something?
Location: Toledo - PR

Poston Wed, 09 Nov 2005 9:29 am

I once had to make calls to several tables. Since it does not allow to be done
Code: Select all
variavel := ':NEW'|| vcampo;
I created a routine that GENERATES all select, for each table that I wanted. So I just applied this select raised on trigger. IE, I just did a \"Facilitator\" for not having to type each sql statement.

Unfortunately, I don't know a way to do this what you want dynamically. :-(
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?

Poston Wed, 09 Nov 2005 7:49 pm

Good evening Dr. Gori ...
so ... I was precisnado to do something dynamic and generic, to prevent changes in the structure of the table.

But I don't see a way to trigger the value being inserted or updated.
I can only get the data using the references: new field ... but for that I would have to leave's static ... and that's not what I would ...:(you could paste a piece of your code so I can take a look ... will all of a sudden a light comes q: roll::) thank you very much for your interest
Location: Toledo - PR

Poston Thu, 10 Nov 2005 8:51 am

Unfortunately, I don't have the routine here.
But basically, she generated the selects static and I just applied on trigger.
Namely, I had a routine that created the select what I wanted. Sought the fields in USER_TAB_COLUMNS and created the sql as I wanted. (just so I don't have to manually create each select for each table)


Code: Select all
SQL> select ', :new.'||COLUMN_NAME
  2  from user_tab_columns
  3  where table_name = 'APLICATIVOS'
  4  /

, :new.CODIGO
, :new.TIPO
, :new.ICONE

Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?

Poston Thu, 10 Nov 2005 10:47 am

Good morning Dr. Gori Understand Yes ... thank you very much for your help ... I'm going to run some more tests, if I find something ... thank you Hugs
Location: Toledo - PR

Poston Tue, 15 Nov 2005 10:41 pm

Zida, could you elaborate on what you have to do, I still don't understand. If you're looking for audit, I have an example on my blog, but I don't know if that's what you need.

http://mportes.blogspot.com/2005/05/aud ... seira.html
Location: São Paulo

Poston Wed, 16 Nov 2005 6:27 am

I'm creating triggers that pegarao the data from a source table, and joagarao in a table mirror with the same fields, but with some extra fields to control. I'm doing this to make a project data export of our system, that should feed another system to get management information and statistics.
However, I would like to get tied up and always when the structure (columns) of the tables change, I have to edit trigger trigger in hand.
I had already seen your example in another page, don't remember which one, and we already uses this schema to create the triggers, but for this case it would work. I wish it were more dynamic ...
Or whenever changes or I change the table, I have to manually rotate a first script that creates the trigger, unless there is some way to catch when a table has changed, so I can leave a JOB at the Bank and good ...
But from what I saw, this is the most \"easy\" and Dr. Gori também gave me this.
Let's see what I'm doing, I'm going to get a better look at things ...

Thank you very much for your attention.
Location: Toledo - PR

Poston Thu, 17 Nov 2005 7:38 pm

the control fields are fixed?
Because if they are, you could keep only one pointer to the Royal table, in which case it wouldn't matter how she move, you would reach the data securely and complementary fields would be there.
I think it's more a case of project technical solution.

I'm sorry I can't be of more help.
Location: São Paulo

  • See also
    Last Post

Return to PL/SQL

Who is online

Users browsing this forum: No registered users and 1 guest