RE: Trigger

From: <raman.yagna_at_db.com>
Date: Thu, 7 Dec 2000 10:00:49 +0800
Message-Id: <10702.123883@fatcity.com>

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 ----------------------------------------

From: R.Daemen_at_facent.nl on 06/12/2000 17:31 GMT

Please respond to ORACLE-L_at_fatcity.com

To: ORACLE-L_at_fatcity.com
cc:
Subject: RE: Trigger

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

Received on Wed Dec 06 2000 - 20:00:49 CST