Use mysql user in trigger

Question

I'm creating a trigger in MySQL to let me know WHO (which mysql user) is doing an update on a specific table.

I know MySQL has a function CURRENT_USER(), but is that going to insert the username at the time the trigger is CREATED, or at the time the trigger is CALLED?

This is my trigger so far. I want to insert the username in the 'content' column.

delimiter |
CREATE TRIGGER update_product_procedure 
BEFORE UPDATE ON product_procedure 
FOR EACH ROW BEGIN 
INSERT INTO trigger_logs SET 
content = 'This is a test', postDate=NOW(); 
END;
|

Interesting question. My initial guess is that it will be the invoker, but I'll follow the question to see the real answer. — Kevin Peno
– Kevin Peno, Commented Nov 19, 2009 at 20:44

blackanchorage · Accepted Answer · 2009-11-19 21:18:57Z

6

I would have put lots of money on it being the invoker, but from http://bugs.mysql.com/bug.php?id=5861:

SQL standard says that: "A triggered action is always executed under the authorization of the owner of the schema that includes the trigger." This means that in MySQL we should execute trigger body under authorization of user who created trigger and not the one who issued statement which invoked this trigger.

Apologies, I assumed it was an obvious question :-(

Regards

EDIT: user() gives the invoker

answered Nov 19, 2009 at 21:18

blackanchorage

1143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nathan H Over a year ago

Thanks. No worries. I picked USER(), which I didn't know existed.

Kevin Peno Over a year ago

Thanks for taking the time to research. I also didn't know about the USER(). Good to know :D

wmorse Over a year ago

Probably obvious, but being able to get the user whose permissions apply to the trigger with CURRENT_USER() is also good to know.

Collectives™ on Stack Overflow

Use mysql user in trigger

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related