Trigger

From: <raman.yagna_at_db.com>
Date: Wed, 6 Dec 2000 17:57:26 +0800
Message-Id: <10702.123761@fatcity.com>

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. Received on Wed Dec 06 2000 - 03:57:26 CST