Using Auto-Increment value in MYSQL Before Insert Trigger?
PLEASE USE AFTER INSERT AND UPDATE
Do not make auto_increment any column you want to manipulate explicitly. That can confuse an engine and cause serious problems. If no column you have used for primary key are auto_increment you can do anything you want with them via triggers. Sure generated values will be rejected if they violate the mandatory uniqness of the primary key.
OP's comment:
How would I do it before, thou?
You can find current auto_increment
value that is to be assigned to a new record.
And use the same in the before
trigger as a parent user id for user_records
table.
You have to query information_schema.tables
table to find the value.
Example:
use `gknet`;
delimiter $$
drop trigger if exists before_create_user; $$
create definer=`root`@`localhost` trigger `before_create_user`
before insert on `users`
for each row begin
declare fk_parent_user_id int default 0;
select auto_increment into fk_parent_user_id
from information_schema.tables
where table_name = 'users'
and table_schema = database();
insert into user_records ( action, userid, timestamp )
values ( 'created', fk_parent_user_id, now() );
end;
$$
delimiter ;
Observations:
As per mysql documentation on last_insert_id(),
"if you insert multiple rows using a single INSERT statement,
LAST_INSERT_ID()
returns the value generated for the first inserted row only."
hence, depending on last_insert_id()
and auto_increment
field values in batch inserts seems not reliable.
Change the trigger to after insert
instead of before insert
and use NEW
to get the last inserted id
USE `gknet`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost`
TRIGGER `after_create_user` AFTER INSERT ON `users`
FOR EACH ROW
BEGIN
INSERT INTO user_records (action, userid, timestamp)
VALUES ('CREATED', NEW.ID, NOW() );
END; $$