Menu
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;
|
1000
We can create a new trigger in MySQL by using the CREATE TRIGGER statement. It is to ensure that we have trigger privileges while using the CREATE TRIGGER command. The following is the basic syntax to create a trigger: CREATE TRIGGER trigger_name trigger_time trigger_event.
Answer: NEW. qty references the qty on the table that the trigger is set on, not the table that is being updated. CREATE TRIGGER after_sales_insert AFTER INSERT ON sales FOR EACH ROW BEGIN UPDATE products SET qty = qty - NEW.
Notice that triggers are stored in the database separately from their associated tables. Triggers can be defined only on tables, not on views. However, triggers on the base table(s) of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against a view.
A trigger can be set to activate either before or after the trigger event. For example, you can have a trigger activate before each row that is inserted into a table or after each row that is updated. MySQL triggers activate only for changes made to tables by SQL statements.
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
57
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
