Question :
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
Answer :
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