Home » SQL & PL/SQL » SQL & PL/SQL » logic for procedure (Oracle 10g)
logic for procedure [message #574971] Thu, 17 January 2013 04:59 Go to next message
sss111ind
Messages: 464
Registered: April 2012
Location: India
Senior Member

Hi All,

    create table t1(empno number,ename varchar2(20),sal number);
    
    INSERT INTO t1(7839,'MILLER',5000);
    
    update t1 set ename='CLARK',SAL=7000 WHERE empno=7839;
    
    CREATE TABLE t2(field VARCHAR2(20),new_value VARCHAR2(20),old_value VARCHAR2(20));
    

when update has been made to t1 table t2 should get get THE DATA LIKE as follows,
    field   new_vlaue old_value
    'ENAME','CLARK','MILLER'
    'SAL',    5000,  7000
    


Plese suggest me.

Regards,
Nathan
Re: logic for procedure [message #574972 is a reply to message #574971] Thu, 17 January 2013 05:06 Go to previous messageGo to next message
Littlefoot
Messages: 19303
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
A database trigger can do that; have a look at example.
Re: logic for procedure [message #574976 is a reply to message #574972] Thu, 17 January 2013 05:28 Go to previous messageGo to next message
sss111ind
Messages: 464
Registered: April 2012
Location: India
Senior Member

Hi LittleFoot,

What Url you have given that is not opening(for sequrity reason).Can you please just copy it in this session.

Regards,
Nathan
Re: logic for procedure [message #574977 is a reply to message #574976] Thu, 17 January 2013 05:32 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
CREATE or REPLACE TRIGGER trig1 
after UPDATE ON t1 
REFERENCING OLD as old
                   NEW as new
FOR EACH ROW 
declare
type lst_cl_name is table of user_tab_cols.column_name%type index by pls_integer;
v_cl_name lst_cl_name;

BEGIN 
select column_name bulk collect into v_cl_name from user_tab_cols where table_name='T1';
for i in 1..v_cl_name.count loop
INSERT INTO t2(field,old_value,new_value) VALUES 
(v_cl_name(i),
 :old.v_cl_name(i), 
 :new.v_cl_name(i)
 ); 
 end loop;
END; 
/ 


But getting error

SQL> sho err
Errors for TRIGGER TRIG1:

LINE/COL ERROR
-------- ---------------------------------------------------
10/2     PLS-00049: bad bind variable 'OLD.V_CL_NAME'
11/2     PLS-00049: bad bind variable 'NEW.V_CL_NAME'


Please explain me why?

Re: logic for procedure [message #574978 is a reply to message #574977] Thu, 17 January 2013 05:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58521
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
:OLD :NEW

Please read:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

There is a WHOLE chapter on coding triggers.

Regards
Michel

[Updated on: Thu, 17 January 2013 05:46]

Report message to a moderator

Re: logic for procedure [message #574979 is a reply to message #574978] Thu, 17 January 2013 05:47 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Thanks Michel

Below is bit clumsy but it surely works

CREATE OR REPLACE TRIGGER trig1 AFTER
  UPDATE ON t1 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
  DECLARE 
  type lst_cl_name IS TABLE OF user_tab_cols.column_name%type INDEX BY pls_integer;
  v_cl_name lst_cl_name;
  BEGIN
    SELECT column_name bulk collect
    INTO v_cl_name
    FROM user_tab_cols
    WHERE table_name='T1';
    FOR i          IN 1..v_cl_name.count
    LOOP
      IF v_cl_name(i)='ENAME' THEN
        INSERT
        INTO t2
          (
            field,
            old_value,
            new_value
          )
          VALUES
          (
            v_cl_name(i),
            :old.ename,
            :new.ename
          );
      elsif v_cl_name
        (
          i
        )
        ='SAL' THEN
        INSERT
        INTO t2
          (
            field,
            old_value,
            new_value
          )
          VALUES
          (
            v_cl_name(i),
            :old.sal,
            :new.sal
          );
      ELSE
        INSERT
        INTO t2
          (
            field,
            old_value,
            new_value
          )
          VALUES
          (
            v_cl_name(i),
            :old.empno,
            :new.empno
          );
      END IF;
    END LOOP;
  END;
/ 




SQL> select * from t1;

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7839 CLARK                      7000

SQL> truncate table t2;

Table truncated.

SQL>     update t1 set ename='RISH',SAL=1000 WHERE empno=7839;

1 row updated.

SQL> select * from t2;

FIELD                NEW_VALUE            OLD_VALUE
-------------------- -------------------- --------------------
EMPNO                7839                 7839
ENAME                RISH                 CLARK
SAL                  1000                 7000



[Updated on: Thu, 17 January 2013 05:53]

Report message to a moderator

Re: logic for procedure [message #574984 is a reply to message #574979] Thu, 17 January 2013 06:12 Go to previous messageGo to next message
sss111ind
Messages: 464
Registered: April 2012
Location: India
Senior Member

Thanks All,

It would be better if RishWinger first code could have worked.
alter table t1 add(hiredate date,job varchar2(20));
update t1 set ename='CLARK',SAL=7000 WHERE empno=7839;



I think THE SECOND code will WORK even IF i ALTER THE TABLE AND still want TO
get the same updated data into t2 table.

Regards,
Nathan
Re: logic for procedure [message #575012 is a reply to message #574984] Thu, 17 January 2013 14:49 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

i think if you maintain a history table and insert or update a record into this history table whenever there is a change or insert in t1, i will go with the idea of littlefoot of writing trigger.
Re: logic for procedure [message #575024 is a reply to message #575012] Thu, 17 January 2013 23:45 Go to previous messageGo to next message
sss111ind
Messages: 464
Registered: April 2012
Location: India
Senior Member

Hi All,

Yes correct,I just wanted to maintain the history table. Any idea how to maintain this or any suggestion.

Regards,
Nathan
Re: logic for procedure [message #575029 is a reply to message #575024] Fri, 18 January 2013 00:59 Go to previous messageGo to next message
Littlefoot
Messages: 19303
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Hm, strange - that's what an example I posted previously does. You said you can't open the page for "security reasons" - what security? This is just another forum (dBforums) ... Anyway, here you are, copy/paste from there.

First, create an empty "history" table (I called it MY_DEPT) (changes made in Scott's DEPT table will be stored in there).
SQL> create table my_dept as select * from dept where 1 = 2;

Table created.

SQL> create or replace trigger trg_bu_dept
  2    before update on dept
  3    for each row
  4  begin
  5    insert into my_dept
  6      (deptno, dname, loc)
  7      values
  8     (:old.deptno, :old.dname, :old.loc);
  9  end;
 10  /

Trigger created.


SQL> select * from my_dept;

no rows selected

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> update dept set dname = 'sales' where deptno = 30;

1 row updated.

SQL> select * from my_dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 sales          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Re: logic for procedure [message #575030 is a reply to message #575024] Fri, 18 January 2013 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 58521
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 17 January 2013 12:45
:OLD :NEW

Please read:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

There is a WHOLE chapter on coding triggers.
...


And I'd add including a section "Examples of Trigger Applications" which contains an example "Auditing with Triggers" which is closed to what you want.
Study it and come back if you can't achieve your goal.
But STUDY it and do NOT rely on forum to do your job.

Regards
Michel

Re: logic for procedure [message #575084 is a reply to message #575030] Sat, 19 January 2013 01:27 Go to previous message
sss111ind
Messages: 464
Registered: April 2012
Location: India
Senior Member

Thank you all.

I got to know that there is no possibility of doing it dynamically what RishWinger was trying to achieve earlier.So I opted for LittileFoot's solution.

I found to Urls,
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055
http://www.oracle-base.com/articles/9i/complete-data-audit.php

Regards,
Nathan

[Updated on: Sat, 19 January 2013 01:29]

Report message to a moderator

Previous Topic: delete table if exist
Next Topic: problem in sql query
Goto Forum:
  


Current Time: Wed Jul 23 19:25:30 CDT 2014

Total time taken to generate the page: 0.13333 seconds