RE: Trigger

Raman,

  The way it is coded, u will get the field name only   because you have just formed the field name and   stored it in the 2 fields shown below.

> > newvalname := ':NEW.'||fldname;
> > oldvalname := ':OLD.'||fldname;
> > stmt := 'begin' || chr(10) ||
> 'dbms_output.put_line(' ||
> > ':newvalname' ||
> > ');' || chr(10) || 'end;';

  Modify this code and place it after the dbms_sql.bind_variable clause to get the actual value stored in it.

  Hope this helps...

Regards
Rajagopal Venkataramany

• raman.yagna_at_db.com wrote:
  > Hi remco,
  >
  > Thanks . But whenever the table changes I
  > will have to run the gen_trigger once again . With
  > my approach there is no need for trigger
  > recompilation.
  >
  > Regards,
  > Raman
  >
  > ---------------------------------------- Message
  > History ----------------------------------------
  >
  >
  >
  > Please respond to ORACLE-L_at_fatcity.com
  >
  > To: ORACLE-L_at_fatcity.com
  > cc:
  >
  >
  >
  > Hi Raman,
  >
  > You can simply use the folowing statement (I used a
  > table called AAA in my
  > example, with two columns A and DATUM) :
  >
  > create or replace trigger tr_AAA
  > before update on AAA for each row
  > begin
  > dbms_output.put_line(:new.A);
  > dbms_output.put_line(:new.DATUM);
  > end;
  > /
  >
  > And if you want to generate this code, try this
  > procedure:
  >
  > create or replace procedure gen_trigger(
  > i_table_name in varchar2 )
  > as
  > CURSOR get_columns IS
  > SELECT DISTINCT COLUMN_NAME
  > FROM ALL_TAB_COLUMNS
  > WHERE TABLE_NAME = upper(i_table_name);
  > begin
  > dbms_output.put_line('create or replace trigger
  > tr_'||i_table_name);
  > dbms_output.put_line('before update on
  > '||i_table_name||' for each row');
  > dbms_output.put_line('begin');
  > for rec in get_columns
  > loop
  >
  > dbms_output.put_line('dbms_output.put_line(:new.'||rec.column_name||');');
  > end loop;
  > dbms_output.put_line('end;');
  > dbms_output.put_line('/');
  > end;
  > /
  >
  > exec gen_trigger('aaa')
  >
  > HTH, Remco
  > > ----------
  > > From:
  > raman.yagna_at_db.com[SMTP:raman.yagna_at_db.com]
  > > Sent: woensdag 6 december 2000 11:00
  > > To: Multiple recipients of list ORACLE-L
  > > Subject: Trigger
  > >
  > > Hi,
  > >
  > > /** Code starts here **/
  > > create or replace trigger test
  > > before update on dept for each row
  > > begin
  > > declare
  > > fldname varchar2(50);
  > > newval varchar2(2000);
  > > oldval varchar2(2000);
  > > newvalname varchar2(2000);
  > > oldvalname varchar2(2000);
  > > CURSOR cur IS SELECT DISTINCT COLUMN_NAME,
  > DATA_TYPE
  > > FROM ALL_TAB_COLUMNS
  > > WHERE TABLE_NAME = 'DEPT';
  > > REC CUR%ROWTYPE;
  > > c_sql_cur integer;
  > > c_ignore integer;
  > > stmt varchar2(1000);
  > > BEGIN
  > > for rec IN cur
  > > loop
  > > fldname := rec.column_name;
  > > newvalname := ':NEW.'||fldname;
  > > oldvalname := ':OLD.'||fldname;
  > > stmt := 'begin' || chr(10) ||
  > 'dbms_output.put_line(' ||
  > > ':newvalname' ||
  > > ');' || chr(10) || 'end;';
  > > c_sql_cur := dbms_sql.open_cursor;
  > > dbms_sql.parse(c_sql_cur,stmt
  > ,dbms_sql.v7);
  > >
  > dbms_sql.bind_variable(c_sql_cur,'newvalname',newvalname);
  > > c_ignore := dbms_sql.execute(c_sql_cur);
  > > dbms_sql.close_cursor(c_sql_cur);
  > > end loop;
  > > END;
  > > END;
  > > /
  > > /** Code starts here **/
  > >
  > > update dept set deptno=10 where rownum < 2;
  > > :NEW.DEPTNO
  > > :NEW.DNAME
  > > :NEW.LOC
  > > :NEW.DEPTNO
  > > :NEW.DNAME
  > > :NEW.LOC
  > >
  > > 1 row updated.
  > >
  > > Requirement is I should see the values of :new
  > not the word from the
  > > trigger . Is it possible . Can someine help me.
  > >
  > > Regards,
  > >
  > > Raman
  > >
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author: Daemen, Remco
  > INET: R.Daemen_at_facent.nl
  >
  > Fat City Network Services -- (858) 538-5051 FAX:
  > (858) 538-5051
  > San Diego, California -- Public Internet
  > access / Mailing Lists
  >

  ---

  
  > To REMOVE yourself from this mailing list, send an
  > E-Mail message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB
  > ORACLE-L
  > (or the name of mailing list you want to be removed
  > from). You may
  > also send the HELP command for other information
  > (like subscribing).
  >
  >
  >
  >
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author:
  > INET: raman.yagna_at_db.com
  >
  > Fat City Network Services -- (858) 538-5051 FAX:
  > (858) 538-5051
  > San Diego, California -- Public Internet
  > access / Mailing Lists
  >

  ---

  
  > To REMOVE yourself from this mailing list, send an
  > E-Mail message
  > to: ListGuru_at_fatcity.com (note EXACT spelling of
  > 'ListGuru') and in
  > the message BODY, include a line containing: UNSUB
  > ORACLE-L
  > (or the name of mailing list you want to be removed
  > from). You may
  > also send the HELP command for other information