Home » SQL & PL/SQL » SQL & PL/SQL » Trigger for two tables (Audting)
Trigger for two tables [message #418626] Mon, 17 August 2009 21:02 Go to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
hi everybody

actually I got a problem and I hope someone can help me to solve it. I wrote a trigger to audit a table and I want to put two tables in one trigger and I got a compilation error. so how can I write one trigger for two tables. The problem that I want the name of the user who changed the data his name come up and I can see it and the two tables. I wrote this code twice and still problem are there


this is my code
CREATE OR REPLACE TRIGGER referencing_clause
AFTER UPDATE OR INSERT OR DELETE
ON PATIENT, USER_PRV
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
  INSERT INTO audit_log
  (o_fname, o_mname, o_lname,o_patid, n_fname,n_mname,n_lname,n_patid, chng_by,chng_time, chng_when)
  VALUES
  (:OLD.first_name, :OLD.last_name, :OLD.family_name, :OLD.PAT_ID,
 :NEW.first_name, :NEW.last_name, :NEW.family_name , :NEW.PAT_ID,
 :USR_ENAME, SYSDATE, SYSDATE);
END referencing_clause;
/

And I wrote also in this way

CREATE OR REPLACE TRIGGER referencing_clausee
AFTER UPDATE OR INSERT OR DELETE
ON PATIENT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
  INSERT INTO audit_log
  (o_fname, o_mname, o_lname,o_patid, n_fname,n_mname,n_lname,n_patid, chng_by,chng_time, chng_when)
  VALUES
  (:OLD.first_name, :OLD.last_name, :OLD.family_name, :OLD.PAT_ID, 
:NEW.first_name, :NEW.last_name, :NEW.family_name , :NEW.PAT_ID, 
:USER_PRV.USR_ENAME, SYSDATE, SYSDATE);
END referencing_clausee;
/

[Updated on: Mon, 17 August 2009 23:54] by Moderator

Report message to a moderator

Re: Trigger for two tables [message #418627 is a reply to message #418626] Mon, 17 August 2009 21:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> I wrote this code twice and still problem are there
PROBLEM? What Problem? I don't see any problem.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: Trigger for two tables [message #418629 is a reply to message #418626] Mon, 17 August 2009 21:57 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
hi there

This is the statement I get:
Warning: Trigger created with compilation errors.


and this error come up because I used a column from another table and that column is "USR_ENAME" because I want the name of the user that I created in the Application Form to be shown so I know who's the user that change the data. I hope now is better.
Re: Trigger for two tables [message #418631 is a reply to message #418626] Mon, 17 August 2009 22:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Post Operating System (OS) name & version for DB server system.
Post results of SELECT * from v$version.

WRT Forms which version & 2-tier or 3-tier?

> I hope now is better.
Yes & NO.

SQL> SHOW ERROR
provides details behind the WARNING.

> I used a column from another table and that column is "USR_ENAME" because I want the name of the user that I created in the Application Form to be shown so I know who's the user that change the data

In a real world & multi-user environment, which row from USR_PRV table should be used?

Bottom line is I suspect you might need to redesign this audit.
Re: Trigger for two tables [message #418645 is a reply to message #418629] Mon, 17 August 2009 23:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the user name comes from another table then you have to select this table.
It should be better you put it in the session context for example using dbms_application_info package to set client_info session parameter.

To get the trigger errors use: "show error trigger referencing_clausee".

Keep your lines of code in 80 character width.

Regards
Michel

[Updated on: Mon, 17 August 2009 23:59]

Report message to a moderator

Re: Trigger for two tables [message #418744 is a reply to message #418626] Tue, 18 August 2009 08:02 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
I have windows XP Pro
I have DB 10G R2
I have Forms 10G R2
and also Forms 6I

the code will work and I can see the old and new value in Patient column in audit_log table but I will not see the name of the user that I created in USER_PRV. So there is no benefit if I can see the old and new value and I don't know the person who insert or delete or update.

The problem is I tried to add the name of another table that for users and the name of the user table is USER_PRV in the previous code. But the error like this

This error come when I implement the first code that I wrote in the first post in this topic
ON PATIENT, USER_PRV
          *

ERROR at line 3: 
ORA-04079: invalid trigger specification



And this error come up when I implement the second code that I wrote in the first post in this topic
./fa/6693/0/
Re: Trigger for two tables [message #418753 is a reply to message #418744] Tue, 18 August 2009 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 18 August 2009 06:58
If the user name comes from another table then you have to select this table.
It should be better you put it in the session context for example using dbms_application_info package to set client_info session parameter.

To get the trigger errors use: "show error trigger referencing_clausee".

Keep your lines of code in 80 character width.

Regards
Michel


[Updated on: Tue, 18 August 2009 08:51]

Report message to a moderator

Re: Trigger for two tables [message #418795 is a reply to message #418753] Tue, 18 August 2009 10:00 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Quote:
It should be better you put it in the session context for example using dbms_application_info package to set client_info session parameter.




Could you please help me more. If it's possible to rewrite my code with the thing that you are telling me about.
Re: Trigger for two tables [message #418797 is a reply to message #418795] Tue, 18 August 2009 10:06 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Could you please help me more.

http://www.lmgtfy.com/?q=oracle+dbms_application_info+client_info
Re: Trigger for two tables [message #418815 is a reply to message #418626] Tue, 18 August 2009 11:03 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
hi swan

before you post your last reply I was searching on the same thing that you gave me. But it seems complicated thing for me. Please I wish if you can do something for me to fix my problem
Re: Trigger for two tables [message #418818 is a reply to message #418815] Tue, 18 August 2009 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your problem in using the package?

Regards
Michel
Re: Trigger for two tables [message #418825 is a reply to message #418795] Tue, 18 August 2009 11:52 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8491662046178

create or replace function f return number
  2  as
  3  begin
  4          dbms_application_info.set_client_info( userenv('client_info')+1 );
  5          return 0;
  6  end;
  7  /
Re: Trigger for two tables [message #418840 is a reply to message #418626] Tue, 18 August 2009 14:34 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Thanks BlackSwan for helping.

I did as you told me so I execute the function that you wrote for me and I don't know how can I call it.

Anyway I dropped that function and I took the line and I put it here inside this code but in the VALUES I put NULL instead of writeing the the column of the User name from User_Prv. Again I can't see the name of the user in the Form. Is it possible to look and check this code if it needs any change.


CREATE OR REPLACE TRIGGER referencing_clause
AFTER UPDATE OR INSERT OR DELETE
ON PATIENT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
dbms_application_info.set_client_info( userenv('client_info')+1 );
  INSERT INTO audit_log
  (o_fname, o_mname, o_lname,o_patid, n_fname,n_mname,n_lname,n_patid, chng_by,chng_time, chng_when)
  VALUES
  (:OLD.first_name, :OLD.last_name, :OLD.family_name,
 :OLD.PAT_ID, :NEW.first_name, :NEW.last_name, :NEW.family_name ,
 NULL, :NEW.PAT_ID, SYSDATE , SYSDATE);
END referencing_clause;
/

[Updated on: Tue, 18 August 2009 14:42] by Moderator

Report message to a moderator

Re: Trigger for two tables [message #418841 is a reply to message #418840] Tue, 18 August 2009 14:42 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can't just blindly copy-and past code snippets in a trial-and-error manner. You have to understand what the Code sniplets do.

BlackSwan has shown you an example on how to read and set the session variable "client_info".

You have to set some session variable in one trigger on the first table, then read that variable in the second trigger. And that has to happen in the same sequence that the application does the update.

Since we don't have your tables and we don't have your application you are the only one who can figure out those details.

Re: Trigger for two tables [message #418849 is a reply to message #418626] Tue, 18 August 2009 15:36 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Thanks Thomas for your contribution. I'm going to post here the tables and the forms as well so you can get what I want.

Table of PATIENT

create table patient (
First_name varchar2(20),
Mid_name varchar2(20),
Family_name varchar2(20),
address varchar2(30),
pat_id number(10) PRIMARY KEY,
phone NUMBER(13));


Table of User
create table USER_PRV (
USER_ID varchar2(100) PRIMARY KEY,
USR_ENAME varchar2(20),
USR_PASSWORD varchar2(20));




Table for audit log
CREATE TABLE audit_log (
o_fname   VARCHAR2(20),
o_mname   VARCHAR2(20),
o_lname   VARCHAR2(20),
o_patid NUMBER(10),
n_fname   VARCHAR2(20),
n_mname   VARCHAR2(20),
n_lname   VARCHAR2(20),
n_patid NUMBER(10),
chng_by   VARCHAR2(100),
chng_time VARCHAR2(20),
chng_when DATE);



By the way, change the extension from txt to zip and then you will get three forms
  • Attachment: tables.txt
    (Size: 22.79KB, Downloaded 129 times)
Re: Trigger for two tables [message #418853 is a reply to message #418849] Tue, 18 August 2009 19:36 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I want to put two tables in one trigger
You may think you do, but I doubt it can or should be done.

What is the purpose of table USER_PRV?

Could an application user ever get logged into the application without a pre-existing row containing USR_ENAME & USR_PASSWORD?

What adds rows to USER_PRV & why are rows added?

Are rows ever removed from USER_PRV? If so, why are rows removed & by what?

>The problem that I want the name of the user who changed the data
OK, let's start with "name of the user".
>I want the name of the user that I created in the Application Form to be shown

From where & how does the Form obtain the name of the user?
Realize that the user ONLY interacts with the Form & this Form runs on a system which is different from client PC & different from DB server system.
Assuming the Form can & does obtain username, exactly what is done with it next?
Re: Trigger for two tables [message #419106 is a reply to message #418626] Wed, 19 August 2009 18:53 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Hi

I'll answer to questions that I understood from you.

the purpost of table user_prv?
like any system in the world if you want to add screen for user restriction so not any one can access you system that you made. I don't want to use the User name that belong to database because not anyone knows how to add user. and if you want to add users with specific restrictions so you can.

by the way, your questions are not clear. I think you are a programmer so I think you have a form for users login you can use it for my program to get the user name.
Re: Trigger for two tables [message #419107 is a reply to message #419106] Wed, 19 August 2009 19:02 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes, usually an application has an login screen.

And usually an application then stores the username in some variable, so it's available for auditing purposes, so that you don't have to worry where to get it from. Doesn't your application to that?
Re: Trigger for two tables [message #419109 is a reply to message #418626] Wed, 19 August 2009 19:13 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>like any system in the world if you want to add screen for user restriction so not any one can access you system that you made.
Agreed.
So why were you attempting to add a trigger on USER_PRV for auditing purposes?
The fact that you are attempting to audit a change in data means the user is already logged into the application & has been validated.

USER_PRV table contains all application users, so how do you determine who just changed the PATIENT data?
Re: Trigger for two tables [message #419257 is a reply to message #418626] Thu, 20 August 2009 13:48 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
hi there

actually it's very imporatant when data changed you want to know who changed the data. For example I have 1000 users and 4 users are only giving privilege to change the salary of employees and who knows may one of these 4 users increase the salary and no one knows about it, so you want to check the audit if someone changed data or not. By the way, I want to add new thing also like an alert will come for specific and the most important fields in some table. Like if salary changed only the Admin should receive this alert notice. I'm giving a global variable to the name of the user but it doesn't want to display in the audit so that's why I need to change the trigger to catch the name of user who changed the data. Please check my picture in this post.
./fa/6700/0/
  • Attachment: audting.PNG
    (Size: 12.38KB, Downloaded 296 times)
Re: Trigger for two tables [message #419273 is a reply to message #419257] Thu, 20 August 2009 15:16 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

I'm giving a global variable to the name of the user but it doesn't want to display in the audit



You seem to be seriously confused about what does what.

The global variable shouldn't be used to "display in the audit" the global variable should be used in the trigger to store the user name of the user who fired the trigger.

In what sort of global variable do you store the name of the logged in user? A session variable? A Package variable?

[Updated on: Thu, 20 August 2009 15:21]

Report message to a moderator

Re: Trigger for two tables [message #419274 is a reply to message #418626] Thu, 20 August 2009 17:10 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Hi Thomas

I'm using global variable in the login screen

:GLOBAL.P_USER_NAME:= :USER_PRV.USR_ENAME;

===================================

No I'm not using session at all.

===================================

I don't have package variable


Thanks a lot for your effort
Re: Trigger for two tables [message #419275 is a reply to message #418626] Thu, 20 August 2009 17:25 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>I'm using global variable in the login screen
>:GLOBAL.P_USER_NAME:= USER_PRV.USR_ENAME;

Now you need some mechanism so the trigger can access this information

you have an information gap that needs to be bridged.












[Updated on: Thu, 20 August 2009 17:26]

Report message to a moderator

Re: Trigger for two tables [message #419278 is a reply to message #418626] Thu, 20 August 2009 18:54 Go to previous messageGo to next message
Only-Oracle
Messages: 43
Registered: June 2009
Member
Hi

What kind of mechanism do I have to implement? is it possible to help in modifing the trigger.


Any help




Merci beaucoup
Re: Trigger for two tables [message #419281 is a reply to message #418626] Thu, 20 August 2009 20:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>What kind of mechanism do I have to implement?
Asked & answered in message #418797 in this thread.


> is it possible to help in modifing the trigger.
Yes
Re: Trigger for two tables [message #419320 is a reply to message #418626] Fri, 21 August 2009 04:02 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
At the moment you're using a forms global variable.
The database can not see forms variables.
To be able to use this value in a db trigger it needs to be stored somewhere that the db can access - using one of the methods discribed above.
Re: Trigger for two tables [message #419322 is a reply to message #419320] Fri, 21 August 2009 04:13 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Another option just came to my mind:

Put 2 additional columns in the patient table. LAST_CHANGE_DATE and LAST_CHANGE_USER. Set these values from forms when the update is done.

Then you have the date/time and the user of the last change in the table itself, so the trigger is able to see them easily in the :NEW values.
Previous Topic: Find unmatched(3 merged)
Next Topic: Question about refernce with 2 condition in oracle
Goto Forum:
  


Current Time: Sat Dec 10 16:33:39 CST 2016

Total time taken to generate the page: 0.06005 seconds