MySQL Error: #1142 - TRIGGER command denied

Question

I need that my MySQL database makes a sum of some values in a specific coloumn. To do that I used trigger statement but MySQL gave me an error:

MySQL Error: #1142 - TRIGGER comand denied to user 'XXXXXX' for table wp_wpdatatable_8

the Trigger is below:

CREATE TRIGGER SumPliche AFTER INSERT ON wp_wpdatatable_8 FOR EACH ROW UPDATE wp_wpdatatable_8 SET sommapliche = tricipite + addome + soprailiaca + sottoscapolare + ascellare + pettorale + coscia

How can I do that? thanks

joaovictortr · Answer 1 · 2018-09-02T16:39:10.073

It seems the issue here is with the permissions your user has on the database from which wp_wpdatatable_8.

To grant trigger permissions for a specific user and database, you may use the following command:

mysql> GRANT TRIGGER ON <DATABASE>.* TO <USER>@'<DATABASE HOST>';

Where

<DATABASE> refers to the database that contains the wp_wpdatatable_8 table.
<USER> the username that is trying to issue the CREATE TRIGGER command.
<DATABASE HOST> database host from where your user accesses the database. If you are running it locally, you can use localhost.

After flushing the privileges, creating the trigger should work normally.

For more information, check out the GRANT syntax on the MySQL.documentation

Good answer, but FYI there's no need to use FLUSH PRIVILEGES. GRANT or REVOKE does this automatically. Only if you use INSERT/UPDATE/DELETE directly on the system tables do you need FLUSH PRIVILEGES. There is no harm in using FLUSH PRIVILEGES, but it's unnecessary. — Bill Karwin, Sep 02 '18 at 16:37

Bill Karwin · Answer 2 · 2018-09-02T16:41:25.030

You lack the TRIGGER privilege on the database you are using.

I got this error when I did a test. Create a test user:

mysql> create user 'bill'@'localhost';
mysql> grant all privileges on test2.* to 'bill'@'localhost';
mysql> revoke trigger on test2.* from 'bill'@'localhost';
mysql> show grants for 'bill'@'localhost';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for bill@localhost                                                                                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bill'@'localhost' IDENTIFIED BY PASSWORD '*29A1BB43D3B9EB42028B4566E4836353285B9395'                                                                                                                  |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT ON `test2`.* TO 'bill'@'localhost' |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Now try creating a trigger as this user:

~ mysql -ubill -p
mysql> use test2
mysql> create table t ( i int);
mysql> create trigger tt before insert on t for each row set new.i = 42;
ERROR 1142 (42000): TRIGGER command denied to user 'bill'@'localhost' for table 't'

I also think your trigger might not do what you think it does, so it's just as well that you didn't create it.

CREATE TRIGGER SumPliche AFTER INSERT ON wp_wpdatatable_8 
FOR EACH ROW 
  UPDATE wp_wpdatatable_8 
  SET sommapliche = tricipite + addome + soprailiaca + sottoscapolare + ascellare + pettorale + coscia

If I create this trigger in my test database (as a different user with TRIGGER privilege), and then try to insert a row:

mysql> insert into wp_wpdatatable_8 values (1, 1, 1, 1, 1, 1, 1, 1);
ERROR 1442 (HY000): Can't update table 'wp_wpdatatable_8' in 
stored function/trigger because it is already used by statement which invoked 
this stored function/trigger.

You probably want to set values only in the row you insert. If so, you would reference the current row being inserted with the NEW.* syntax:

CREATE TRIGGER SumPliche BEFORE INSERT ON wp_wpdatatable_8 
FOR EACH ROW 
  SET NEW.sommapliche = NEW.tricipite + NEW.addome + NEW.soprailiaca 
    + NEW.sottoscapolare + NEW.ascellare + NEW.pettorale + NEW.coscia;

Also you must use BEFORE INSERT if you want to change a value in the row before you insert it. If you use AFTER INSERT, it's too late.

Now it works:

mysql> insert into wp_wpdatatable_8 values (1, 1, 1, 1, 1, 1, 1, 1);
Query OK, 1 row affected (0.02 sec)

mysql> select * from wp_wpdatatable_8;
+-------------+-----------+--------+-------------+----------------+-----------+-----------+--------+
| sommapliche | tricipite | addome | soprailiaca | sottoscapolare | ascellare | pettorale | coscia |
+-------------+-----------+--------+-------------+----------------+-----------+-----------+--------+
|           7 |         1 |      1 |           1 |              1 |         1 |         1 |      1 |
+-------------+-----------+--------+-------------+----------------+-----------+-----------+--------+

MySQL Error: #1142 - TRIGGER command denied

2 Answers2