MySQL trigger to set salary insert/update limits

Question

So I have a table employees and I am trying to ensure that staff cannot earn more than their managers.

Here is my attempt to create a trigger for that

create trigger staffsalary before update on employees for each row begin
if ((new.salary < 50000) where staffid < 200000) 
then signal sqlstate '45000' set message_text = 'A promotion is required
for staff to earn above 50k'; end if; end^^

I have also tried to tailor the trigger to say where staffed like '1%' because my staff IDs who are not managers start with 1.

But nothing has worked, and MySQL keeps showing me an error where I have 'where staffID < 200000).

Help or suggestions what alternatives may work would be appreciated!

Answer 1

MariaDB [sandbox]> delimiter $$
MariaDB [sandbox]>
MariaDB [sandbox]> create trigger staffsalary before update on employees for each row begin
    -> if ((new.salary > 50000) and new.emp_no < 5) then
    -> signal sqlstate '45000'
    -> set message_text = 'A promotion is required for staff to earn above 50k';
    -> end if;
    -> end $$
Query OK, 0 rows affected (0.05 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> delimiter ;
MariaDB [sandbox]>
MariaDB [sandbox]> select emp_no, salary from employees;
+--------+--------+
| emp_no | salary |
+--------+--------+
|      1 |  20000 |
|      2 |  39500 |
|      3 |  50000 |
|      4 |  19500 |
|      5 |  10000 |
|      6 |  19500 |
|      7 |  40000 |
|      9 |   NULL |
+--------+--------+
8 rows in set (0.00 sec)

MariaDB [sandbox]>
MariaDB [sandbox]> update employees set salary = 66000 where emp_no = 1;
ERROR 1644 (45000): A promotion is required for staff to earn above 50k