Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: April 20, 2010 - 6:03 pm UTC

Last updated: January 16, 2018 - 1:03 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Tom,

thanks for your devotion to duty in answering the world's Oracle questions.

I am struggling to explain to colleagues precisely why triggers are a bad idea in Oracle. I have read your article entitled "The trouble with Triggers" and it says it all, and I agree completely with it.

Nonetheless, I struggle to wean colleagues off triggers, and get them to understand why stored procedures are so much better (bind variables anyone?).

Other than the above mentioned article, do you have (or know of) authoritative sources of information about the trouble with triggers?

For myself, the fact that they make a database non-deterministic suffices to comdemn them utterly, but this does not impress managers who just want something delivered tomorrow....

What do you recommend as a technique for convincing colleagues about anything to do with Oracle? I "bang on" about stored procedures, using SQL instead of PL/SQL, Analytics, and just come across as a DBA "having a go" again about something. I even have to make the case for constraints on fields! Explaining that the application is NOT the only thing that will ever access the data just seems like throwing marbles at a steel wall.

Your reputation must help in this regard, as nobody using Oracle dismisses Tom Kyte lightly. Sadly, us mere mortals don't have your 'street cred' so, in terms of influencing folk, and getting them to see the light about Oracle best practices, can you recommend anything?

Please do bear in mind, that where I work, a DBA is irrelevant to Oracle decision making, until something goes wrong, and then it's his problem all of a sudden.

and Tom said...

well, bind variables would not be a reason - triggers do binds just as well as stored procedure - since they are the same language and all..

Other than the above mentioned article, do you have (or know of) authoritative sources of information about the trouble with triggers?

I guess the only other authoritative source will be.....

experience.

I used to think triggers were the coolest thing ever. The. Coolest. Thing. Ever. In fact, I stated that in
https://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html

Then I got older, I saw the ramifications. In short, I learned from my mistakes. Unfortunately, I didn't have a generation before me to learn from before I made them with triggers (triggers were brand new when I started databases - Sybase was the coolest thing ever with triggers - v7.0 of Oracle became cool simply because they (triggers) existed)...

I'm reminded of these articles now - I was going to blog on them, but they apply here:

http://www.skorks.com/2009/09/does-software-development-have-a-culture-of-rewarding-failure/

Isn't that so very true - very very very true. I know it is true because unfortunately, I receive a lot of kudos for coming in and "laying hands on" and sometimes being able to make some rube goldberg machine (a software design) appear to work (i make it work a little better, but never really good). No one wins awards for coming in on time, under budget and exceeding expectations. They get awards for working 100hours a week 'fixing' something that should never have been done in the first place.


Then there is this:
http://www.skorks.com/2010/04/on-the-value-of-fundamentals-in-software-development/

How true - learn the fundamentals - yet how often do your developers read the concepts guide? (or even how many of your developers have HEARD of the concepts guide??)

What do you recommend as a technique for convincing colleagues about anything to do with Oracle? I "bang on" about stored procedures, using SQL instead of PL/SQL, Analytics, and just come across as a DBA "having a go" again about something. I even have to make the case for constraints on fields! Explaining that the application is NOT the only thing that will ever access the data just seems like throwing marbles at a steel wall.

I just keep on "banging on" and banging and banging. I think this is why everything in real life devolves into a "hard right and a hard left". There is never a middle, just polar extremes.


Perhaps start keeping a list - to prove that history keeps repeating itself. It is hard in a meeting at the drop of a hat to say "I told you this last time on March 15, 2009, before that we say this same exact mistake in oct of 2008, prior to that....." and so on.

It takes experience and sometimes a bit of "we've been here over and over again" - typically in front of people a layer or two up.


a DBA is irrelevant to Oracle decision making

I would be utterly frustrated in such an environment - I've had the pleasure of not having to be in that situation too often and when it got to the point where it was getting to be that way - I simply left.

I didn't leave for a better job, I just left. I figured that if the place where I spent most of my waking hours was going to be a place that made me just want to scream, I probably shouldn't be there....


It is funny when I go back and talk with some people from the olden days (who are all older now). Hindsight is 20/20 they say and it is never more true when they are faced with their design decisions of yesteryear.

