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

Differentiate Database Activity and Front End Activity

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

jakoboski
Member
Posts: 12
Joined: Sun Nov 03, 2013 2:28 pm

Differentiate Database Activity and Front End Activity

Postby jakoboski » Mon Mar 17, 2014 8:57 am

I have a Oracle Forms application using 11G R2.

There is a database table level trigger which is BEFORE_UPDATE and it does update a column to 'CHECKED'. This should only trigger when there is an update from front end application.

We do a couple of other procedures which does update and insert on table and thus trigger is BEFORE_UPDATE, it fires.

Is t there is a way to restrict triggering only when there is a change to rows and should only trigger when user update from front end?

Regards

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

Re: Differentiate Database Activity and Front End Activity

Postby Tim... » Mon Mar 17, 2014 9:44 am

Hi.

So what I'm thinking is you could do something like this.

Put your trigger code into a stored procedure. In the stored procedure, so something like this...

Code: Select all

IF SYS_CONTEXT('trigger_control_context', 'disable_trigger') = 'TRUE' THEN
  NULL;
ELSE
  -- Put your trigger code here.

END IF;


Now you need to create a context in the same schema as the trigger code.

Code: Select all

CREATE OR REPLACE CONTEXT trigger_control_context USING trigger_control_context_api;

CREATE OR REPLACE PACKAGE trigger_control_context_api AS
PROCEDURE set_parameter(p_name   IN  VARCHAR2,
                        p_value  IN  VARCHAR2);
END context_api;
/
SHOW ERRORS


CREATE OR REPLACE PACKAGE BODY trigger_control_context_api IS

PROCEDURE set_parameter (p_name   IN  VARCHAR2,
                         p_value  IN  VARCHAR2) IS
BEGIN
  DBMS_SESSION.set_context('trigger_control_context', p_name, p_value);
END set_parameter;

END context_api;
/
SHOW ERRORS


So by default, your forms session will not have this set and will run the trigger code as normal. For any sessions that need to ignore the trigger, simply do the following...

Code: Select all

BEGIN
  trigger_control_context_api.set_parameter( 'disable_trigger', 'TRUE');

  -- Call your procedure here.

END;
/


Because the parameter is now set, your trigger code will be disabled...

Just a thought...

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

jakoboski
Member
Posts: 12
Joined: Sun Nov 03, 2013 2:28 pm

Re: Differentiate Database Activity and Front End Activity

Postby jakoboski » Mon Mar 17, 2014 9:49 am

Sigh, alas, thanks Tim for the solution.

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

Re: Differentiate Database Activity and Front End Activity

Postby Tim... » Mon Mar 17, 2014 10:11 am

:)
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 0 guests

cron