0

I am doing a tweeter project and I have a trouble with the database. I created the next table to manage the twets. One of the property is "isRetweet" which will store the id of the retweted tweet. And another is the retNum which will store the times a tweet was retweted. 

create table tweets(
  tweetId int auto_increment,
  userId int not null,
  tweetText varchar(140) not null,
  createDate TIMESTAMP DEFAULT current_timestamp,
  retNum int DEFAULT 0,
  loveNum int DEFAULT 0,
  commNum int DEFAULT 0,
  isRetweet int DEFAULT -1, 
   PRIMARY key (tweetId),
   foreign key (userId) references users(userId)
);

Here comes my problem.

I created a TRIGGER in charge of update the retNum AFTER inserting a new tweet in the tweets table. So if the isRetweet id is different of -1 and there is a tweet with this id I want to update its retNum with +1.

I created this trigger to make it. 

/*Retweet TRIGGER*/
DROP TRIGGER IF EXISTS addRetwet;\\
CREATE TRIGGER addRetweet AFTER INSERT ON tweets
FOR EACH ROW
BEGIN
    UPDATE tweets SET retNum = retNum + 1 WHERE tweetId = New.isRetweet;
END;\\

The problem come when i try to insert a new tweet. It gives the next error:

Can't update table 'tweets' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

Do you know why it gives me this error and how can i solve it?

Thanks you.

Mr.Robot
  • 25
  • 4
  • 1
    Instead of using an UPDATE statement, you would simply set the column value. The new data has a alias of new, so use ```SET new.retNum = new.retNum + 1;``` instead of the UPDATE statement. – Sloan Thrasher Jun 10 '17 at 19:28
  • Mi interest is to update the retweted tweet retNum and not the new one. – Mr.Robot Jun 11 '17 at 11:16
  • The error message pretty much explains itself. In MySQL, a trigger can't update/insert into the table that triggered it. Take a look [here](https://dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html) for more info. – Sloan Thrasher Jun 11 '17 at 12:33
  • For a work-around, look at the answer in this [question](https://stackoverflow.com/questions/12877732/mysql-trigger-for-updating-same-table-after-insert). – Sloan Thrasher Jun 11 '17 at 12:36

0 Answers0