Rating

  (12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alexander, April 21, 2010 - 11:01 am UTC

Never in my many years of reading this site have I read a post or question that I can relate to as much as John's. I swear we might be working for the same company.

I have looked all over the place to find documented "best practices" for exactly the reasons John states; We are mortal DBAs that often get as much respect as the janitors. For example, putting 1 instance per host, other than Tom, try finding an Oracle doc that says that's how Oracle is designed to work.

Another near impossible thing to prove to those people that make the decisions, is the work and dollars associated with supporting a piece of crap. Our hours and hours of extra work don't matter, we get a salary, done.

I feel I can apply this example with triggers do virtually everything I do that involves a design decision using Oracle. Obviously, we common folk would have a much tougher time finding a new job. But to be honest Tom, I'm starting to get the feeling this is how the world works, and it doesn't matter how many times I switch.....
Tom Kyte
April 21, 2010 - 11:37 am UTC

... Obviously, we common folk would have
a much tougher time finding a new job....

everyone forgets that I was common folk. I wasn't "asktom" until this century.

http://asktom.oracle.com/Misc/this-site-owes-me-new-monitor.html

great article

Wally, April 21, 2010 - 12:09 pm UTC

Thanks for your insight Tom.

Alexander, April 21, 2010 - 1:56 pm UTC

"everyone forgets that I was common folk."

Did people listen to you when your were?
Tom Kyte
April 21, 2010 - 2:26 pm UTC

many times, yes.

sometimes however, apparently they did not since I had to leave...

It is my firm belief that if you are in a situation that is just driving you crazy - it could be time to look elsewhere.

corporate

mahesh, April 21, 2010 - 11:42 pm UTC

Most of us work in corporate world, where efficiency, maintainability and cost savings are always not on priority.
I relate the corporate world to my family- when we went to mall, my three year old daughter asked for a toy, we tried hard to convince her- but she want it-so we purchased – knowing that it will cost and not suitable for her. But we do buy against our wish. Isn’t it the same?

Most important is to keep relationships in order than appropriate technology /design/solution/cost.

Tom Kyte
April 22, 2010 - 8:05 am UTC

... we tried hard to convince her- but she want it-so
we purchased – knowing that it will cost and not suitable for her. But we do
buy against our wish. Isn’t it the same?
...

no, because you do that out of affection - care - love even.



and you do this:

... where efficiency, maintainability and cost
savings are always not on priority. ...

out of "not smartness", care, consideration - has nothing to do with it.


The corporate world is where efficiency, maintainability and cost savings are paramount - if you want to stay in the corporate world.


... Most important is to keep relationships in order than appropriate technology
/design/solution/cost.
...


hah, but did you read the above - these are some defective, abusive relationships then.

Agree

Tom, April 22, 2010 - 9:10 am UTC

The reason we have to deal with this on a daily basis is due to the fact that many people in our fields don’t continue to keep up with the latest techniques and best practices by reading AskTom, studying books, benchmarking, learning from others…
Developers I work with tell me they don’t have time to learn and implement the techniques that we have learned from AskTom and his great books. They always say “yeah, yeah I know about AskTom”. I tell them that if they kept up with the best practices they would have plenty of time, due to the fact that they would not be writing as much code, spending as much time debugging… The battles continue and I am becoming exhausted with battling the entire enterprise. Like Tom has noted above I also may need to quit my current position due to the organizations’ rules.
Here are a few of the things that I am championing on behalf of my fellow Oracle developers:
1. Can we PLEASE have the “SELECT_CATALOG_ROLE” in the development environment so that we can see the V$tables …
2. Can the DW team stop using a self imposed rule that all code must do “bulk collect” as their only method for loading data (ignoring insert append no logging techniques. Create Table As).
3. Can the DW team update their general load APIs to stop imposing general rules that don’t make sense like “automatically building Bit Map indexes on every low cardinality column i.e date fields, numbers, everything that is low cardinality)
4. Why can’t we have enough space to get our job done? Space is cheap but they will not give us enough space to stage data in tables. We cannot have 2 copies of large tables in the staging area at the same time. Instead they are going to force us to use a slow by slow method.
5. I am in a constant battle with the Java guys, trying to convince them that SQL belongs in the back-end not the middle (I have a meeting next week with an architectural committee on this issue. If anyone has a good presentation to show them why code belongs in the back-end please post and I will incorporate into mine.)
6. I am constantly battling my fellow PL SQL developers over standards that hinder readability (schema. schema_name_table_name, schema. schema_package_name, all_tables end with _TBL, all procedures and functions in a single package for the whole application …), developers still utilizing cursors throughout their code for everything, minimal or no utilization of Case statements or Oracle Analytics.
7. I am constantly battling management over a “No talking with the end users rule”, how on earth are we ever going to get the applications right if we are severely restricted from communicating with the end user groups?
8. ….
I would have already quit but it seems to be this way everywhere I go! Career change?

Don't have time

Ben, April 22, 2010 - 9:28 am UTC

If you don't have time to do it right, you must have time to do it again.

Agree

Vic, April 22, 2010 - 9:41 am UTC

Why don't you get a copy of Tom's book or ebook 'Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions'

Tom goes into why and how and how not to develop applications. He goes into great detail as to why the database should 'do the work' I'm sure he won't mind if you quote him with his permission.

Follow Up

Tom, May 13, 2010 - 10:21 am UTC

As a follow up to my ranting above. I met with the Architects from one of the largest IT consulting companies in the world last week in Reston, VA. I was there to try to make them understand that SQL belongs in the Oracle database and not in the Java middle. I presented everything I had including many of the threads from Ask Tom. I was informed by the so-called “Architects” that SQL needs to be in the middle so that the applications may scale. This company is building hundreds of applications for the government with 0 sql in the Oracle database. I asked them to please call me when the performance starts to be a problem and I will come back to try to assist them once again.

The trouble with Triggers

Patrizia, May 20, 2010 - 5:45 am UTC

