Win a copy of Rust Web Development this week in the Other Languages forum!
  • 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
  • Tim Cooke
  • Campbell Ritchie
  • Ron McLeod
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Junilu Lacar
  • Rob Spoor
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Tim Moores
  • Jesse Silverman
  • Stephan van Hulst
  • Carey Brown
  • Al Hobbs
  • Piet Souris
  • Frits Walraven

What is the reason to use trigger ? is there an alternative ?

Ranch Hand
Posts: 841
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi guys,

First of all, happy new year 2022.

I am now stumped with a design in a code base that I totally have no experience as in I do not know how to /why use trigger in a db and hope that I can get some answers here.

So, there is this part of the code that when something is update, it will lead to a trigger in the db that will do the insertion.

I'd like to know if we don't use trigger can we just write a function that if a insert is done in a table, then another table will be updated also.

(I should think it is possible but I have not tried out and not sure if it can be done quickly as a experiment on a personal basis)

This question is really to satisfy my curiosity why use trigger because my experience is too shallow to know why.

Thanks again for your help.

Posts: 74654
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sounds like something that should appear in a basic SQL tutorial. If you have an event that might occur, and you want to make changes to the database when it occurs, then a trigger is a good way to do that. Maybe if you have a bank loan and the date for a reminder is reset whenever a payment is received, then a trigger on that payment field might be a way to allow you to reset that reminder date or otherwise record the loan as now up to date.

[ADDITIONAL]Thread moved to our databases forum.
Saloon Keeper
Posts: 24825
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, we're a Java world here, so as a general rule, we like our application logic to be actually in the applications. I think you're probably already heard my polemics on avoiding stored procedures. Likely too many times.

However, triggers do have their uses, even as do stored procedures. They can help maintain database integrity, especially where multiple apps are using the same database, for example. They can create alerts to questionable conditions - for example, when someone attempts to move $USD 10,000 or more on an account (federal regulations apply). And many other conditions, including those Campbell suggested (although resetting reminder dates is one of those things I'd normally have the app do).
Posts: 1012
Netbeans IDE Oracle MySQL Database Tomcat Server C++ Java
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
tangara goh,

I'm a backend developer and have been for quite some time.  When I first started programming, I used triggers fairly extensively, but came to know that really, the only reason the triggers were being used was: lazy database development and/or lack of well defined business rules.

As in Java where you use setters to do and enhance process to ensure proper setting of values in an application, the same rule should be extended to the database as well.  A setter in SQL is known as a stored procedure.  When business rules are set by the Admins of the data, the business layer should include sufficient stored procedures to ensure the business model laid down for the developers to follow.  In leu of the backend enforcing the integrity through setters defined in business rule, the middle tier should take the place of the stored procedure with sufficient objects to process the business rules from that layer.

In any case an argument can be made against one and in favor of another, I have supported many different ways of ensuring database integrity and business rule authentication, but  I will say: once I left that trigger mentality, I have never gone back, nor do I find many DBA's that will support the trigger idea, but rather, do as I do: support better database and business rule development.


BTW: if you truly want to do this from the frontend, then look at updateable views.
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
    Bookmark Topic Watch Topic
  • New Topic