RE: Trigger

From: Daemen, Remco <R.Daemen_at_facent.nl>
Date: Wed, 6 Dec 2000 12:48:12 +0100
Message-Id: <10702.123806@fatcity.com>

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
Received on Wed Dec 06 2000 - 05:48:12 CST