MySQL Error: #1142 - TRIGGER command denied

Question

I need that my MySQL database makes a sum of some values in a specific column. To do that, I used trigger statement, but I get an error from MySQL:

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

The trigger code is shown here:

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

Answer 1

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

Answer 2

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 |
+-------------+-----------+--------+-------------+----------------+-----------+-----------+--------+