4

I have setup triggers in my MySQL tables and when I applied them they were restricted to the account I used when creating the triggers, now I have multiple users in my MySQL and I want it so that the triggers will work for all users not just for the one I used when creating them. I currently tried this:

CREATE DEFINER=`%`@`%` TRIGGER after_atm_account_insert 
    AFTER INSERT ON `system_database`.atm_account
    FOR EACH ROW 
BEGIN
    INSERT INTO `log_database`.system_logs
    SET actionDone = 'insert',
        employee = USER(),
        logDate = NOW(),
        tableChange = 'atm_account',
        rowChange = NEW.atmID,
        new_data = concat("Account #",NEW.accountNumber,", Pin:",NEW.pin,", Security Code:",NEW.securityCode,", Withdrawal Limit:",NEW.withdrawalLimit);  

END
1

If you think of the definer as the user that the trigger runs as then it needs the right grants to do whatever the triggers action is.

But it sounds like what you want is for other users to be able to trigger the trigger. In this case all of the users that you need to be able to invoke the trigger need the trigger privilege.

See more info here: http://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_trigger

But basically for each user you need to grant trigger.

  • I think you mean TRIGGER, not GRANT TRIGGER. – Rick James Dec 18 '15 at 5:52
  • Updated with suggestion – sysadmiral Dec 18 '15 at 7:33
  • I did grant them trigger privileges but it still returns an error saying that the user in the definer should be the one i'm using not the other user. Should i just create the same trigger for the other users? – makingitwork Dec 18 '15 at 9:31
  • The documentation itself states: The TRIGGER privilege enables trigger operations. You must have this privilege for a table to create, drop, or execute triggers for that table. This is true from MySQL 5.5 to 5.7 so a user with the trigger privilege should be able to execute a trigger. What is the error message you are getting exactly? – sysadmiral Dec 18 '15 at 11:13
  • What throws me is "'%'@'%'". This is not a specific user, so I wonder if it works at all? – Rick James Dec 18 '15 at 17:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.