I am attempting to architect an e-commerce rewards/promotions program for purchasing items.

Here are some typical scenarios:

  • $X amount of dollars off a purchase for a specific day
  • $X amount of dollars given if some threshold is met.
  • Can only be redeemed once, or x amount of times
  • Can only be redeemed during a time frame (days, months, weeks)

Would a SQL Trigger be an appropriate use case for the above? The database action needs to be implemented immediately when these criterias are met and Inserting, updating, & deleting all seem to be shared among the use cases. Is this an appropriate implmentation or is there something better that I can potentially use? Thank you.

  • Hey @a_horse_with_no_name I understand that sql-server, mysql, and postgresql implement them and I haven't committed to a database yet – Dan Rubio Sep 5 '19 at 19:15
  • I know the question probably got downvoted for being vague, but really what I'm looking for is a general direction to go off into. I was just wondering if there is something specific or an alternative that I can dig into – Dan Rubio Sep 5 '19 at 19:23
  • 1
    If you haven't settled on a product yet, I think this question is a bit premature, honestly. Not any of the three listed would make using triggers like this a good idea, but you're putting the cart waaaay ahead of the horse. – LowlyDBA - John M Sep 5 '19 at 20:26
  • Put the business logic in the application, not buried deep in triggers. Do put in some layer between the API and the database, either another layer of app routines or a db Stored Routine. – Rick James Sep 5 '19 at 20:59

Triggers are dangerous.

I have flown around the country as a database performance consultant and some of the rare things I have found is a trigger that was put in years ago and the table or tables that the trigger acts have grown in size considerably, the entire time the trigger was producing a table lock which is fine when the table was small and server usage was light, but it did not scale as the table size and DB usage grew. When I found the triggers as the cause I replaced them with stored procedures.

As further evidence, the big out of the box applications don't use triggers. Supply chain software such as SAP, CRM software such a Siebel.

  • Hmm... interesting. I've never used SAP or Siebel in work, but I did work for a company which wrote EAM (Enterprise Asset Management) software and one of our clients was the entity which ran the railway network for a large European country - we're literally talking billions of widgets. This SW ran on Oracle and SQL Server (and others )and therefore couldn't use Triggers or Stored Procedures. Everything was done in application code - hell, there weren't even any Foreign Keys! <sound of my jaw dropping!!> Is this the reason that SAP and Siebel don't use SPs or Triggers? – Vérace Sep 5 '19 at 21:33
  • I think you answered your own question. – Duane Lawrence Sep 6 '19 at 17:51
  • Really? How so? You said that Triggers are dangerous. and the big out of the box applications don't use triggers. I pointed out a large EAM prog that doesn't use them and reasons. I firmly believe that the non-use of TRIGGERs is due to what I call the lowest-common-denominatorism of the big CRM/ERM/EAM apps rather than any "danger" associated wth them per se. Properly used, they can be extremely effective and your argument about tables growing/scaling applies to any database coding solution. No disrespect, but I don't think your answer is correct, maybe not totally incorrect? – Vérace Sep 6 '19 at 17:58
  • The big companies that produce the high end software that has high usage don't use triggers due to performance problems. If you disagree with this, it is a free country with free speech. – Duane Lawrence Sep 6 '19 at 18:00

I think a trigger would be the wrong solution for philosophical and for practical reasons.

The rules you mention sound like business rules, not data integrity rules. As such they should be in the "logic" tier of your application where the workflow, connections and transitions are managed. A trigger does not fit comfortably with this architecture.

Practically speaking, triggers tend to be forgotten. They're not well-integrated with the main flow of code. Their activation is opaque. Unless they form a central part of the architecture people forget they exist.

It is appealing to put this kind of thing in the database. Changes are likely to be frequent. DB code can be distributed and re-compiled on-the-fly at run time. The alternative is to parse out all the rules into (many) tables and have generic routines to implement logic at run-time. It is writing a new domain specific language within the application. Putting the logic in the main application requires a full rebuild and release each and every time the rules change. Even with CI/ CD this can be tricky.

One solution would be to put the frequently-changing rules into a script language, say Python, which is then called from within the main application. Here's one example in .Net. If you do choose to use your DBMS's scripting language to implement this I'd suggest using a stored procedure (SP) rather than a trigger. The app can call the data action and then this SP making the dependency obvious in code, or the app calls a single SP which has within it the data action and a subsequent call to the "special rules" SP.

Not the answer you're looking for? Browse other questions tagged or ask your own question.