Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

The Perfect Trigger

in Oracle Application Express #orclapex
by

Scott Wesley

on 24 November 2016

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of The Perfect Trigger

create table srw_demo
(id number generated by default on null as identity
,created_date date default sysdate not null
,created_by varchar2(30) default coalesce( -- APEX5, remote, DB
sys_context('APEX$SESSION', 'app_user')
,regexp_substr(sys_context('userenv', 'client_identifier')
,'^[^:]*')
,sys_context('userenv', 'session_user')
)
not null);
svenweller.wordpress.com/2016/02/24/sequence-and-audit-columns-with-apex-5-and-12c/
The Perfect Trigger
Scott Wesley
There is no spoon
With thanks to Sven Weller
There is no trigger
create table
srw_demo
(id number

generated by default on null as identity

,created_date date

default sysdate
not null

,created_by varchar2(30)
default coalesce(
-- APEX5, remote, DB

sys_context(
'APEX$SESSION', 'app_user'
)
,regexp_substr(sys_context(
'userenv', 'client_identifier'
)
,
'^[^:]*'
)
,sys_context(
'userenv', 'session_user'
)
)

not null);
user
v('APP_USER')
+
(if previous is null)
+
NVL
(v('APP_USER'), user)
regardless of first expression
COALESCE
(sys_context
('APEX$SESSION', 'app_user')
,regexp_substr
(sys_context('userenv', 'client_identifier')
,'^[^:]*')
-- WESLEYS:112358132134
,sys_context
('userenv', 'session_user'
)
)
sys_context( , )
Returns apex_application.g_user
But page items result in a
table query
Another (expensive) query
Not short circuit evaluation
Unnecessary select in trigger
For each row!
Extra object
No direct relation to table
Pre 5.0,
remote query
SQL session = user
Short circuit evaluation
:APP_USER
Internally managed sequence
12c
use if not supplied
if
cheap_check
or

Time taken to insert 1 million rows
no trigger
NVL
COALESCE
What's so bad about a trigger?
So what does no trigger look like?
v('APP_USER')
+
COALESCE(v('APP_USER'), user)
nvl evaluates all expressions
coalesce accumulates
-- (So we can see code in transcript)
create table swe_demo (id number primary key
,col1 number
,col2 varchar2(30)
,inserted_date date not null
,inserted_from varchar2(30) not null);

create sequence swe_demo_seq cache 10000;

create or replace trigger swe_demo_bri_trg
BEFORE INSERT ON swe_demo
FOR EACH ROW
BEGIN
-- record needs always a key
IF :new.id IS NULL
THEN
:new.id := swe_demo_seq.NEXTVAL;
END IF;

-- timestamp of the last changes
:new.inserted_date := SYSDATE;
:new.inserted_from := nvl(v('APP_USER'), user);
END swe_demo_bri_trg;
/
select table_name, sequence_name
from all_tab_identity_cols
alter table my_table modify
(id generated as identity

START WITH LIMIT VALUE
);
References
Short Circuit Evaluation
Synchronise Sequence
Decommission Triggers
www.grassroots-oracle.com/2016/10/decommissioning-triggers-in-12c.html
www.grassroots-oracle.com/2016/10/synchronise-sequence-value-with-12c-identity-column.html
www.grassroots-oracle.com/2010/02/one-more-coalesce-vs-nvl-example-to.html
svenweller.wordpress.com/2016/02/24/sequence-and-audit-columns-with-apex-5-and-12c/
Sequence & Audit Columns
with APEX 5 and DB12c (Sven Weller)
exec reset_seq(
'my_seq'
, 1123581321)
nothing executed if first parameter not null
Short Circuit Evaluation
alter table srw_demo
modify id
default srw_demo_seq.NEXTVAL
;

drop trigger
srw_demo_biur;
create table blah

(
id number generated
by default on null
as identity
,...
Identity Columns replace manual sequence
Need to synchronise sequence with data?
*some triggers have their place
Fetching whom
(if previous is null)
+

expensive_check
then

new tables have better solution
As for the sequence?
.nextval during insert often best
now part of table definition
sagecomputing.com.au
-- Sample code
fin
Sites I Like
apex.world - Community Collaboration - Forums, Slack, News, Tweets, Jobs, Plugins, et al
APEX 5 Page Designer - Adjust Colour Contrast
XPLUG - Browser Extension for Page Designer
http://blog.oratronik.org/?page_id=144
http://www.grassroots-oracle.com/2015/08/apex-5-colour-contrast.html
https://apex.
world
/
community.oracle.com/community/database/developer-tools/application_express/content
OTN APEX Forum - Content page - try perusing a few minutes a day
Learn & Share SQL, for free - great for test cases
https://
livesql
.oracle.com
Oracle Open Source content - "tech rich, marketing poor"
http://www.
oraopensource
.com/
Oracle APEX Feature
Requests
apex.oracle.com/
vote
Oracle APEX
5.1
Early Adopter
apex
ea
.oracle.com
Full transcript