问题:

I have two tables, one for storing reports of a user, and another for storing users.

1. Report_table
-----report_id(P)-----user_id--------
        001           user1
        002           user1 
        003           user3


2.Users table 
-------user_id(P)-------no_of_reports
        user1             2
        user2             0
        user3             1

Now whenever a report is inserted in report_table(say report_id:004, user_id: user1), I update both tables like this.

INSERT INTO reports_table VALUES (004,`user1`)
INSERT INTO users_table  (user_id,no_of_reports) VALUES (`user1`,1)
  ON DUPLICATE KEY UPDATE no_of_reports=no_of_reports+1;

Is there a better way of doing this.. Is there a way of automatically incrementing the no_of_reports counter when a new report is inserted?

回答1:

You can deal with TRIGGER by using a AFTER INSERT trigger

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

回答2:

You need a trigger: http://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html which should fire after each insert operation on the reports_table.

CREATE TRIGGER update_report_cnt AFTER INSERT ON reports_table
FOR EACH ROW
   BEGIN
      UPDATE users_table SET no_of_reports = no_of_reports + 1 where user_id = NEW.user_id;
   END;

(I'm not sure the syntax is correct. Ithink a before insert trigger would also work)