-1

Possible Duplicate:
mysql: Cant we create triggers on system tables?

We are using MySQL ver 5.1.59 on rhel 6.1 (64 bit). We want to monitor the deletion of databases and for that trying to create a trigger on the db table of mysql database. We are creating the trigger by logging in as root user. the trigger written is as follows:

DELIMITER |

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `mysql`.`test` BEFORE DELETE
    ON `mysql`.`db`
    FOR EACH ROW BEGIN
    INSERT INTO cescnet.db_monitor (DB_name,user_name,dateandtime,operation_type) VALUES (mysql.db.Db,CURRENT_USER(),NOW(),'Delete');
    END;|

But we are getting the error:

Error Code : 1465

Triggers can not be created on system tables

As we are working as root user, we think we have all the permissions on the system. The global privilege for root user from any host including local host includes the SUPER permission. Can anyone help us please?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Goutam Baul
  • 1
  • 1
  • 2
    Gee... I think the error msg pretty much sums it up! – Bohemian Nov 17 '12 at 07:43
  • Trying to create trigger on the system table. Is not it like editing the system table? I had tried a lot for the same thing. I wish for your success. – Sami Nov 17 '12 at 09:01

2 Answers2

0

I'm going to take a wild guess and say the reason you're getting that error message is that triggers can not be created on system tables.

Oh, perhaps I can add "regardless of privileges"

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • thanks for responding. But I have seen posts here where it was mentioned that triggers can be created on system tables. – Goutam Baul Nov 17 '12 at 08:50
  • Which post? Why would you not like to refer? Your question is exact duplicate of http://stackoverflow.com/questions/10346546/mysql-cant-we-create-triggers-on-system-tables – Sami Nov 17 '12 at 09:05
-1

In short : You can not do.

I have also tried things like

See (All) and (Super) Privileges Provided by mySql

After trying all type of privileges like

grant super on *.* to root@localhost
grant all on *.* to root@localhost

I have tried simplest trigger and get same error

Triggers can not be created on system tables

If you found anywhere that triggers can be created on system tables, simply that is wrong

Sami
  • 8,168
  • 9
  • 66
  • 99