473,327 Members | 2,007 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

dynamic :NEW

700 Expert 512MB
how to make below trigger works

Expand|Select|Wrap|Line Numbers
  1. create or replace trigger .....
  2.  
  3. v1 varchar(50);
  4. st varchar(50);
  5. ...
  6. begin
  7. ....
  8. v1=':NEW.'||'column_name';
  9. execute immediate 'select '||v1||' from dual' into st
  10. dbms_output.put_line(st);
  11. ....
  12.  
the most important is that when defining trigger we don't know :NEW column names, and we generate it. I have a function that generate column names.
But the trigger i wrote do not work, the error message is
Expand|Select|Wrap|Line Numbers
  1. ORA-01008: not all variables bound
  2.  
and it as about execute statement, cos trigger treats v1 as string not as :NEW.column_name
What i want to achieve is to write a trigger which is working even if i change column names.
Jul 15 '08 #1
10 3317
amitpatel66
2,367 Expert 2GB
How are you passing the column name to this trigger from a function that you are talking about that generates a column?>
Jul 15 '08 #2
rski
700 Expert 512MB
How are you passing the column name to this trigger from a function that you are talking about that generates a column?>
the function reads colum name from system table and returns it as varchar.
Jul 15 '08 #3
amitpatel66
2,367 Expert 2GB
the function reads colum name from system table and returns it as varchar.
Could you please post your actual code for reference or a proper pseudo code for the same...Are you calling a function in the trigger and how you are doing that?
Jul 16 '08 #4
rski
700 Expert 512MB
here you are

a trigger def

Expand|Select|Wrap|Line Numbers
  1. create or replace trigger tg_qaz
  2.         after insert
  3.         on qaz
  4.         for each row
  5.         declare
  6.         st varchar(100);
  7.         v1 varchar(50);
  8. begin
  9.   v1:=':NEW.'||get_cols.get_col_name('QAZ',1);
  10.   execute immediate  'select '||v1||' from dual ' into st;
  11.   dbms_output.put_line(st);
  12. end;
  13.  
and the function, I skip the package definition

Expand|Select|Wrap|Line Numbers
  1. FUNCTION get_col_name(tab VARCHAR,num INTEGER) 
  2. RETURN VARCHAR
  3. IS
  4.     CURSOR cols IS SELECT column_name FROM all_tab_columns WHERE
  5.                           table_name=UPPER(tab);
  6.     c all_tab_columns.column_name%TYPE;
  7.  
  8.         n INTEGER;
  9.         BEGIN
  10.                 open cols;
  11.                 n:=1;
  12.         LOOP
  13.                 FETCH cols INTO c;
  14.                 EXIT WHEN cols%NOTFOUND;
  15.                 IF (n=num) THEN
  16.                         RETURN c;
  17.                 END IF;
  18.                 n:=n+1;
  19.         END LOOP;
  20.         dbms_output.put_line('There are not so many columns in '||UPPER(tab));
  21.         RETURN NULL;
  22.         END get_col_name;
  23.  
remember, i'm plsql begginer :)
Jul 16 '08 #5
amitpatel66
2,367 Expert 2GB
How many columns you have in that table?
Jul 16 '08 #6
rski
700 Expert 512MB
How many columns you have in that table?
there are 2 columns in table
Jul 16 '08 #7
amitpatel66
2,367 Expert 2GB
there are 2 columns in table
Brilliant. Then you can make the things simple instaed of using a seperate function. You can select both the columns using :NEW.column1 and :NEW.column2 and use the one you require. The :NEW is not getting BIND to any value at run time so the error.
Jul 16 '08 #8
rski
700 Expert 512MB
Brilliant. Then you can make the things simple instaed of using a seperate function. You can select both the columns using :NEW.column1 and :NEW.column2 and use the one you require. The :NEW is not getting BIND to any value at run time so the error.
I'm afraid i wasn't clear enough. The problem is that i don't know what are (better say what will be in the future) the column names cos they may changed so I can't write
:NEW.i if column is 'i' because with time it may change to let say 'j'.
That's why i try to build :NEW. dynamically reading column names fro system table.
Is it clear? Maybe I do not understand your solution?
Jul 16 '08 #9
amitpatel66
2,367 Expert 2GB
I'm afraid i wasn't clear enough. The problem is that i don't know what are (better say what will be in the future) the column names cos they may changed so I can't write
:NEW.i if column is 'i' because with time it may change to let say 'j'.
That's why i try to build :NEW. dynamically reading column names fro system table.
Is it clear? Maybe I do not understand your solution?
The problem in using it this way is that the :NEW is not getting BIND to any value at runtime.
Jul 16 '08 #10
rski
700 Expert 512MB
The problem in using it this way is that the :NEW is not getting BIND to any value at runtime.
I know that, the question is if it is possible to resolve this problem in another way.
Jul 16 '08 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Roel Wuyts | last post by:
CALL FOR CONTRIBUTIONS International Workshop on Revival of Dynamic Languages http://pico.vub.ac.be/~wdmeuter/RDL04/index.html (at OOPSLA2004, Vancouver, British Columbia, Canada, October...
1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
3
by: NateDawg | last post by:
I'm reposting this. I'm kinda in a bind untill i get this figured out, so if anyone has some input it would sure help me out. Ok, I’ve noticed a few gridview problems floating around the forum....
7
by: Jo | last post by:
Hi, How can i differentiate between static and dynamic allocated objects? For example: void SomeFunction1() { CObject *objectp = new CObject; CObject object;
0
by: alexandre.bergel | last post by:
Dear colleges, You might want to consider Dyla'07 as a good venue to present your work and your favourite programming language. Regards, Alexandre ...
0
by: Eniac | last post by:
Hi, I've been working on a custom user control that needs to be modified and the validation is causing me headaches. The control used to generate a table of 4 rows x 7 columns to display all...
0
by: Alexandre Bergel | last post by:
Dear colleague, Please, note that after the workshop, best papers will be selected, and a second deadline will then be set regarding preparation of the Electronic Communications of the...
1
by: MaryamSh | last post by:
Hi, I am creating a Dynamic Search in my application. I create a user control and in Page_load event I create a dynamic dropdownlist and 2 dynamic button (Add,Remove) By pressing Add button...
0
by: MaryamSh | last post by:
Create Dynamic Dropdownlist Controls and related event -------------------------------------------------------------------------------- Hi, I am creating a Dynamic Search in my application. I...
2
by: englishman69 | last post by:
Hello, I have been banging my head against this one for a while... Searches online have revealed many different proposals for correcting my issue but none that I can follow! My basic situation...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.