| Counting No of DML operations [message #370194] |
Sat, 30 December 2000 07:47  |
Shrikant Gavas
Messages: 4
Registered: November 2000
|
Junior Member |
|
|
Hi
I want to have complete details about every DML (Insert, Update, Delete) operations performed on a table .
This Details must include Username and Date on whch modification have been done.
Is there any View or table In Oracle Database which can give me this Information..
Shrikant
|
|
|
|
| Re: Counting No of DML operations [message #370196 is a reply to message #370194] |
Sat, 30 December 2000 13:07  |
Robert Moy
Messages: 15
Registered: December 2000
|
Junior Member |
|
|
Hello Shrikant:
You can use a "Trigger" to do this. I am using a trigger to monitor any changes (insert, delete, and updates) to my wage table. The Trigger will put information about these changes into another table called security. The code is shown below.
CREATE OR REPLACE TRIGGER security
After Insert or UpdATE or delete ON wage
FOR EACH ROW
BEGIN
If :old.salary <> :new.salary then
DBMS_OUTPUT.PUT_LINE(' Change on wage Notice: Old Salary: '
|| :Old.salary || ' New salary '
|| :New.salary || ' For employee: ' || :Old.name);
end if;
If :old.city <> :new.city then
DBMS_OUTPUT.PUT_LINE(' Change on City Notice: Old city: '
|| :Old.city || ' New city '
|| :New.city || ' For employee: ' || :Old.name);
end if;
Insert into security
(Old_name,Old_salary,New_salary,old_city,new_city,change_date)
Values
(:Old.name,:Old.salary,:New.Salary,:old.city,:new.city,sysdate);
Update security
Set What_change = 'Salary change for ' || old_name
Where old_salary <> new_salary
and
change_date = sysdate;
Update security
Set What_change = 'City change for ' || old_name
Where old_city <> new_city
and
change_date = sysdate;
End;
Trigger created.
If you wish, you can have these changes show up immediate after a change has been made
The code is shown below.
Declare
Message Varchar2(1800);
Status Integer;
Begin
DBMS_ALERT.REGISTER('Wage_change');
DBMS_ALERT.WAITONE('WAGE_CHANGE' ,message,status,20);
DBMS_output.PUT_LINE(message);
DBMS_ALERT.REMOVE('WAGE_CHANGE');
End;
Good Luck,
Robert Moy
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