Hi Tom, this is my 'first time' about asking you. I tried to find your article about triggers, but i didn't. Please, can you link it ?
Thanks and regards
Patrizia
Tom Kyte
May 24, 2010 - 11:34 am UTC

https://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html

first hit on google (en) with a search string of

trouble with triggers

Asking for trouble by using a trigger submit a job to avoid a mutating table challenge?

Hein, September 06, 2011 - 4:03 pm UTC

Is it reasonable to have a trigger submit a job to avoid a mutating table challenge for (low frequency) changes?

I'm working on an application with a table of 'people'. (naming changed to protect the innocent)
Columns are person_id, parent_id, family_id, name, and such, with GUIDs.
There was a performance problem with a frequently (millions/day) executed
query using CONNECT on the main table to report up/down hierarchy
for a given person within a family.

A real solution of course involves reducing those millions of executions
each day (most are done in the context of a COLLECTION ITERATOR PICKLER FETCH).

As a (temporary?) workaround I have put a 'helper' table in place which
materializes the immediate family structure for each person (up, and down)

This perfomed 30x faster, but of course adds the challenge to keep the helper table in sync.
There is a procedure to update the helper structure for a specified family
My initial, naive, attempt was to add an INSERT trigger to execute that update procedure.
Of course the generated "ORA-04091: table is mutating..."

My suggested solution for that is not to execute the helper update procedure directly,
but instead to submit a job to do so, when the insert transaction commits (once per day or less)

It seems to work.
Do you think this is a total hack, or reasonable usage of trigger and job?
Any and all feedback appreciated!
Regards,
Hein


CREATE OR REPLACE TRIGGER persons_helper_insert_trigger
AFTER INSERT ON persons_table
FOR EACH ROW
DECLARE
L_JOB number;
BEGIN
-- When the job below fails, information about the failure is recorded in a
-- trace file and the alert log. Oracle writes message number ORA-12012 and
-- includes the job number of the failed job.
--
DBMS_JOB.SUBMIT
(JOB => L_JOB,
WHAT => 'persons_helper_table_load ( hextoraw (''' || :new.family_id || '''));');
END;
/

Tom Kyte
September 07, 2011 - 8:31 am UTC

It seems to work.
Do you think this is a total hack, or reasonable usage of trigger and job?
Any and all feedback appreciated!


It seems reasonable - but I would change the way the job is submitted. right now, it is NOT bind friendly. I would do this:

declare
   l_job number;
begin
   dbms_job.submit( job => l_job, what => 'persons_helper_table_load( JOB );' );
   insert into persons_helper_table_load_parms ( job_id, family_id, ... )
   values ( l_job, :new.family_id, .... );
end;



and then in the persons_helper_table_load - you would have:
procedure persons_helper_table_load( p_job in number )
is
   l_rec persons_helper_table_load_parms%rowtype;
begin
   select * into l_rec from persons_helper_table_load_parms where job_id = p_job;
   ... your code ...
   delete from persons_helper_table_load_parms where job_id = p_job;
end;




that way, you only submit ONE 'what' input - it is constant, always soft parses...

Trigger trouble

Patrick, December 28, 2011 - 3:45 pm UTC

hi
(don't know if this is good enough for the site)
some bad existing situations found in a legacy application:
influencing existing trigger :
begin
a_package.set_var(1); --set a package variable
update table a_table
set
where
a_package.set_var(0);
exception
when others then
a_package.set_var(0);
raise;
end;
trigger on a_table :
if a_package.get_var = 1 then
do_something;
else
do_normal;
end if;
Procedure is better: new input parameter i_a_parameter and test on it.

2. common use of mutating table via 3 triggers and a package pl/sql table.
years of problems with restart: pl/sql table filled in BR trigger didn't get rollbacked (thanks Tom for the tip in your book), so double entry.
3.Large (many columns) table updated in different processes : many mutating table entrys in the trigger to process the different cases : IF field x updated then mutating table action "process_x", ... If you read the trigger then you can not see the entire functional flow.
4. update table a, in trigger update cumul table, in trigger on that : update cumul table higher level table...
so procedure must only update table a, and the other tables are automatically updated. one can not program to use FORALL on the related tables. eg. do the processing of table a, postpone the updates of the cumul tables to do just before commit whith forall. It is also not possible to put an order on the cumul rows, to prevent deadlock.
5. "We have a problem we can't solve find but we think the triggers play ping-pong".

Triggers take away your freedom. It's harder to program new functionality

Trouble with Triggers | Asktom Column oracle magazine

Rajeshwaran, Jeyabal, January 16, 2018 - 9:44 am UTC

Team,

was reading this AskTom column from Oracle magazine online.
http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html
it has references a couple of links to Tomkyte's blog, however we are unable to access those blog link.

could you help us with that contents?

tkyte.blogspot.com/2005/08/something-different-part-i-of-iii.html
tkyte.blogspot.com/2005/08/part-ii-seeing-restart.html
tkyte.blogspot.com/2005/09/part-iii-why-is-restart-important-to.html

Also this link (asktom.oracle.com/tkyte/Mutate) referred in the online magazine is not working, could you help us with the right link?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library