CODEWITHSUNDEEP
mysqlsql
Jakub L.
Jakub L.

Reputation: 11

Counting INSERT operation to a table with trigger

I'm student and beginner SQL programmer. I build forum in php and MySQL, for my database classes. I wanna use TRIGGER to count INSERT operation, when user write a post and I want to overwrite a record in my database table with actual number of his messages. Is that possible?

For example I've got two tables: user (id, username, password, email, number_of_messages) messages(id,date,user_id,subject_id,subject,content)

When user write a message I want run TRIGGER:

CREATE TRIGGER activity
 AFTER INSERT on messages 
  FOR EACH ROW UPDATE INTO user(number_of_messages) ....

And I don't know what to do. Can you help me with that?

Upvotes: 0

Views: 205

Answers (1)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

It is possible but it is not a good idea to track count of messages. You can count it each time when it is necessary by this simple query.

select count(*) message_count from `messages` m
  join `user` u on m.user_id = u.id
where u.id=<your user id> -- or where u.username = <your user name>

Upvotes: 1

Related Questions