Mysql trigger performance vs php manual insert

Question

I want to create a notification after insertion on some tables. For example whenever a user inserts a comment I create a notification for the administrators that this user has created a comment.

I used to do it manually in the PHP, It wasn't as that bad, It was something like that:

// after the comment is created
Notification::create(....);

Not bad, but sometimes I give the user the ability to add images, posts, ..etc. So I have to remember every time to insert a notification.

So I am thinking of using a mysql trigger instead. But I am worry how that will affect the performance?

One last thing, Is it possible to create a trigger after insert on multiple tables?

Thanks,

Answer 1

Is it possible to create a trigger after insert on multiple tables?

No, it is not possible. You have to create a separate trigger for each table.

I am worry how that will affect the performance?

Performance wise it shouldn't be a disaster although by introducing triggers you artificially prolong insert/update operations on you tables (images, posts, ...) effectively increasing locking time.

But performance is not the only factor to consider. Maintainability should be important too. By creating triggers you scatter you application logic between your app and database. It's harder to test. Triggers are often forgotten e.g. when you transfer the schema or produce a dump. Sometimes you don't want them to fire while you do some maintenance DML on your tables. But MySQL lacks this capability. You'll have to use workarounds for that.

Bottom line: consider not to use triggers unless you have to.