CODEWITHSUNDEEP
phpmysqltriggers
Rohan Sood
Rohan Sood

Reputation: 437

Triggers or PHP scripts?

So, I am developing a system in PHP + MySQL which has about 4 related tables. Any form of Insert,Update, and Delete in one table typically reflects across at least 2 tables.

My question is, what is more efficient, the use of triggers or additional php script (which triggers SQL queries) to take care of the required related changes?

Triggers seems ideal to me, but when I write them they become pretty complicated and messy from readability point of view. PHP allows for a more "sane" way of doing things.

Please suggest on the basis of both, performance as well as usability/future modifications etc.

Upvotes: 5

Views: 2422

Answers (4)

Rennie Bottali spam
Rennie Bottali spam

Reputation: 1

Yo can add trigger in MYSQL using below Syntax...

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

Upvotes: -1

SDC
SDC

Reputation: 14212

  • Triggers if you want your DBA to be in control of the code.
  • PHP if you want your programmers to be in control of the code.

To clarify:

If it's a trigger, then the implication is that it's part of the database design for things to work that way. ie the structure of the database depends on it working that way.

If it's PHP code, then the implication is that the action is part of your business logic, and not critical to the core structure of the DB.

Upvotes: 3

ibtarek
ibtarek

Reputation: 795

As a matter of fact, database triggers would be faster than PHP code. It depends on how much you need to "translate" the data from the source table before updating the others. The more you have to manipulate it, the less readable and maintainable you SQL is likely to be. As it may contain busienss logic too.

So, IMHO, if you don't have performance issues, you'd better keep it within PHP.

Upvotes: 1

Ed Heal
Ed Heal

Reputation: 60037

I would strongly suggest that you construct your database to look after itself.

It is called decoupling.

Then even it there is a mistake in your (or somebody else) script the data is protected. For most (all?) business that is the most important asset.

Also it enables one to use other mechanisms to utilize the database. Even the raw command line.

To make the code more readable - format it and use comments.

Upvotes: 5

Related Questions