Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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');
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
![]() |
![]() |