Using MySQL triggers or transactions? I want to ask your opinion in using MySQL triggers or transactions in a website. 

Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So each payment operation is inserted in this table. 

However, it will be time consuming calculating each time the total credit amount of the user every time he do action. So I thought maybe is a good idea to keep the total credit amount for each user in a user profile table.  

Here is the problem. How can I be sure that the total credit amount form the profile table will stay synchronized with the operations from payment history table ?

I thought using 2 methods:


MySQL triggers or
transactions coded in the source code 


Which is more reliable? What if I have large database (over 100.000 users) ? 

Do you have any suggestions to do this?

The BD MySQL engine is InnoDB.
Without a doubt, I would rule out triggers and strictly stay with transactions.

Triggers are, by nature, stored procedures. Their actions are virtually hard to roll back. Even if all underlying tables are InnoDB, you will experience a proportional volume of shared row locks and annoying intermittency from exclusive row locks. Such would be the case if triggers were manipulating tables with INSERTs and UPDATEs being stagnated to perform heavy duty MVCC inside each call to a trigger.

Combine this with the fact that proper data validation protocols are not implemented in MySQL's Stored Procedure Language. Business Intelligence is OK to have contained in a database provided the Stored Procedure Language can handle a transactional environment. As a MySQL DBA, I have to honestly say that such is not the case with MySQL. Oracle (PL/SQL), PostgreSQL (PL/pgSQL), and SQL Server (T-SQL) have this edge over MySQL.

Concerning transactions, MySQL has InnoDB as its main ACID-compliant storage engine (Deafult storage engine in MySQL 5.5). It has excellent crash recovery and obeys the ACID compliance protocols.

I would choose transacitons over triggers every single time.