This forum is currently locked. You can't register or post questions at this time. (read more)

Context Behaviour in Triggers

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Guru3i
Senior Member
Posts: 96
Joined: Fri Aug 24, 2007 7:17 am

Context Behaviour in Triggers

Postby Guru3i » Fri Aug 17, 2012 4:58 pm

Hi Tim,

I execute the following in Scott and HR Schema

###### Scott Schema

Code: Select all

create table uom_master
(
uom_code varchar2(240),
uom_code_desc varchar2(2400),
uom_client_id varchar2(3)
);

Insert into uom_master Values
('U001','Unit of Measurement 1','001');
Insert into uom_master Values
('U002','Unit of Measurement 1','001');
Insert into uom_master Values
('U001','Unit of Measurement 2','002');
Insert into uom_master Values
('U002','Unit of Measurement 2','002');

grant select, insert, update, delete, references, alter, index on uom_master to hr;

CREATE OR REPLACE TRIGGER RND_TRG_UOM_MASTER
BEFORE INSERT ON UOM_MASTER
FOR EACH ROW
DECLARE
M_SYS_CTX_VALUE VARCHAR2(240);
BEGIN
SELECT VALUE INTO M_SYS_CTX_VALUE
FROM SESSION_CONTEXT
WHERE NAMESPACE LIKE 'RND_CLIENT%';
:NEW.uom_CLIENT_ID := M_SYS_CTX_VALUE;
END;

################ HR Schema

Code: Select all

create or replace procedure p_set_clid_context_001(p_client_id in varchar2 default '001')
--authid current_user
is
begin
sys.dbms_session.set_context('RND_CLIENT_ID_001','CLIENT_ID_001',P_CLIENT_ID);
exception
when others then
dbms_output.put_line('code '|| sqlcode || 'message '|| sqlerrm);
dbms_output.put_line('Exception '||dbms_utility.format_error_backtrace);
end p_set_clid_context_001;
/

create or replace context RND_CLIENT_ID_001 using p_set_clid_context_001;

CREATE OR REPLACE TRIGGER hr.after_logon_trg
AFTER LOGON ON hr.SCHEMA
BEGIN
DBMS_APPLICATION_INFO.set_module(USER, ' Logon Context Initialized');
p_set_clid_context_001(); -- Default value will be Set
END;

create or replace view uom_master as
select * from
SCOTT.uom_master
where UOM_CLIENT_ID = sys_context('RND_CLIENT_ID_001','CLIENT_ID_001');

Insert into uom_master(UOM_CODE,UOM_CODE_DESC) Values
('U005','Unit of Measurement 2');




During this Insert the Trigger of the Table Fires and Fetches the Sys context Value from HR Schema. There is no Context defined in Scott Schema, the following select Statement

Code: Select all

 SELECT *
 FROM
 SESSION_CONTEXT



will return No Rows when run from Scott. But the context is picked from HR Schema where its defined. Though there is No Context defined in Scott Schema. The trigger fired for the view UOM_MASTER in HR is through the trigger RND_TRG_UOM_MASTER defined in Scott. Is the behaviour correct and kindly explain and clarify.

Thanks in advance

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Context Behaviour in Triggers

Postby Tim... » Fri Aug 17, 2012 6:11 pm

Hi.

Contexts are session-specific. You are running the trigger in the SCOTT schema, but the session doing this is connected to the HR schema, so the context information is correct, since your session is HR.

You are querying SESSION_CONTEXT. It says session in the name. Now if it were called SCHEMA_CONTEXT, then I would not expect this behavior.:)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 4 guests