The dreaded "when others then null" strikes again...
People think sometimes that I must be able to read the mind of "software" because they'll say "I'm observing X" and I'll respond with "you probably did Y" - and many times that guess is right.
I cannot read the mind of software - I can however recognize patterns, especially when I see the same bad pattern over and over and over and over again.
Another "when others then null" guess. I hate "when others". Wish it did not exist for all of the problems it causes.
Things I don't like
- generic implementations that are not necessary
- triggers
- when others (not followed by raise!!)
- triggers
- not using bind variables
- triggers
posted by Thomas Kyte at 10:54 AM
72 Comments:
I would think that Not Using Bind Variables would scores higher than Triggers on this list...
this is not a stack ranked list :)
declare
car varchar2(20) := 'Pontiac';
begin
drive_car_down_road(car);
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/
ORA-XXX: Bridge is out.
Abnormal Termination of Programmer.
Tom,
I'm surprised you don't like triggers. Can you give me your reason(s). I find them quite helpful with enforcement business rules and creating / keeping history tables.
-Rob
I'm surprised you don't like triggers.
Because I hate being surprised or tricked.
And triggers are all about trickery and surprises.
Far far far FAR too many people try to do "everything including the kitchen sink" processing in triggers. "Hey look, you insert the number 1 into this table and 5billion side effects happen automagically in the background"
I find triggers to be improperly implemented. People THINK they are enforcing integrity - but they are not. Show me a trigger that enforces a rule ACROSS ROWS in a single table or ACROSS MORE THAN ONE TABLE - and I'll show you a trigger with a big bug in it.
People try to "fix or patch" an existing system by trigger magic. Makes the system wholly unmaintainable. You forget about those triggers and bad things happen.
They are used improperly so often, that I'd rather live without them - then have them there for the very very very very few times they could truly be useful.
That is, I'd rather give up their very small benefit - for safety and sanity reasons.
> That is, I'd rather give up their very small benefit - for safety and sanity reasons.
---
I'll concure with that. But I'll give you a good use. We have an application developed by a group of java programmers who believe the database is a black box. (don't start me on that one) Anyway - the end client needed a history of user logon activity. I could have passed the requirement to the java programmers, waited for weeks for the java code to be written. This is the same group that wanted to put auditing / security in the java application.
I spent an hour wrote a couple of triggers that fit the users needs. Job done.
Later,
-Rob
I expected not using bind variables to lead the list. I was surprised for not liking trigger either. What would you use/suggest instead of triggers?
Let's say you have a table and, for lack of better software design, you have fifteen or more forms that runs based on that table. What would you use to track which user changed what?
Ops. I guess I just turn the blog into asktom
Sorry !
Gleisson Henrique
Rob said.....
But, you see, if I had my way, Java programmers would stay java programmers and would never be allowed to pretend to be database developers.
And the only code they would be able to submit to the database would start with "begin" or "declare" - no insert, update, delete, merge at all.
That is, they would invoke transactionally correct stored procedures coded by someone that actually understands the database.
Hence, the need for the trigger would disappear again.
But for the history of user logon - seems "audit" would do it?
Same for Gleisson's comments, transactionally consistent APIs in the form of stored procedures that do the right thing. No need for "side effects that happen by magic"
I think triggers are the best solution for the following:
1. populating a sequence based column (primary key).
2. populating a history table
These are very common in applications.
I can picture Tom slowly pounding his head on his desk saying, "why did I post that? Now everyone is going to give their opinion on triggers."
If it's any consolation, I remember many years ago writing a piece of code that included "when others then null;" and promptly being whacked with a boat paddle.
Later,
-Rob
Tom said - But for the history of user logon - seems "audit" would do it?
---
Well in a normal well designed app I would agree. But sadly all auth. is done through the java app. Therefore I had to capture the user who populated a table then write that to a login history table.
Of course this group wrote a piece of code that takes 20+ hours to run. It generates a sql statement and passes it to the database to process one row.
I rewrote the code as a package and it runs in two minutes. I have been trying to educate our java developers about set operations. I am still in a battle for my package to be put into production. The arguement; then the application will not run on all databases.
Is it any wonder my hair is gray.
-Rob
Triggers - ugh! This week we've just implemented a bought in system that basically does everything with triggers. ALL foreign key relationships are held together with triggers. It's horrible!!! (and performs like a dog).
I rewrote the code as a package and it runs in two minutes. I have been trying to educate our java developers about set operations.
This is why when you are forced to use systems that don't understand bind variables and such, it is better to use third party versions than in-house. When I tell my vendor that the allocation process will now run in the database using my package.....they comply. Getting in-house idiots to do the same is a whole different ballgame.
Sure, it's always better to use properly implemented stuff, but reality and politics often dictates otherwise.
triggers are a blessing and a curse. One such curse occurred today-We have an app that uses Designer's Table-API, and somehow an unhandled exception was thrown. For whatever reason, a check constraint was violated but we were never told what the table/constraint was. We're looking at a call-stack about a mile long. If we didn't have the triggers/TAPI in place, we would have received a simple ORA-01400 with the schema/table/field names.
Why overcomplicate?
Having said that, I like triggers that modify the current row (and the current row ONLY!)
Hi Tom , though I read Ask Tom and your Blog religiously I never write a comment or review due to the fear that I am a Novice Programmer.
But the mention of triggers thrice in your Hate list and the substantiation for the same in the comments was really self explanatory. But for generic implementations that are not necessary , here how to decide how far we can implement generic queries or procedures. Is there any limit on the same.
I think there is another one that should top your list, and that is:
* Blindly following any kind of "rule" like :
[and then your list].
Hariharan said...
My approach is: generic code is the path last taken. Only if and when it is absolutely proven out that it would save so much in development and maintenance would I go that way.
Which is "hardly ever"
For many, 'generic code' (that is never actually reused) solutions are the first path taken (along with database independent code...)
believe me or not:
you can educate java developpers !
( I tought them the only code they need and they are allowed starts with "begin" or "declare" - no insert, update, delete, merge at all. And the transactionally correct stored procedures are coded by someone that actually understands the database.)
It works and it is great
(for the java developpers *AND* for the database developpers)
Beside that I *love* triggers.
so what about triggers ?
;)
When the Java programmers want to use an object/relational framework like ADF Business Objects, or Hibernate or TopLink or EJB 3.0, those frameworks are easiest to use when they can do SELECT, INSERT, UPDATE and DELETEs. To make the frameworks call table APIs instead is possible, but requires more hand-coding, hence more opportunity for getting it wrong. Tom- what do you think of Views with Instead of Triggers rather than table APIs?
I've written about 'table' API's before.
The only TAPIs I believe in are....
TRANSACTIONAL APIs
Not table APIs. Table APIs are just as bad as insert/update/delete privileges, they do not solve the underlying problem that most developers of GUI programs do not understand, nor do they care to understand, what a transaction is, what a database does, how concurrency controls work. They view the database as an (un)necessary evil.
I don't think they should be allowed to touch it directly.
Hi Tom
I agree with you totally on Triggers being abused.
However I do find them very useful for maintaining tracking information on tables like last update date, updated by user etc.
I done see how this can be done effetively without triggers.
I done see how this can be done effetively without triggers.
Transactional APIs
they answer it all. Consolidate, centralize the data access code - put it into transactions. Transactions are written as stored procedures. Stored procedure does the right thing...
In your response as to why you hate triggers, I wrote a little query on our database schema
SQLWKS> select max(line) from
2> (select max(line) line ,name from user_source
3> where name in (select trigger_name from user_triggers)
4> group by name)
5>
MAX(LINE)
----------
1479
I thought you'd get a kick out of that.
-Scott
At least 'table' APIs have the advantage that all the SQL is contained in the database, where a database developer can inspect and refactor (optimize) it, instead of being dispersed in gazillions of client source files.
I assume of course that the definition of 'table' API includes also SELECTs, even if spanning more than on table (joins).
I agree with Tom again!
I've seen applications where they insert and it is just a nightmare trying to troubleshoot the trigger order. But it does have it's uses.
I would say for auditing, look into streams. It's a non intrusive way to capture changes (Change Data Capture)...
Also on Transactional API's there should be programs that the Java Developers call, for example, ADD_USER (Inputs). Create_Record (Inputs, Inputs1...) Delete_Order, Change_Order etc.. You get the idea : )
You forgot autonomous transactions! You hate those too, right?
I know you hate triggers, but I know you love data integrity, and sometimes triggers are the best way of achieving that (in my opinion). Sometimes triggers are the lesser of two evils.
Our application is riddled with triggers and they have caught me out many many times when trying to troubleshoot data issues, but our audit triggers have proved their worth time and time again. Can you suggest how data auditing could be better implemented? Example: staff member updating their own credit limit?
Scott said...
...
MAX(LINE)
----------
1479
I thought you'd get a kick out of that.
Perhaps, but I'd say someone's definitely due a kick because of that! ;o)
A lot of developers (including myself) use triggers to get the next sequence value during row inserts. What would be the alternative solution for this ?
Matthias
I have very little experience with triggers...places I ever worked hardly used them or I didn't deal with that part...guess I'm "lucky" huh ?
Robert Vollman said....
Robert - I'd be very interested in see an example of a data integrity constraint that is better implemented via a trigger than a check constraint.
And if you really mean "entity integrity" (crosses rows in a table, crosses tables) - then I'd really be interested in seeing the code (so I can tell you of the bug that inevitably exists in such triggers!!!!)
Scott said....
I would suggest...... as I suggested above.....
TRANSACTIONAL APIs. No need for "automagic triggers"
As I said, the bad done via triggers so far outweighs any good ever accomplished (yes, everyone - EVERYONE - has their "yeah-but" story with triggers - we all start with "man, the pain triggers have given me, yeah, but, once I used a trigger in a good way...) that I would rather forgo having them for safety reasons.
Since auditing is a program requirement (not an after thought to be glued on AFTER the fact), it absolutely belongs right there in the transaction itself!!!!!
Matthias Hoys said...
insert into t (primary_key,....)
values
(seq.nextval,......)
so few extra keystrokes.
so much less ambiguity
so much more performance and flexibility...
My example above is a forms application - you can code your auditing logic into your on-insert or on-update forms triggers - but I fail to see how this differs from an rdbms trigger. Do you suggest all form updates should call a pl/sql block which explicitly updates and audits?
My example above is a forms application - ... - but I fail to see how this differs from an rdbms trigger.
one saying pops into mind right now...
WUH-OH
you do not see the difference between
a) a 'trigger' on an 'even' in a GUI (eg: when new record, on-commit, what ever)
b) a trigger on a database table
?
I am a delphi programmer and I like the idea of using Transactional-API. When I need a list of persons, I go to the DB-Programmer and say give me a procedure that will return this kind of list.
Who control the commit? The Application or the stored procedure?
When I want to use more then one stored procedure as a combined transaction should then their a new stored procedure which do all the stuff for me?
If yes, how can I avoid a real parameter horror?
Who control the commit?
I firmly believe the application is the only one that knows when the transaction is done.
Say you have a "change address" process and a "change phone number" process.
You might want to have some situations where change address is the transaction, others where phone number is the transaction and yet others where BOTH are the transaction.
The application should control that.
Thomas said:
[[And if you really mean "entity integrity" (crosses rows in a table, crosses tables) - then I'd really be interested in seeing the code (so I can tell you of the bug that inevitably exists in such triggers!!!!)]]
I'm curious if the way we have been doing triggers to implement constraints is flawed.
So I'll take the challenge and provide you with a trigger-based implementation of a "constraint accross rows".
You provide the constraint.
Toon
You provide the constraint.
take your pick, you seem to indicate you have some constraints that cross rows (eg: average salary by department cannot exceed X) or tables (eg: foreign key like constraint)
Take one of your "real" bits of code, let us have a look.
I can't use real code, since we use a framework to generate these triggers, which in turn use framework API's again.
I'll take the "avg sal per dept" and script the code down to the bare mininum so that it's absorbable (is that a word?) within 10-15 minutes.
Give me some time (a day or so): shall I post it here?
Toon
sure... post away
now the fun is starting ...
Okay, okay, TRANSACTIONAL API, not Table API - point is well taken. But no-one has responded to my comment that J2EE frameworks are easier to use when you can let them generate code based on a table or view, not on an API. So my thought is to execute at least part of the API from Instead of triggers on views. What do you think?
Perfect! I saw Toon's Hotsos presentation and then read this blog post. I wondered what Tom would have said if he had been there. (I can see the WWTS bracelets selling at Hotsos 2008 :p )
Now it will be like you were there.
John Flack's comment about frameworks is very salient. With a few queries a framework can figure out how to write an appropriate insert statement for a given table. It CANNOT determine which procedure to call to do the same thing. Having a lot of monotonous code written by the framework frees up the developer's time to write 'value-added' code.
If pl/sql provided a way to declare that this package does the CRUD for this table, then you could have common frameworks interact with transactional APIs and have the best of both worlds.
In general I hate frameworks for the same reason you hate triggers, lots of stuff happens that you have little knowledge of or control over, but .Net and Java guys can't seem to live without them.
Curious if you know of pervious requests for this kind of enhancement to pl/sql.
In my Hotsos presentation last week I discussed creating database API's for the Java frameworks: and yes a lot of them are SQL-minded, not procedure-call-minded. However what I showed is that the SQL-API should be based on "UI-rows". And a UI-row is often not a (single) table-in-your-db-design-row. Creating views that surf-up the UI-rows and accept "UI-row DML" (through instead-of triggers) is how we use these Java-frameworks. And it works just fine.
Toon
PS. Trigger code should follow with in the next hour or so.
Hi Tom,
Here it is, pl/sql trigger-code to implement the constraint accross rules:
the average salary by department cannot exceed X (3000).
I tried to keep it as short as possible.
The constraint is named C1 in the code.
- I'm assuming table EMP is already available.
- Row triggers maintain the "transition effect" (TE) of a dml-statement. Using a temp global table for that.
- After statement triggers validate the actual constraint (they use the TE). These will only validate when the dml is such that validation is required. In this case the constraint needs no validation when deletes occur against EMP. And only when certain types of updates occur.
- Of course the lot has to be serialized: using dbms_lock to get that done.
Really curious where this code is flawed (I'm aware of one flaw...).
(wasn't able to use the pre-tag, so it's all unindented code)
Toon.
PS. I promised Lex to show you this approach: I had to finish our book first though.
The last chapter deals with this stuff. That one hasn't gone into copy-edit yet, so I can still change it...
======================
Rem
Rem Transition Effect Table.
Rem
drop table EMP_TE;
create global temporary table EMP_TE
(DML varchar2(1) not null check(DML in ('I','U','D'))
,ROW_ID rowid
,SAL number(7,2)
,DEPTNO number(2,0)
,check(DML<>'I' or ROW_ID is not null)
,check(DML<>'U' or ROW_ID is not null)
,check(DML<>'D' or ROW_ID is null)
) on commit delete rows
/
Rem
Rem Triggers to maintain transition effect
Rem for constraint C1.
Rem
create trigger EMP_BIUDS_TE
before insert or update on EMP
begin
-- Reset transition effect for every
-- dml-statement execution.
delete from EMP_TE;
--
end;
/
create or replace trigger EMP_AIUDR_TE
after insert or update on EMP
for each row
begin
-- Conditionally maintain the transition effect.
if INSERTING
then
-- Only store 'pointer' to affected row.
insert into EMP_TE(DML,ROW_ID) values('I',:new.rowid);
elsif UPDATING
then
-- Only save entry if involved columns
-- are updated.
if :old.sal <> :new.sal or :old.deptno <> :new.deptno
then
-- Store snapshot of old version of row,
-- plus pointer to new version.
insert into EMP_TE(DML,ROW_ID,SAL,DEPTNO)
values ('U',:new.rowid,:old.sal,:old.deptno);
end if;
end if;
--
end;
/
Rem
Rem Create Transition Effect views
Rem on top of TE-table.
Rem
create or replace view V_EMP_ITE as
select e.sal,e.deptno
from EMP_TE te,EMP e
where DML='I' and te.ROW_ID = e.ROWID
/
create or replace view V_EMP_UTE as
select e.SAL as N_SAL
,e.DEPTNO as N_DEPTNO
,te.SAL as O_SAL
,te.DEPTNO as O_DEPTNO
from EMP_TE te,EMP e
where DML='U' and te.ROW_ID = e.ROWID
/
Rem
Rem Constraint serialization lock services.
Rem
create or replace function f_allocate_unique
(p_lockname in varchar2) return varchar2 as
--
pragma autonomous_transaction;
--
pl_lockhandle varchar2(128);
--
begin
-- This does implicit commit and is called
-- from triggers, hence the aut-tx.
dbms_lock.allocate_unique(upper(p_lockname)
,pl_lockhandle
,60*10); -- Set expiration to 10 minutes.
--
return pl_lockhandle;
--
end;
/
create or replace procedure p_request_lock(p_lockname in varchar2) as
--
pl_lockhandle varchar2(128);
pl_return number;
--
begin
--
-- Go get a unique lockhandle for this lockname.
--
pl_lockhandle := f_allocate_unique(p_lockname);
--
-- Request the application lock in
-- exclusive mode.
-- Allow for a blocking situation that lasts
-- no longer than 60 seconds.
--
pl_return :=
dbms_lock.request(lockhandle => pl_lockhandle
,lockmode => dbms_lock.x_mode
,timeout => 60
,release_on_commit => true);
--
if pl_return not in (0,4)
then
raise_application_error(-20998,
'Unable to acquire constraint serialization lock: '||p_lockname||'.');
end if;
--
end;
/
Rem After statement triggers.
create or replace trigger EMP_AIS_C1
after insert on EMP
declare pl_message varchar2(80);
begin
--
-- Every insert requires a C1 constraint check.
-- Only check once per department.
--
for r in (select distinct deptno
from v_emp_ite)
loop
begin
--
-- Serialize the validation query.
--
p_request_lock('C1'||to_char(r.deptno));
--
select 'Constraint C1 is violated for department '||r.deptno||'.' into pl_message
from (select avg(sal) as avg_sal
from emp
where deptno = r.deptno)
where avg_sal > 3000;
--
raise_application_error(-20999,pl_message);
--
exception when no_data_found then null;
end;
end loop;
end;
/
create or replace trigger EMP_AUS_C1
after update on EMP
declare pl_message varchar2(80);
begin
--
-- Only updates that increase the
-- sum(sal) of a department
-- require a C1 constraint check
--
for r in (select deptno
from (select deptno,sum(delta_sal) as sum_delta_sal
from (select n_deptno as deptno,n_sal as delta_sal
from v_emp_ute
where o_deptno <> n_deptno
union all
select o_deptno as deptno,-o_sal as delta_sal
from v_emp_ute
where o_deptno <> n_deptno
union all
select n_deptno as deptno, n_sal-o_sal as delta_sal
from v_emp_ute
where o_deptno = n_deptno)
group by deptno)
where sum_delta_sal > 0)
loop
begin
--
-- Serialize the validation query.
--
p_request_lock('C1'||to_char(r.deptno));
--
select 'Constraint C1 is violated for department '||r.deptno||'.' into pl_message
from (select avg(sal) as avg_sal
from emp
where deptno = r.deptno)
where avg_sal > 3000;
--
raise_application_error(-20999,pl_message);
--
exception when no_data_found then null;
end;
end loop;
end;
/
Toons -
I will check this fully later (I sort of thought *you* would get it right :)
I see the dbms_lock call in there, that is the one thing people *almost always forget or never even THINK ABOUT*.
You are serializing at the department level!!!
My approach might be instead:
a) on commit fast refresh materialized view that selects deptno, avg(sal)
b) with a check constraint to verify the MV avg(sal) is less then N
much less code...
But if you don't mind, I'll use this example after validation (I'm sure it'll be OK however, given you have the necessary serialization code in there) as why "doing this is HARD, COMPLEX and you need a full understanding of how concurrency controls in your database actually work in order to program it properly!!!
[[You are serializing at the department level!!!]]
Yes, but since I *only* validate C1 when it is really neccessary, I only serialize when it is neccessary.
In practice the serialization enforced by this code should be acceptable: lots of types of updates on EMP can coexist.
Somehow, I get the feeling that it will only be really necessary when 42 autonomous transactions all decide to serialize at the same time, and a couple manage to deadlock the trigger code when trying to grab the lock.
Perhaps an interesting stress test would be to loop this repeatedly while forking copies of the loop. Which will kill the system first, latch contention or deadlock detection?
Tom said...
Since auditing is a program requirement (not an after thought to be glued on AFTER the fact), it absolutely belongs right there in the transaction itself!!!!!
Auditing is quite often an "afterthought to be glued on". If you only have one application updating a table then sure, you can get rid of triggers and code it all in the application. But in many cases there will be several different applications or processes updating the same table (legacy application, java app, etc) and putting the audit code in a trigger ensures that you capture all DML on the target table.
updating the same table (legacy application, java app, etc) and putting the audit code in a trigger ensures that you capture all DML on the target table.
ahh, but think about this nirvana:
you have a transactional API
it IS the essence of "resusable code", the absolute (stated) goal of programmers everywhere
and that new application - it just uses it...
It is true, triggers can be used to apply and ugly band-aid that works for a while in some/many cases by accident.
But, I would still forgo them in order to have the safety we have without them.
The bottom line is, if you HAVE to use triggers to audit for the reasons you mention - then not enough thought went into the initial design of your system.....
or else auditing would have been there from day one....
and apparently it was an after thought
Which will kill the system first, latch contention or deadlock detection?
I'm not worried about beating up on the code for that - it looks like is properly serializes.
I will use it as a case study in how it HAS to be done and how it is almost NEVER done.
I pretty much thought (knew) that Toons would get it right - but look at the amount of it :) PRoves yet another point... Shows a lot of points actually - this'll be a good thread (in a new blog entry - when I get back from travel...)
Could VPD invalidate Toon's trigger constraint ?
If VPD prevents the user doing the update from seeing high earning employees, the changes could be okay according to that user's view of the data, but bad according to the unfiltered data.
You're right, Tom, I'm talking about constraints that cross tables.
And you're right, I was thinking about enforcing business rules than enforcing data integrity.
But let me ask, would it be a safe argument to state that cross-table constraints (with the exception of foreign keys) are never necessary to maintain data integrity?
Given a schema where triggers were allegedly necessary to enforce legitimate data integrity, is it fair to say that it could be re-designed in such a fashion that it isn't?
What of business constraints? Is there a better way to enforce them without triggers? As an example, think of enforcing a simple business rule that ensures all employees are paid at or above the minimum wage in their state/province.
I have a dilema with triggers (as an audit trail) and auditing in so much as they can both be turned off at some level. Auditing of sys must help this but Auditors are suspicious beings (rightly so). Turn the trigger off, fraud away and turn it back on again. I've not found the answer to give to the auditors about this one yet (even with controlled sys access).
I do use triggers to pin packages at startup into the shared_pool - seems pretty harmless to me for a use of a trigger
"Business rules"
"Data integrity"
"Cross-table constraints"
"Legitimate data integrity"
"Business constraints"
I suspect far more terms than concepts here, right?
Toon
If you have table-level triggers in your DB, one or more of the following applies:
* You don't have a structured API for CRUD (and most likely use an ORM framework)
* It would take X days for your app developer to do some change which would take 10 minutes to do in a trigger so you opt for a quick hack
* You probably aren't aware of the "mutating" errors
* You haven't been burnt badly by them! (poor exception handling, poor visibility...)
* You're planning to leave your job in the near future
When I looked at the new 11g features I got somewhat disappointed so see that there are further enhancements to triggers... I wish Oracle had instead invested that energy into solving other DB issues, such as DB config management or the object-relational chasm.
Andrew,
I use triggers purely for the purpose of enforcing data integrity. They can be used for lots of other stuff too, in which cases I mostly agree with your statement.
However I find using triggers for enforcing data integrity a better way than embedding constraint enforcing code inside the CRUD API's.
Toon
PROCEDURE update_ocp_data_count
IS
row_count NUMBER(10);
BEGIN
EXCEPTION
WHEN OTHERS THEN
-------------------------------------------------------------------------
-- Delete rows for current year and month in case they already exist.
-- Thomas.Kyte@otn.com
-- dbms_output.put_line(sqlerrm);
-------------------------------------------------------------------------
END;
Define: NULL
You can use some HTML tags.
Here is one New BusCAr Number
1729
Otn Automated.
Sorry
This thread's probably a bit old now but I came across it earlier whilst surfing a number of similar threads and it struck a chord.
I've worked with Java and Oracle for approx 8 years and see these and similar issues all the time - concurrency, data integrity, error handling etc etc.
It's definitiely not limited to database access but I think non DB savvy developers are a major culprit. Many new Java developers have no concept at all of synchronization. Most are not even aware that the default mode for most servlet containers is to run a single instance of a servlet with many threads executing through it. Instance variables in these servlets are a ticking time bomb as soon as you get more than 1 user but you see it all the time.
Part of the issue is all the new web development frameworks which are geared towards high productivity but allow developers to build apps without a sound knowledge of the underlying principles. Then, when the issues show up where do they turn???
My 2c worth...
Very interesting!
This is a really old thread at this point, but i'll just speak and hope you'll notice this comment.
I'm a total novice in the field of databases, so this can be totally off, and please forgive my rudeness for asking this, but.. Toon's trigger-code looked unnecessarily complex in my eyes. Could you explain why something like the following wouldn't work: (Constraint borrowed from http://technology.amis.nl/blog/?p=1375 -- Maximum of three clerks per department)
(Code in mysql, as that's all i know how to use. Cases for moving employees between departments omitted from the employee-update-trigger)
CREATE TABLE dept (
dept_id int unsigned not null auto_increment,
clerk_count int unsigned,
PRIMARY KEY (dept_id)
) ENGINE=innodb;
CREATE TABLE emp (
name varchar(16),
dept_id int unsigned,
job varchar(16),
FOREIGN KEY (dept_id) REFERENCES dept (dept_id)
) ENGINE=innodb;
DELIMITER |
CREATE TRIGGER ins_clerk BEFORE INSERT ON emp FOR EACH ROW
BEGIN
IF NEW.job = 'CLERK' THEN
UPDATE dept SET clerk_count = clerk_count + 1 WHERE dept_id = NEW.dept_id;
END IF;
END;|
CREATE TRIGGER del_clerk AFTER DELETE ON emp FOR EACH ROW
BEGIN
IF OLD.job = 'CLERK' THEN
UPDATE mtrig_test_dept SET clerk_count = clerk_count - 1 WHERE dept_id = OLD.dept_id;
END IF;
END;|
CREATE TRIGGER upt_clerk BEFORE UPDATE ON emp FOR EACH ROW
BEGIN
IF OLD.job != 'CLERK' AND NEW.job = 'CLERK' THEN
UPDATE mtrig_test_dept SET clerk_count = clerk_count + 1 WHERE dept_id = NEW.dept_id;
ELSEIF OLD.job = 'CLERK' AND NEW.job != 'CLERK' THEN
UPDATE mtrig_test_dept SET clerk_count = clerk_count - 1 WHERE dept_id = NEW.dept_id;
END IF;
END;|
--
-- Ugly way to abort the triggering statement. Will stop on foreign key violation.
-- AFAIK mysql doesn't yet have a native way to generate user-defined errors.
-- A better way would be to make an UDF that generates the error, and use that inside the trigger, but that's irrelevant here.
--
CREATE TRIGGER cnt_clerk BEFORE UPDATE ON dept FOR EACH ROW
BEGIN
IF NEW.clerk_count > 3 THEN
SET NEW.dept_id = NULL;
END IF;
END;|
DELIMITER |
@aleksi
you would want to use
set clerk_count = nvl(clerk_count,0)+1
(beware nulls)
and then just add
alter table dept
add constraint check_3_only
check (clerk_count <= 3)
deferrable initially deferred;
I've demonstrated that one, or we can use a materialized view with "refresh fast on commit"
Yes, there are simplier ways - Toons was demonstrating how to do it without maintaining roll up data is all.
(and yes, I would use either a materialized view OR the trigger approach - not the more complex triggers)
Thanks! That cleared it up.
I think mysql only allows primary, unique and foreign key constraints at the moment, though.
Rather cool place you've got here. Thank you for it. I like such topics and anything that is connected to this matter. BTW, why don't you change design :).
Hi, Ankit here.
I need to know what can we do at last in WHEN OTHERS :
Currently what i follow is using SQLCODE and SQLERRM and printing them.
Using RAISE or RAISE APPLICATION ERROR will raise unhandelled exception as there is no exception section following WHEN OTHERS section.
Please tell me what is the best efficient way in such scenario.
@ankit
you WANT it to raise that unhandled exception.
tools like sqlplus will print out the errors already - in a much much better fashion than you ever could.
other things that don't print the error out will be client applications and then NEED to get the error back.
If you catch it - you turn an error into "not an error", it will be missed, you will have bugs because of it.
JUST LET THE ERROR GO BACK TO THE CLIENT APPLICATION. they will receive it, they can decide what to convey to the end user.
It is OK to let errors get out of your code - in fact, it is MANDATORY you allow them to do so. Catching an error and swallowing it (hiding it, whatever you want to call it) is just about the worst thing you can do.
POST A COMMENT
<< Home