0

I want to create a trigger in MySQL for the following table such that if the attribute of occurrences is greater than 100 the attribute of status updates to "popular".

The name of the table is trigger

Repo_ID |Occurences |Status
1       | 50        | Normal
2       | 70        | Normal
3       | 190       | Popular

I've tried the following thing. It didnt work. Any suggestions?

CREATE TRIGGER `A` after update
 ON `trigger`
FOR EACH ROW BEGIN 
  IF (new.occurence > 100) THEN
    SET new.STATUS = "popular";
  ELSE SET new.STATUS = "normal";
  END IF;
END
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
lucyF
  • 1

1 Answers1

2

You want a before update trigger:

CREATE TRIGGER `A` before update ON `trigger`
FOR EACH ROW
BEGIN 
    SET new.STATUS = (CASE WHEN new.occurences > 100 THEN 'popular' ELSE 'normal' END);
END;

I also removed the IF because CASE seems more concise.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks @Gordon Linoff. I used the before update trigger and encountered a new error Operation failed: There was an error while applying the SQL script to the database. ERROR 1054: Unknown column 'occurence' in 'NEW' – lucyF Nov 19 '17 at 21:27
  • @lucyF . . . That is what you wrote in your original code. Perhaps it is `occurences` as in the sample data. – Gordon Linoff Nov 19 '17 at 21:51