What are the advantages, disadvantages and restrictions of using MySQL triggers?

We must have to understand the advantages, disadvantages, and restrictions of using MySQL triggers so that we can use it effectively.

Advantages

Followings are the advantages of using MySQL triggers −

• Integrity of data − With the help of MySQL trigger we can check the integrity of data in the table. In other words, MySQL triggers are the alternative way to check the integrity of data.
• Useful for catching errors − MySQL triggers can catch errors in business logic in the database layer.
• Alternative way to run scheduled tasks − Actually by using MySQL triggers we do not have to wait to run the scheduled tasks because the triggers are invoked automatically ‘before’ or ‘after’ a modification is done to the data in the table.
• Auditing − Actually MySQL triggers are very much useful for the purpose of auditing of the changes made in the table.
• Prevention of invalid transactions− MySQL triggers are very useful in the prevention of invalid transactions.
• Logging of event− MySQL triggers can log an event and can also store the information on the access of table.

Disadvantages

Followings are the disadvantages of using MySQL triggers −

• Cannot replace all validations − Actually, MySQL triggers cannot replace all the validations and can only provide an extended validation.
• Invisible from client applications − Basically MySQL triggers are invoked and executed invisible from the client applications hence it is very much difficult to figure out what happens in the database layer.
• Impose load on server − Triggers can impose a high load on the database server.
• Not recommended for high velocity of data − Triggers are not beneficial for use with high-velocity data i.e. the data when a number of events per second are high. It is because in case of high-velocity data the triggers get triggered all the time.

Restrictions

Followings are some of the restrictions apply to MySQL triggers −

• Only one trigger for each timing/event − Each table can have only one trigger for each timing/event combination (ie: we can't define two BEFORE INSERT triggers for the same table).
• RETURN statement is not permitted − As triggers don't return any values hence the RETURN statement is not permitted.
• Foreign key restriction − Triggers are not activated by foreign key actions.
• Outdated metadata − Suppose, if a trigger is loaded into cache, it is not automatically reloaded when the table metadata changes. In this case, a trigger can operate using outdated metadata.
• Cannot use ‘CALL’ statement − We cannot use the CALL statement in triggers.
• Cannot create a TEMPORARY table or a view − We cannot create a view for a temporary table or a view.
• Not activated by changes in INFORMATION_SCHEMA − Actually, triggers are not activated by changes made in INFORMATION_SCHEMA or performance_schema tables. It is because these tables are views and triggers are not permitted on views.