Oracle Trigger 3

[Rocco03]

I need to write a trigger that will update a field on one table after a field in another table has been modified.

Table1.field1 has been modified so now I need it to update Table2.field2 with that updated information.

I'm new Oracle and need some assistance.

Rocco03

 

[SantaMufasa]

Rocco,

I'm happy to provide a trigger to do what you suggest. Is your trigger to fire for INSERTs and DELETEs in addition to UPDATEs?

Dave
Sandy, Utah, USA @ 21:09 GMT, 14:09 Mountain Time

 

[Rocco03]

You are correct. This must be done for instances.

 

[Rocco03]

I'm sorry about my english. But I do need for update and inserts. I do not need it for any deletes for they will only be modifiying the data in one table.

Rocco03

 

[SantaMufasa]

Rocco,

Your English is just fine. (I'm sure your English is much better than my command of your first language !)

Here is a VERY simple example of a minimum trigger that INSERTs into another table a message following either INSERT, UPDATE, or DELETE on the S_EMP table:

create table emp_audit (message varchar2(50));
create or replace trigger emp_change_minimum_trigger 
Before insert or update or delete on s_emp for each row
BEGIN
	if inserting then
		insert into emp_audit values (:new.id||': Inserted');
	elsif deleting then
		insert into emp_audit values (:old.id||': Inserted');
	else
		insert into emp_audit values (:new.id||': Updated');
	end if;
end;
/

I'm happy to build for you a much more usable trigger that can audit inserts, updates, or deletes to ANY TABLE and record the Table Name, Action Type ("Insert", "Update", or "Delete&quot, Action Date/Time, Oracle User who made the change, Changed-Column_name, Previous column value, and New column value.

(By the way, what is your first language?)

Let me know if you want the more extensive trigger,

Dave
Sandy, Utah, USA @ 23:07 GMT, 16:07 Mountain Time

 

[ahmedabad]

Dave,

Can u pls. provide me details on how to get information on Oracle User who made the chnage ( from trigger code ) ?

Thanks
Jay

 

[sem]

What kind of information do you need? The name may be obtained from USER function.

Regards, Dima

http://seminihin.narod.ru

 

[ahmedabad]

I just wanted to know user name who made change.

Thanks Dima.

 

[sem]

So add one more column to Dave's emp_audit table:

alter table emp_audit add (operator varchar2(30));

create or replace trigger emp_change_minimum_trigger
Before insert or update on s_emp for each row
BEGIN
if inserting then
insert into emp_audit values new.id||': Inserted', user);
else
insert into emp_audit values new.id||': Updated', user);
end if;
end;


Regards, Dima

http://seminihin.narod.ru

 

[bookouri]

Could anyone tell me how to capture the machine/client information.. if I can capture the USER, can I capture information about the machine the user is logged on to?

 

[SantaMufasa]

Bookouri,

To facilitate obtaining machine/client information did the following:

1) Created a "GET_SESSION_INFO" function (in the SYS schema)
2) Granted EXECUTE privileges to PUBLIC on the "GET_SESSION_INFO" function
3) Created a PUBLIC synonym on the function.

Here is what I did (as SYS):

create or replace function sys.get_session_info
		(sess_id    in number
		, info_type in varchar2) return varchar2
	is
		w_osuser	varchar2(100);
		w_machine	varchar2(100);
		w_user		varchar2(100);
	begin
		Select	osuser, machine, user into
				w_osuser, w_machine, w_user
			from v$session
			where audsid = sess_id;
		if	upper(info_type) = 'OSUSER' then
			return w_osuser;
		elsif	upper(info_type) = 'MACHINE' then
			return w_machine;
		elsif	upper(info_type) = 'USER' then
			return w_user;
		end if;
	end;
/*	Above function accesses session information */
/

function created.

grant execute on get_session_info to public;

Grant succeeded.

create public synonym get_session_info for sys.get_session_info;

Synonym created.

Then connected as some other user:

SQL> col osuser format a20
SQL> col machine like osuser
SQL> col usr heading "Oracle|User" format a20
SQL> select get_session_info(userenv('sessionid'),'OSUSER') OSUSER
  2  , get_session_info(userenv('sessionid'),'MACHINE') machine
  3  , get_session_info(userenv('sessionid'),'USER') usr
  4  from dual
  5  /
                                          Oracle
OSUSER               MACHINE              User
-------------------- -------------------- --------------------
dhunt                TFMOBILE2709         TEST

1 row selected.

You may use the "get_session_info" in an audit trigger, as well.

