logic for procedure [message #574971] |
Thu, 17 January 2013 04:59 |
|
sss111ind
Messages: 634 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 #574979 is a reply to message #574978] |
Thu, 17 January 2013 05:47 |
|
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 #575012 is a reply to message #574984] |
Thu, 17 January 2013 14:49 |
|
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 #575029 is a reply to message #575024] |
Fri, 18 January 2013 00:59 |
|
Littlefoot
Messages: 21807 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>
|
|
|
|
|