Help - Search - Members - Calendar
Full Version: Problem with cursor in trigger
Oracle DBA Forums > Oracle > Oracle Forum
unname2010
Hello,i have a problem with Cursor in trigger,please help me
CODE
create or replace trigger admin
                  after insert on admin
                  for each row
                  declare
                   bang varchar2(250):='ADMIN';  
                   tam varchar2(250);
                   tam1 varchar2(250);
                   cursor cs1 is Select COLUMN_NAME from user_tab_columns where table_name=bang;
                  
                begin
                    for i in cs1
                    loop

                         tam:=':new.'||i.column_name'; --such as :new.username,:new.password ....
                         -- but i query [b] select tam from dual[/b] ,it just show a string [b]:new.username[/b] not result (such as admin....)
                     -- select :new.i.column_name from dual but not run
                         select tam into tam1 from dual;
                                            
                    end loop;
                      
                  end;

i will try with select :new.i.column_name from dual, but not run (because i can't determination exactly column name thus i must use column_name variable)


Thank
burleson
Hi,

>> See here:

http://www.remote-dba.net/t_grid_rac_admin_triggers.htm

burleson
Hi.

>> tam:=':new.'||i.column_name'; --such as :new.username,:new.password ....
-- but i query select tam from dual ,it just show a string :new.username not result

It's is's "all select from dual". You don't, need a trigger at a!!!

Why is there a loop in the the PL/SQL?

I have no idea what you are trying to do!

Can you please whloew w=some working code with the rrrrors?
unname2010
QUOTE (burleson @ Dec 25 2011, 09:52 AM) *
Hi.

>> tam:=':new.'||i.column_name'; --such as :new.username,:new.password ....
-- but i query select tam from dual ,it just show a string :new.username not result

It's is's "all select from dual". You don't, need a trigger at a!!!

Why is there a loop in the the PL/SQL?

I have no idea what you are trying to do!

Can you please whloew w=some working code with the rrrrors?


because my purpose is want to write new row be inserted(include column name anh value) to file (with all table in database),if i have 1000 table ? i must create 1000 trigger and try to remember column name of table each ?
i'm trying with this code

CODE
declare
    cursor cs is select table_name from user_tables;
begin
    for i in cs
    loop    
    
      execute immediate '
                
                  create or replace trigger '||i.table_name||'
                  after insert on '||i.table_name||'
                  for each row
                  ...................

But i still not get value in cursor
please help me

Thank
burleson
Hi


>> because my purpose is want to write new row be inserted(include column name anh value)

No offense, but that is not a great idea, re-storing dictionary matadata (column_name) is-if it were table data.
burleson
Lets step back . . . .

What are you trying to accomplish?
burleson
Hi,

>> ),if i have 1000 table ? i must create 1000 trigger and try to remember column name of table each ? i'm trying with this code

OK, why not just tmake a "real table for this pupose?

CODE
mr value
1   3625242
2   6454
3   36353
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.