Hi,
It's not woking for me. IS it working for you. Thanks ....
Regards,
Raman
- Message History ----------------------------------------
From: gvrvrr_at_yahoo.com on 07/12/2000 04:36 GMT
Please respond to ORACLE-L_at_fatcity.com
To: ORACLE-L_at_fatcity.com
cc:
Subject: 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
> (like subscribing).
Do You Yahoo!?
Yahoo! Shopping - Thousands of Stores. Millions of Products.
http://shopping.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rajagopal Venkataramany
INET: gvrvrr_at_yahoo.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
Received on Wed Dec 06 2000 - 23:52:58 CST
