CodeIgniter Forums

Full Version: Database - Triggers or no Triggers?
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
First, Note: This app im creating isnt for a website that ill be using, its actually an app that others will be downloading and using in their own networks.

There are plenty of times in my app where a value in the database needs to be populated when the row is inserted or updated...

EG..
  • assets.created needs to be set to time() when the asset is created
  • assets.modified needs to be updated to time() when the row is updated
  • sessions_assoc.regens needs to be set to┬áregens+1 when its updated
Obviously this can be done in the PHP just fine. But ive gotten in the habit of using MySQL Triggers to accomplish this.
EG:
Quote:CREATE TRIGGER assets_list_created BEFORE INSERT ON assets_list FOR EACH ROW SET new.created = UNIX_TIMESTAMP(NOW())

CREATE TRIGGER assets_list_updated BEFORE UPDATE ON assets_list FOR EACH ROW SET new.modified = UNIX_TIMESTAMP(NOW())

It seems to work just fine, except when I have someone else import a MySQL dump and their user is different than mine, but im sure I can find a way around that.

So im just curious how many people use PHP to accomplish these kinds of things, and how many use SQL Triggers.
I usually handle this in the model through PHP. The only real reasons I can think of for this preference are:
- I don't have to rewrite the triggers if I change databases (not a likely occurrence for me, but it was one of the reasons)
- I can code the behavior once in my base model (or, in this case, most of the code was written by someone else) and control whether it's used on a particular model with a boolean value, rather than defining a new set of triggers for each table.

I'm sure if I really put my mind to it I could find a way to easily define the triggers in any supported database, but I really didn't give the option much thought when I made the decision (I really didn't even give it enough consideration to think of it as a conscious choice).
(08-27-2015, 12:11 PM)mwhitney Wrote: [ -> ]I usually handle this in the model through PHP. The only real reasons I can think of for this preference are:
- I don't have to rewrite the triggers if I change databases (not a likely occurrence for me, but it was one of the reasons)
- I can code the behavior once in my base model (or, in this case, most of the code was written by someone else) and control whether it's used on a particular model with a boolean value, rather than defining a new set of triggers for each table.

I'm sure if I really put my mind to it I could find a way to easily define the triggers in any supported database, but I really didn't give the option much thought when I made the decision (I really didn't even give it enough consideration to think of it as a conscious choice).

Reason #1 might be the reason I end up switching from Triggers back to PHP. This app will be installed by other people on their own servers with their own database accounts. So the database schema/dump will need to NOT have the triggers in it, and the installation script will need to add the triggers as the correct user, or else the database backup that I will provide will try to create the triggers as my user.

If something happens in the setup, and the portion that creates the triggers doesnt run, that will mess with a LOT of things
You should avoid using triggers in general ... they are SQL-based logic and that's bad unless the database is what literally drives your application (not just as storage). If you're writing a PHP app, keep the logic in PHP - that's the basic rule that you should follow.
Sql Triggers can also be a nightmare for debugging, especially when you are giving the code to others to use. Triggers certainly have their place but I suspect not (IMHO) for the reasons stated. Personally, I never use them, but I can see where they would be useful in the right environment where ease of code maintenance is not the issue, but security or data integrity is, such as banking etc.
Well, you have to remember that SQL itself is a programming language. We may use databases only for storage, but there are cases where the database itself is the application.
(09-01-2015, 05:49 AM)Narf Wrote: [ -> ]Well, you have to remember that SQL itself is a programming language. We may use databases only for storage, but there are cases where the database itself is the application.
Yeah, I think im gonna switch to PHP, and any heavy MySQL jobs might be programmed as stored procedures in MySQL, MAYBE... (As opposed to cron).