• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Junilu Lacar
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Ganesh Patekar
  • Tim Moores
  • Pete Letkeman
  • Stephan van Hulst
Bartenders:
  • Carey Brown
  • Tim Holloway
  • Joe Ess

Can you give me real time scenario when to use Procedure, Function, Cursor, Trigger?  RSS feed

 
Ranch Hand
Posts: 175
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want to know in what particular scenarios you will need to write Procedure, Function, Cursor, Trigger?

thanks in advance.
 
Marshal
Posts: 4455
284
Clojure IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Odd question. Are you working on some homework or something? Perhaps give us some context and tell us what you think the answer is to your question?
 
Arun Singh Raaj
Ranch Hand
Posts: 175
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Odd question. Are you working on some homework or something? Perhaps give us some context and tell us what you think the answer is to your question?



@Tim Cooke, I read about cursor, Trigger, Function and understood but I'm unable to think of the real time scenarios when I would implement them.

What i found on internet about Procedure is:
Assume I have an e-commerce application and I can use Procedure while inserting the product into Product table. Scenario for using Procedure can be like:
1. Before inserting, check the detail about the product name. If the product name is available, update an existing product qty + inserted product qty,
2. Before inserting, check the detail about the product name. If the product name is available, check the product price. If the existing product price is less, the inserted product product price replaces the existing product price with the inserted product price.
3. If first and second conditions are not satisfied, insert the product information, as new record into the table.

Similarly, I want to know in what case i should choose Trigger, Cursor, Function.

Your help will be much appreciated.
 
Bartender
Posts: 19673
92
Android Eclipse IDE Linux
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Short answer: As rarely as possible.

I don't care for any of those constructs because for one thing, they're non-portable for the most part and thus lock you into a specific brand of database. I like to keep my options open. These days you can never tell when your employer will get purchased by another company and the accountants will object to license fees for Oracle or DB/2 and management will mandate migration to PostgreSQL or MySQL. Or, conversely, they may be so strung out on traditional IT and mandate everything goes in DB/2, Or even that you might be directed to refactor to target a NoSQL DBMS.

Secondly, there's maintenance. Historically, DBMS's have not had history or "undo" options once a transaction has been committed. Often even the ones that have had something like that have required explicitly switching the feature on. Since that means that anyone with sufficient access rights can go in and accidentally or deliberately muck things up - or simply introduce a bug during maintenance, recovery can be a pain. Good shop practices can mitigate that, but if you keep the master code for triggers and stored procedures in an external source control system, there's no direct or obvious connection and often in cases of extreme panic, no one will know where to get the backup versions or the person(s) who are won't be available when it all goes wrong.

Finally, there's the issue of code-splitting. Some of the least enjoyable things I've done in IT centered around projects where some of the logic was in application programs, and some was in stored procedures and every bit of maintenance had to include a "treasure hunt" to determine what was where and what unrelated apps would be affected if you changed things. This isn't just an issue with databases. I've chastised people using MVC code for putting too much logic in their View components for the same reason, but databases are especially prone to it. Especially if you're working in one of the shops that invested early in EJBs, where I've seen a lot of session-type EJBs running Oracle or DB/2 stored procedures. To minimize the cost of maintenance AND to reduce the time and effort involved (which itself is a cost), it's better if you can know before you ever actually look at anything where the logic is going to be.


So those are the reasons I don't recommend stored procedures, triggers, et. al., - and really all such things are really stored logic of one kind or other, which is why I lumped them all together.

Now here are some reasons why you should consider server-side logic:

1. Automatically-generated keys. Often a system will have more than one concurrently-running application adding records. In which case, the DBMS is the most likely place to be able to centrally generate and co-ordinate unique key values. And certainly one of the simplest.

2. Mandatory value constraints. Often a table will have columns which must not be left uninitialized. DBMS logic can be used to supply a default value, reject transactions with missing values, and ensure that only certain values are set. Some of these functions are implicit (such as defining a column as "NOT NULL"), while others may be triggers or full-fledged stored procedures (or combinations)

3. Schema constraints. Often you'll wish to ensure that if you delete a parent record that all its children are deleted as well (cascade deletion). Foreign key constraints are also part of this sort of database logic. Here again, triggers and stored procedures can be used to enlarge such concepts.

4. Single-point logic. In the event that critical logic applying to all clients of the database is needed, it's usually better to provide this logic in the database than having to maintain seperate per-client copies of the logic. It's less work and it ensures that all the clients change at the same time.

5. Performance constraints. Some things just require too much time and work if you have to have a client application fetch massive quantities of data, fiddle with it, then update it all and it's just less wear and tear on the network and the client machine if the logic is done inside the DBMS server (and generally faster as well). However, this isn't always as desirable option as you might consider. First, there's the idea of shared workload. You have one database server, but typically many clients. So it's usually more productive if the heavy logic is distributed among many application hosts rather than all crunched into a single DBMS hosts. Secondly, there's the cost of labour. As the proponents of ORM systems have noted, it's fairly easy to get people with Java skills, less easy to find people who can competently deal with the convolutions of SQL, and difficult indeed to find people who qualify in both aspects.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!