Let me know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:24 (15Jan04) GMT, 16:24 (15Jan04) Mountain Time)

 

[carp]

Dave -
Beautiful! I knew there was a way to pick the right session out of v$session, but had forgotten the particulars.
Thanks!

 

[bookouri]

thanks, that is GREAT... perfect...

 

[AOLEURO]

Hi

I would not use the above described code. I would only accept it if developers and other used it outside application. I would not use the code in triggers, packages and so on. The code is fast to create but performance is a town in Russia.

First: you got the problem that you must query to get the information.

Next: you got the problem that you need to query repeated times to get several information.

Third: In case of using the code in an AUTID trigger – you query a lot.

One way to avoid it – is to use the following:

Create a storage package as SYS. In here you create global variables (ex. UserName, Machine, and so on). These variables are initialized in an LOGON schema trigger there the package is called. Now the variables are available in all the user session without any query. I would still try to avoid triggers for each row.

And another way to avoid it – create context. Use the above describe storage package and create context. And use the context through the code.


Regards
Allan
Icq: 346225948

 

[sem]

Allan, what's wrong with towns in Russia? I've just come back from St. Petersburg - quite nice city
I think that populating those variables in package initializer is better than doing it from trigger.

Regards, Dima

http://seminihin.narod.ru

 

[AOLEURO]

Hi Dima

Oh - nothing except that girls from East Europe is so lovely and alcohol is raining down all over the place – A cola is more expensive than Vodka.

No – actually after the USSR collapsed we have a change to see nice old culture like old towns in Russia and that’s fine.

But people are still very poor.

Regards
Allan
Icq: 346225948

 

[sem]

Some people are very poor.
It was snowy there, but I'm sure that anybody who needs those rains may find them almost anywhere
I suppose that both your and Dave's advices to create something in SYS schema are at least disputable: I find it to be better to grant explicit SELECT on V$SESSION to somebody else and create that package in its schema. Then, populating values during package initializing has an advantage of NOT EXECUTING without need. Then, querying only those fields from v$session may be done only once per session, but some other parameters may be changed on OPS/RAC, so the life-cycle of this storage may be discussed.
Thus my bet is a package with accessors but with no mutators and "hidden" package variables populated during the first invocation for this specific case and probably one more "refresh" procedure to renew a "cache".


Regards, Dima

http://seminihin.narod.ru

 

[bookouri]

"Create a storage package as SYS. In here you create global variables (ex. UserName, Machine, and so on). These variables are initialized in an LOGON schema trigger there the package is called. "

Can you give me more details about how to create and use a package like this? Ive never tried anything similar before.

thanks

 

[SantaMufasa]

Bookouri,

Global variables are data items that (by default) are visible during the entire life of a session. That is, their values "persist" rather than revert to an initial value each time we invoke the package in which we define the global variables. We define global variables in the header portion of a package definition. Following is a VERY SIMPLE example of a global-variable definition and some examples of usage:

set serveroutput on
create or replace package dh_ws is
	WS_NUM	number;
end;
/
exec dh_ws.ws_num := 0;
exec dbms_output.put_line(dh_ws.ws_num);
exec dh_ws.ws_num := dh_ws.ws_num + 100;
exec dbms_output.put_line(dh_ws.ws_num);
exec dh_ws.ws_num := dh_ws.ws_num + 100;
exec dbms_output.put_line(dh_ws.ws_num);
exec dh_ws.ws_num := dh_ws.ws_num + 100;
exec dbms_output.put_line(dh_ws.ws_num);

In AOLEURO's earlier post, he suggests storing values in persistent, global variables. Did this answer your question satisfactorily?

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:38 (19Jan04) GMT, 12:38 (19Jan04) Mountain Time)

 

[SantaMufasa]

Don't you just hate it when you realize, just after your finger releases the [Submit Post] button that your post was missing something? Here is the output from the usage of the "DH_WS.WS_NUM" global variable:

SQL> exec dh_ws.ws_num := 0;
SQL> exec dbms_output.put_line(dh_ws.ws_num);
0
SQL> exec dh_ws.ws_num := dh_ws.ws_num + 100;
SQL> exec dbms_output.put_line(dh_ws.ws_num);
100
SQL> exec dh_ws.ws_num := dh_ws.ws_num + 100;
SQL> exec dbms_output.put_line(dh_ws.ws_num);
200
SQL> exec dh_ws.ws_num := dh_ws.ws_num + 100;
SQL> exec dbms_output.put_line(dh_ws.ws_num);
300

Sorry,

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:41 (19Jan04) GMT, 12:41 (19Jan04) Mountain Time)