Home » SQL & PL/SQL » SQL & PL/SQL » capture change info
capture change info [message #260537] Mon, 20 August 2007 04:40 Go to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
AOA
i have table named backup which has
================
create table update_history
( table_name varchar2(25) , -- name of table where updation
column_name varchar2(30) , -- column name updated
old_value varchar2(30) ,-- old value
new_value varchar2(30) ,-- new updated value
time_stamp date) -- present time
======================================
now if do a update on emp , dept or salgrade
============== i want the above table to be filled with appropriate rows containing the correct info.
Re: capture change info [message #260539 is a reply to message #260537] Mon, 20 August 2007 04:47 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
or simply do it for one table say "dept"
only
Re: capture change info [message #260543 is a reply to message #260537] Mon, 20 August 2007 04:54 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Create tigger to do this.
Re: capture change info [message #260544 is a reply to message #260543] Mon, 20 August 2007 05:01 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
ya i have created trigger
but how to know which column hs been changed , and which has not been, please tell about it
thanx and regards
Re: capture change info [message #260547 is a reply to message #260544] Mon, 20 August 2007 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Compare old and new values.

Regards
Michel
Re: capture change info [message #260562 is a reply to message #260547] Mon, 20 August 2007 05:27 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
ya i can do
but any other better approach
-- flexible coding
like if the table has say 40 columns, then
how wud i compare all of them.
thanx michel for always being there
regards

PEACE
Re: capture change info [message #260576 is a reply to message #260562] Mon, 20 August 2007 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have 40 rooms in floor 1 numbered 1 to 4 and 40 rooms in floor 2 numbered 1 to 40, in each room there is 1 person.
How can you know if room of same number has a person of the same sex in both floors?
I don't know any other way than looking in each room.
If you know another way, tell us.

Regards
Michel
Re: capture change info [message #260583 is a reply to message #260576] Mon, 20 August 2007 06:40 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd like to volunteer and check ladies' rooms only, please.
Re: capture change info [message #260584 is a reply to message #260537] Mon, 20 August 2007 06:42 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Laughing Laughing Laughing
Re: capture change info [message #260588 is a reply to message #260584] Mon, 20 August 2007 06:53 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
sorry little foot
its a boys hostel ,
but i do respect your devotion and commitment,
but i will appreciate more if u come back to
me with a solution.
regards and thanx

PEACE
Re: capture change info [message #260591 is a reply to message #260584] Mon, 20 August 2007 06:55 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
I don't know any other way than looking in each room.


But it would be nice if there would be a way to tell our intern :
look in each room and compare the person to the one with the same number room on the other floor



instead of having to tell our intern :

look in room 1 and compare the person to room 1 on the other floor.
look in room 2 and compare the person to room 2 on the other floor.
look in room 3 and compare the person to room 3 on the other floor.
look in room 4 and compare the person to room 4 on the other floor.
look in room 5 and compare the person to room 5 on the other floor.
....
look in room 36 and compare the person to room 36 on the other floor.
look in room 37 and compare the person to room 37 on the other floor.
look in room 38 and compare the person to room 38 on the other floor.
look in room 39 and compare the person to room 39 on the other floor.
look in room 40 and compare the person to room 40 on the other floor.
.....
(but don't check the lady's room, Littelfoot has dibs on that.)


But I haven't found a solution to do it the first way either, the couple of times I needed something like that.
Re: capture change info [message #260598 is a reply to message #260591] Mon, 20 August 2007 07:26 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
i have found something in ddl
now can i have something like
===========================================
SELECT column_name
FROM USER_TAB_COLUMNS
where DATA_UPGRADED in ('NO' , 'YES')
===========
but data_upgraded = 'yes' for every column,
==can i have something like
=======================
where column.status = 'changed'
===
or something similar


regards and thanx


PEACE

Re: capture change info [message #260601 is a reply to message #260537] Mon, 20 August 2007 07:37 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Value of Data Upgraded indicates whether the column data has been upgraded to the latest type version format.

Not the one you think about.
Re: capture change info [message #260639 is a reply to message #260601] Mon, 20 August 2007 09:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could write something to generate the code for the body of the trigger using the list of columns in user_Tab_columns.
That way you can regenerate the trigger automatically whenever the table definition changes.
Re: capture change info [message #260855 is a reply to message #260639] Tue, 21 August 2007 02:19 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
NOW THATS MY PROCEDURE
====================

CREATE OR REPLACE TRIGGER AUDIT_EMP_VALUES
AFTER UPDATE ON EMP
FOR EACH ROW
BEGIN
trg_proc('EMP');
END;

============
WHERE THE PROCEDURE TRG_PROC IS
==========================

1 CREATE OR REPLACE PROCEDURE TRG_PROC
2 ( a in dept.dname%type )
3 IS
4 t_name varchar2(30) ;
5 CURSOR C1 IS SELECT COLUMN_NAME
6 FROM USER_TAB_COLUMNS
7 WHERE TABLE_NAME = 'EMP' ;
8 C1_ROW C1%ROWTYPE ;
9 BEGIN
10 OPEN C1;
11 LOOP
12 FETCH C1 INTO C1_ROW;
13 EXIT WHEN C1%NOTFOUND;
14 INSERT INTO EMP10
15 VALUES(1467 , ' IMSHAH' );
16 END LOOP;
17 CLOSE C1;
18* END TRG_PROC;
SQL> /

Procedure created.
=======================
few things in procedure are hard coded like table
is sent but not used similarly inside the loop
of procedure purpose less insert statement.
=======================
NOW INSIDE THE LOOP I CANT REFERENCE THE
OLD AND NEW VALUES, MY QUESTIONS ARE
-- DOES THE :OLD.VAR , :NEW.VAR1 EXIST IN THE SCOPE OF PROCEDURE CALLED BY THE TRIGGER
-- IF THEY HOW I REFERNCE THEM AS
:OLD.C1_ROW.COLUMN_NAME = :NEW.C1_ROW.COLUMN_NAME ;
IS WRONG.
?
PLEASE HELP ME

regards and thanx

PEACE
Re: capture change info [message #260875 is a reply to message #260855] Tue, 21 August 2007 03:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
NOW THATS MY PROCEDURE

And it is not formatted: How to format your posts

Regards
Michel
Re: capture change info [message #260878 is a reply to message #260855] Tue, 21 August 2007 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
NOW INSIDE THE LOOP I CANT REFERENCE THE
OLD AND NEW VALUES, MY QUESTIONS ARE
-- DOES THE :OLD.VAR , :NEW.VAR1 EXIST IN THE SCOPE OF PROCEDURE CALLED BY THE TRIGGER

no

Regards
Michel
Re: capture change info [message #260986 is a reply to message #260855] Tue, 21 August 2007 08:06 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
sispk6 wrote on Tue, 21 August 2007 03:19

14 INSERT INTO EMP10
15 VALUES(1467 , ' IMSHAH' );



Not a good method here. You should name your columns or have your code fail in the future. And did you really want to pad your name with a space?
Re: capture change info [message #261080 is a reply to message #260537] Tue, 21 August 2007 14:32 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
have a look at bind variable thread. seems to me you are trying something similar.

As JRowbottom hinted, you cannot create a "generic" trigger, but you can use "generic" PL/SQL code to generate it.
You can find the demonstration in generic trigger for auditing column level changes thread on AskTom.
Previous Topic: Sending Outlook Calender mail
Next Topic: Is it possible to use a variable with the UNISTR function? (renamed by LF)
Goto Forum:
  


Current Time: Fri Dec 09 03:47:38 CST 2016

Total time taken to generate the page: 0.09073 seconds