Menu
It's my understanding that when you call last_insert_id() it goes by connections, so you'll get the id of the last row inserted on the same connection where last_insert_id() is called, right?
So what if I call last_insert_id() in an 'AFTER INSERT' trigger?
What I want to do is basically this
DELIMITER $$
CREATE TRIGGER sometrigger
AFTER INSERT ON sometable
BEGIN
INSERT INTO anothertable (id, lastup) VALUES (last_insert_id(), NOW());
END $$
It's very important that the id in 'anothertable' is the same as in 'sometable' Would this work or should I create a stored procedure instead that inserts into both tables?
Or possibly there is some, in the trigger, to get the values from the insert statement that caused the trigger to fire? I haven't found anything about that.
516
The LAST_INSERT_ID() function returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table.
By using triggers that run before an update or insert, values that are being updated or inserted can be modified before the database is actually modified. These can be used to transform input from the application (user view of the data) to an internal database format where desired.
Use the BEGIN and END delimiters to indicate the trigger body: CREATE TRIGGER <trigger name> <trigger time > <trigger event> ON <table name> FOR EACH ROW BEGIN <trigger body>; END; Make sure to change the default delimiter before creating a trigger with multiple operations.
An AFTER INSERT Trigger means that MySQL will fire this trigger after the INSERT operation is executed.
You should be able to use NEW.{id column name} to refer to the last insert id (so for example NEW.id if the auto increment column is called id.)
85
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
