A reader, March 08, 2002 - 8:55 am UTC
"Simulate On_Commit Trigger Behaviour From PL/SQL", version 8.1.7
Cheema, March 08, 2002 - 1:46 pm UTC
Thanks a lot.
I think that is a very good idea and just what I needed.
"Simulate On_Commit Trigger Behaviour From PL/SQL", version 8.1.7
Cheema, March 20, 2002 - 2:11 pm UTC
Hi Tom,
I have investigated the idea of materialized view(MV).
In order to make it work I need to create a trigger on MV,
which I have been able to create. There are no compilation
errors, however, the trigger doesn't seem to fire.
I tried creating an INSTEAD OF Triggers, but this doesn't compile. Probably, beacue MV is like a Table than a view.
Any advise?
Cheema
March 20, 2002 - 3:07 pm UTC
The triggers fire, upon commit, they do fire.
However, I'm confused here - why would you need a trigger - the MV is there to do the work for you.
"Simulate On_Commit Trigger Behaviour From PL/SQL", version 8.1.7
Cheema, March 20, 2002 - 2:31 pm UTC
Hi Tom,
Sorry about my earler e-mail. It was premature.
Trigger is firing, and I should be able to make it work.
Regards,
"Simulate On_Commit Trigger Behaviour From PL/SQL", version 8.1.7
Cheema, March 21, 2002 - 12:56 pm UTC
Hi Tom,
>>However, I'm confused here -
That is because the logic to populate RC(new) tables is complex and requires procedural code. So the trigger on MV calls a package which pouplates RC tables which are real tables.
In fact MV is only a mechanism to invoke the package after a commit in any transaction in which Master table of MV has been modified. That is exactly what I wanted.
Cheema
March 21, 2002 - 5:07 pm UTC
And this shows they fire upon commit:
ops$tkyte@ORA817DEV.US.ORACLE.COM> @test
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;
Table dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop materialized view t_mv;
Materialized view dropped.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int primary key, y int );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create materialized view t_mv
2 refresh on commit
3 as
4 select y, count(*) cnt from t
5 group by y;
Materialized view created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace trigger t_mv_trigger
2 after insert or update or delete
3 on t_mv
4 begin
5 if ( inserting )
6 then
7 dbms_output.put_line( 'Hey, I fired!! for insert' );
8 end if;
9 if ( deleting )
10 then
11 dbms_output.put_line( 'Hey, I fired!! for deleting' );
12 end if;
13 if ( updating )
14 then
15 dbms_output.put_line( 'Hey, I fired!! for updating' );
16 end if;
17 end;
18 /
Trigger created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1, 1 );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec null;
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec null;
Hey, I fired!! for deleting
Hey, I fired!! for insert
PL/SQL procedure successfully completed.
oops!
Yogeeraj, March 22, 2002 - 3:24 am UTC
hi,
sorry, i missed something..
What is this "exec null" trick?
thanks and
Best Regards
Yogeeraj
March 22, 2002 - 9:20 am UTC
sqlplus calls dbms_output.get_lines after every insert/update/delete and EXEC call. sqlplus then prints out whatever it gets.
It does not do this after commit for example.
In order to show WHEN (which statement) caused the trigger to fire, I used exec null; to have sqlplus attempt to dump the dbms_output buffer. If I did not have it after the commit, we would not see it (the messages). I put it after the insert just to make sure that there was nothing up my sleeves.
Stanley, March 22, 2002 - 6:42 am UTC
SQL*Plus receives contents of DBMS_OUTPUT buffer only after executing PLSQL block, not after a commit. Hence, to get messages from trigger that fires when you issue commit, you need to execute some PLSQL block.
"Simulate On_Commit Trigger Behaviour From PL/SQL", version 8.1.7
Cheema, March 22, 2002 - 1:32 pm UTC
>>Hey, I fired!! for deleting
>>Hey, I fired!! for insert
I have found that regardless of the operation(insert/update/delete) on t , the trigger on t_mv is always being fired for delete and insert in that order.
Any explanation?
Regards,
Cheema
March 22, 2002 - 1:55 pm UTC
Out with the old, in with the new.
The refresh isn't done as an update, its done as a delete of stale and insert of fresh.
Trigger on mview - very good idea!
Piotr Jarmuz, August 29, 2003 - 9:04 pm UTC
I had a similar problem and solved it in a more convoluted way.
I used:
create some_function(p_dummy number) return number
deterministic
is
begin
if sys_context('some_app_ctx','signal')='true' then
dbms_alert.signal('some_signal','some_message');
/* this one is associated with the app context */
reset_signal_flag_in_app_ctx;
end if;
return p_dummy;
end;
create materialized view dummy_mview
build immediate
on commit refresh
as
select count(*) cnt from some_table where 1=0
create index dummy_mview_idx
on dummy_mview(some_function(cnt))
So a function-based index on materialized view.
mview and index had just one dummy row(entry).
And whenever someone modified some_table (via stored procedure or instead of... triggers at the sam time saving a flag to send a signal to the listener session later on) and commited then some_function has been called sending signal on a common signal NAME thus avoiding blocking problem with dbms_alert
Sending signal and commiting immediately after reduces the blocking time window...
But your idea is even better!
No need of an index. Simple and pure.
I don't know why I didn't get this idea before?!?!
I seems like it takes some time to learn thinking in your way.
Best regards.
P.S. Hope Oracle 10g will have before commit triggers on schema and/or on database. Seems like Oracle uses internally on commit hooks already (mview refresh, redo log flush, locks release) so it would be nice to expose that functionality to developers (just like you did with dbms_lock, snapshots, jobs, etc). And you could forbid commiting and/or rolling back in such a trigger to avoid infinite recursion...
Does the materialized view refresh occur before the commit finishes ?
Steve, May 27, 2004 - 12:07 pm UTC
Hi Tom,
I tried making this work as a post_commit trigger, but it seems under 9.2.0.4 that the materialized view is updated as part of the transaction, so this is acting as a pre_commit trigger.
are post_commit triggers in 10g?
Thanks
May 27, 2004 - 8:23 pm UTC
it is "on commit refresh", the refresh is part of the commit processing -- part of the transaction.
there are no post-commit triggers
Simulating BEFORE_COMMIT trigger
Zlatko Sirotic, May 28, 2004 - 5:55 am UTC
Simulate "ON COMMIT" trigger behaviour - distributed transactions
Chris Phillips, November 15, 2004 - 1:47 pm UTC
I've managed to get the "ON COMMIT" trigger simulation working using an MV refreshed on commit and a trigger on the MV. However when this solution was put into a distrbuted transaction environment I then hit the Oracle bug 1385495, so it doesn't work in a distributed transaction environment. Bug states:
Please be aware of the following: . As per this bug tables with "ON COMMIT" snapshots / MVs are not allowed to be updated within a distributed transaction. This INCLUDES updates from an XA or other globally coordinated transactions.
November 15, 2004 - 8:58 pm UTC
but that'll be "not a bug" (all things are "bugs" -- but not all bugs are bugs).
on commit refresh is not supported in distributed transactions.
I wanted a method to ensure I dont enqueue a UN COMMITED change
A reader, April 13, 2006 - 4:17 am UTC
and this gives me a method I can use as template for my problem.
/Hans
Interesting..
Menon, May 24, 2006 - 8:10 pm UTC
Hi Tom
I wonder whether we can solve the same issue (creating an "on commit trigger" by submitting the "trigger logic" using dbms_job. (Similar to your idea of making "emails transactional".
May 25, 2006 - 1:30 pm UTC
it would fire after the commit
and it could be submitted over and over (you'd have to come up with the logic to make sure it was submitted only once - it could be done using the transaction id)
thanx!
Menon, May 25, 2006 - 2:57 pm UTC
"it would fire after the commit"
Yes - the requirement is to have "after commit trigger" on commit. But you are right - the commit will go through regardless of the dbms_job.
"and it could be submitted over and over (you'd have to come up with the logic to
make sure it was submitted only once - it could be done using the transaction
id)
"
I must be missing something as I was thinking of submitting for a one-time execution using using dbms_job?
May 25, 2006 - 5:51 pm UTC
if you are using triggers to do this, you would have to have logic to make sure only the "first" trigger in a transaction did this.
use trigger on materialized view
rain, January 03, 2007 - 4:25 am UTC
Hi Tom ,
We have an demand that sync text index real time.
so i want to use materialized view characterization to achieve.(I don't want to use the drbgdml.sql)
example:
create table q (x int primary key,y int name char(20));
create index q_ix on q(name) indextype is ctxsys.context;
create materialized view q_mv refresh on commit
as
select y, count(*) cnt from q group by y;
create or replace trigger q_mv_trigger
after insert or update on q_mv
declare
pragma autonomous_transaction;
begin
ctx_ddl.sync_index('Q_IX');
end;
because normal trigger can't execute implicit commit,
i use an autonomous_transaction trigger to execute ctx_ddl.sync_index.
when i update/insert q then commit,the q_mv can refresh on commited.
so i assume even if the trigger use another session to execute sync_index,
it should be see the commited data and exec sync_index successfully.
but i don't understand why it no action!!
thanks
Regard
January 05, 2007 - 7:55 am UTC
that won't work in an autonomous transaction, the new data isn't committed in the table, the autonomous transaction cannot see it yet.
10g has this feature to do this, as part of the create index syntax. Else you should use a stored procedure to modify this table and call sync index from there.
Don't try magic - it'll bite you bad every time.
use trigger on materialized view ~~
rain, January 07, 2007 - 10:39 pm UTC
Hi,Tom
Thank your valuable reply.
I still have some unclarity above.
The materialized view created refresh on commit,
When the table committed, the mv just refreshed , right?
Saying an after trigger on this mv, in reality this trigger do some thing after that modified table.
(just use the mv's refresh commit characteristic)
In my point of view, the flow path is :
modify table-> "enter commit" -> mv refresh -> trigger fire to do some thing about modified table
So,when the trigger that on mv is firing, in this time, the mv had refreshed already,
the table also had committd already. Both mv and table should be visible for everyone.
Does it right, please correct me.
thank regard
January 08, 2007 - 11:57 am UTC
with a fast refresh on commit materialized view, yes - when you commit, the changes are applied.
but your hypothesis is incorrect. The MV is refreshed during the commit as part of the commit and no changes are visible to other sessions until the commit is done.
/oramag/code/tips2002 removed?
Zlatko Sirotic, March 24, 2007 - 4:09 am UTC
March 26, 2007 - 7:19 am UTC
ugh, those first triggers (stopped reading at that point) - they do not work in real life (more than one user)
i know that this causes serialization
Zlatko Sirotic, March 26, 2007 - 12:07 pm UTC
First triggers in
1. Solving "COMMIT rules" without Deferred Constraint Checking
or first triggers in
2. Solving "COMMIT rules" with Deferred Constraint Checking
or both?
I know that this causes serialization, but:
1. Let the database enforce data integrity (Effective Oracle by Design, page 368)
2. Client-side enforcement of constraints is problematic at best, if you can do it at all (page 369)
3. To solve the problem, we can use serialization and server-enforced integrity (page 372)
Best regards,
Zlatko Sirotic
March 26, 2007 - 2:09 pm UTC
I hate triggers - when they do not need be used.
And they did not here
And the supplied triggers did not cover all of the bases. And they do not force that the data is left consistent at the end of the transaction - the "client" must come along and do that.
If I do not have to use triggers - I will not use triggers. If you supply well formed transactional API's, the "need" for triggers becomes very dubious.
Toon K., March 26, 2007 - 3:49 pm UTC
>> If you supply well formed transactional API's,
>> the "need" for triggers becomes very dubious.
I'd like to state something similar, yet slightly different:
"If the DBMS would provide us with full declarative support for data integrity constraints, then there is no need for triggers."
(I'm using *data integrity constraints* here in the relational-DBMS-theory sense, not in the what-are-SQL-DBMS's-giving-us sense).
No matter how well you design your transactional API's, I find you will always end up either with:
- copying code for integrity enforcement accross these API's (which you won't when this code is dealt with by triggers), or
- a sub-optimimal implementation in terms of the resulting degree of concurrency due to serialization that must be done (again which you won't get when using triggers in a smart way).
Toon.
...I know, I know, you want examples...
The method solves "COMMIT rules" completely on the DB level
Zlatko Sirotic, March 27, 2007 - 4:16 am UTC
"And they (triggers) do not force that the data is left consistent at the end of the transaction
- the "client" must come along and do that."
Yes Tom, this is true for:
1. Solving "COMMIT rules" without Deferred Constraint Checking
"If Forms (or some other client tool) didn't give that command before the transaction COMMIT,
a value "F" (False) would be left in the DEPT table rows.
In this way, the client must cooperate with the database."
(seams that you stopped reading at that point)
but this is not true for:
2. Solving "COMMIT rules" with Deferred Constraint Checking
"The method that allows you to solve "COMMIT rules" COMPLETELY ON THE DATABASE LEVEL
is the ability to delay the checking of the declarative constraints ... until the commit."
My motto is:
"Thou shall not do in the Middle Tier, what thou could have done in the Data Tier" (Toon Koppelaars).
I agree with Tonn that:
"If the DBMS would provide us with full declarative support for data integrity constraints,
then there is no need for triggers.".
Best regards,
Zlatko Sirotic
March 27, 2007 - 9:33 am UTC
yes, i stopped reading an article titled
Solving "COMMIT business rules"
when the first part was all about the application doing it with only partially implemented triggers. Sorry - but I couldn't get past that.
MV trigger
A reader, December 22, 2011 - 11:57 pm UTC