Database Triggers

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.

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 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?

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.

[quote user="Mike Fechner"]

[/quote]

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

Only there is also a disable triggers statement and ‘everywhere' does not include the SQL engine (JDBC/ODBC) so I would say it’s probably time for Progress to actually become a relational database and add a long due support for foreign keys inside the database server instead of pretending this can be done using triggers (running on client space).

I have always felt that the core flaw of db triggers was that it put the logic of record creation, modification, and deletion in two different places -- partly in the trigger and partly in the application code.  There is nothing in either to tell you what is happening in the other one.  A properly designed modern application is going to have these actions centralized in a component responsible for those actions so there is one place to check to see what is happening and one place to modify if changes are needed.  DB triggers get in the way of that goal.

Oh dear. I agree with the Doctor. Have I been assimilated ?

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. :)

Not all applications are perfect, but this should not be used as an excuse to just do what is expedient or convenient.  We should understand what is better and constantly strive to incorporate that into the application whenever possible.

Agreed. It was a theoretical question that came up while updating code (within the app) to set the field value.

[quote user="Thomas Mercer-Hursh"]

Not all applications are perfect, but this should not be used as an excuse to just do what is expedient or convenient.  We should understand what is better and constantly strive to incorporate that into the application whenever possible.[/quote]

You are absolutely right and this is exactly what I told management here. It was a few seconds before they started laughing and walked away.

Did I say something weird?

[quote user="Patrick Tingen"]

Thomas Mercer-Hursh

Not all applications are perfect, but this should not be used as an excuse to just do what is expedient or convenient.  We should understand what is better and constantly strive to incorporate that into the application whenever possible.

You are absolutely right and this is exactly what I told management here. It was a few seconds before they started laughing and walked away.

Did I say something weird?

[/quote]

Just sounds like they made a different 2 out of 3 picks from "fast, good, cheap" than you would...