0

I have this trigger that inserts a row to a table

CREATE 
    DEFINER=`root`@`localhost` 
    TRIGGER `user_log_AFTER_INSERT` 
    AFTER INSERT ON `user_log` 
FOR EACH ROW 
BEGIN
    INSERT INTO audit_trail 
    values( aud_no, 
           ( SELECT user_acc_id 
             from user_accounts 
             where user_username = REPLACE( CURRENT_USER, '@localhost', '')), 
           ( SELECT user_fname 
             from user 
             where user_id = ( SELECT user_acc_id 
                               from user_accounts 
                               where user_username = REPLACE( current_user(), '@localhost', ''))), 
           current_date(), 
           current_time(), 
           "logs in and out"); 
END

My problem here is that the CURRENT_USER is not working, when I tried to use another account it does not insert the right user account. What is it does is, it uses the account root@localhost instead of my CURRENT_USER.

So far I have tried changing the CREATE DEFINER=`root`@`localhost` instead of root I use a different account and it work. but What I want is to make it dynamic, It must insert the current_user who made changes to the database not root@localhost. Thanks

0

Since I cant get the right user inside the trigger using current_user() and user() but works fine on Stored Procedure. What I did is I create a Stored Procedure and call it inside the trigger.

Stored Procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_log`()
BEGIN
    INSERT INTO audit_trail 
    values( aud_no, 
           ( SELECT user_acc_id 
             from user_accounts 
             where user_username = REPLACE( user(), '@localhost', '')), 
           ( SELECT user_fname 
             from user 
             where user_id = ( SELECT user_acc_id 
                               from user_accounts 
                               where user_username = REPLACE( user(), '@localhost', ''))), 
           current_date(), 
           current_time(), 
           "logs in and out"); 
END

Trigger

CREATE DEFINER=`root`@`localhost` TRIGGER `user_log_AFTER_INSERT` AFTER INSERT ON `user_log` FOR EACH ROW BEGIN
        CALL insert_log();
END
| improve this answer | |
  • Instead of using REPLACE, use a simple SUBSTRING_INDEX – Rick James Oct 18 '18 at 15:22

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.