Database Triggers - Forum - OpenEdge Development - Progress Community
 Forum

Database Triggers

This question is answered

Hi.

What is the "recommended" usage of database triggers in OE applications these days?

Are they still okay to use, or is it considered ancient, legacy, bad-idea, etc.? 

Jeff Ledbetter

Roundtable Product Architect

www.roundtable-software.com

Verified Answer
  • Thank you all for input. It all matches our internal dialog on some level.

    "..that may imply that you have multiple creates of a table in various portions of the application"

    Not all applications are perfect. :)

    Jeff Ledbetter

    Roundtable Product Architect

    www.roundtable-software.com

All Replies
  • They have their place and can be used to implement application / db-wide behavior that can't be done any other way.

    Did you have any specific concerns?

  • Hi Tim. No specific concern; just curious if they were still considered appropriate to use or if one should always code the logic needed into the applications.

    Jeff Ledbetter

    Roundtable Product Architect

    www.roundtable-software.com

  • What is it that can’t be done any other way?

    Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

  • You can still use it for database integrity cases like cascading updates and deletes.

  • The main use case I ran into was capturing data changes across the application, applying some business logic to identify and capture the transactions, send the changes to related sites, apply the changes while applying more business logic.

    Without db triggers, there was no way this project could've been economically completed.

  • I personally consider them o.k. for basic RI (delete cascade, delete restrict, etc.) – when the application is not based on a framework which supports RI in the core data access classes.
     
    I tend to avoid any business logic in them.
     

    Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

  • “Without db triggers, there was no way this project could've been economically completed.”

    OK – your first statement sounded a lot like you were suggesting certain ABL capabilities only exist in triggers.
     
    This statement now sounds much more like it had to be done in a certain way due to the structure of the legacy application code base.
     

    Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

  • I believe it is replication use case, which is most appropriate inside the table triggers.

  • I was thinking along the lines of assigning unique key values (i.e. guid values). In theory, is that an appropriate use-case for a trigger or should it be in application code?

    Jeff Ledbetter

    Roundtable Product Architect

    www.roundtable-software.com

  • I’d consider that in a create trigger.
     
    If you are saving data from temp-tables and you have parent/child relationships in that data, then you may need to reconsider (since you have a key there anyway).
     
  • We do use triggers in that manner. Although it makes it tricky synchronizing the temp-table after creating a record which now have the valid GUID.

  • In which case the value from the create trigger would most likely be overwritten with the data from the temp-tables (external system). So that parent/child relation should remain intact.
    Von: Peter Judge [mailto:bounce-pjudge@community.progress.com]
    Gesendet: Montag, 20. März 2017 19:29
    An: TU.OE.Development@community.progress.com
    Betreff: RE: [Technical Users - OE Development] Database Triggers
     
    Update from Progress Community
     
    I’d consider that in a create trigger.
     
    If you are saving data from temp-tables and you have parent/child relationships in that data, then you may need to reconsider (since you have a key there anyway).
     

    View online

     

    You received this notification because you subscribed to the forum.  To unsubscribe from only this thread, go here.

    Flag this post as spam/abuse.

     

    Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

  • Mike Fechner

    “Without db triggers, there was no way this project could've been economically completed.”

    OK – your first statement sounded a lot like you were suggesting certain ABL capabilities only exist in triggers.
     
    This statement now sounds much more like it had to be done in a certain way due to the structure of the legacy application code base.

    It was an question of capturing all data changes regardless of where they were made, and maintaining that capability regardless of who was maintaining the code down the line.

    Triggers were the only technology that provided that capability. 

  • I have to say that I do not like or use db triggers - I was bitten by the fact that they are not run when executing SQL .

    If you are looking to use create triggers, that may imply that you have multiple creates of a table in various portions of the application - something in itself could be considered "sub-optimal" as it would mean that any change to the creation code would require multiple changes to the source.

    Just my 0.01p* worth

    * adjusted after currency devaluation

  • Ah, so you’re saying do both and then you don’t get hurt in the case where a single-table-create doesn’t previously assign values?