Re: Default column value of MAX +1 of column - possible in Oracle 9i?

On Fri, 25 Mar 2005 14:06:31 GMT, HansF <News.Hans_at_telus.net> wrote: Hi Hans,

Thanks for your well-written reply.

Comments embedded (not in order of appearance).

>So, I guess it comes down to experience. Mine (which goes back a few
>decades) yields a conclusion exactly opposite to yours. Religion anyone?

Probably religion. Which doesn't make this debate less interesting of enlightning.

I must clearly admit, though, that my statement "Triggers are evil", was an exaggeration.

>Since there is a clean separation between business logic and user
>interface, very similar to that desired by the J2EE - JSP/Servlet vs EJB
>separation, developers dealing with users can concentrate entirely on
>getting the GUI correct without ever having to worry about whether the
>business logic is included.
                

This separation generally is a good thing and holds true in some cases. But not all.

But let's pick a case in which it is true.

A GUI is used for entering, say, a new customer. The end-user types all the relevant info and presses the "Insert this new customer and do all the business stuff involved"-key. This can be carried out in (at least) 2 different ways :

1. The key press generates an insert statement, executes it and an INSERT trigger implements the rest of the business stuff. This trigger possible calls one or more stored procedures to do it's work.
2. The key press calls a stored procedure which does all the business stuff, possibly calling other SP's in doing so.
3. and A) both accomplish the same.
4. requires no more work on the client than A).
5. Involves 1 step, B) involves 3 steps.
6. Requires no more maintenance than A).

I would like you to give another common example in which the most robust and easy way to the goal involves a trigger.

>a) organizations
>- with a minialist IT attitude (understaffing and JOAT developer);
>- limited developer skills (super user 'come developer, no DB training);
>- where interdepartmental politics is priority (DBA vs Dev syndrome).
>
>b) coding standards
>- that don't exist;
>- do not separate between business logic and presentation;
>- do not [know how to] use the database correctly.
>
>c) developers
>- who are called to DBA but have no appropriate training/experience;
>- who don't understand SQL (eg: use loops instead of sets);
>- who have not learned PL/SQL ;
>- who have developed a database independence religion;
>- who have only delivered quick 'n dirty prototypes;
>- who have never had to do a cost analysis including operating expense;
>- who have never gone past the 'MS Access is fun' stage.
>
>Realize the above is not comprehensive. Realize also the wording is
>intentionally sever and worst-cases. This represents what I have seen
>and do not imply your situation.
>
>So, I guess it comes down to experience. Mine (which goes back a few
>decades) yields a conclusion exactly opposite to yours. Religion anyone?

Almost all projects in which I were involved, suffered from at least 5 of the mentioned diseases.

Furthermore, I have viewed the DBA role change rapidly, especially the last 5 years :

10 years ago the DBA was much more involved in the development process and commonly a part of the development team.

Ideally, not one line of code was made before a proven data model was made and approved by the DBA.

Today the developers often throw a set of tables, which have never seen a data model, thus basic quality assurance is lacking. No normalization, no constraints (I love them, unlike triggers) etc. They then code SQL directly in the front-end code, often without any coordination, each developer coding the SQL he/she needs right here, right now.

The DBA then gets involved when such a beast is put into production. Not surprisingly, the DBA does more and more fire-fighting and less fire-prevention

And many times I have seen triggers being used for repairing such an application, making the beast even worse.

Furthermore, when I had the chance to make decent data models from the start, triggers almost never turned out to be the best solution to implement...well : anything.

These 2 experiences (which do not span 2 decades, but still some years) together have made me quite sceptical regarding triggers.

• Kenneth Koenraadt