Imagine I have a MySQL table (tbl_test) with these fields: id, title, priority.
id will be incremented automatically. I need to fill priority field with a value as same as id field after inserting.
As I'm new in using MySQL triggers, please tell me what I have to write for it. I did something , but I think it is not true:
CREATE TRIGGER 'test' AFTER INSERT ON `tbl_test`
BEGIN
SET new.priority = new.id;
END
Thanks for your assistance.
AFTER UPDATE Trigger in SQL is a stored procedure on a database table that gets invoked or triggered automatically after an UPDATE operation gets successfully executed on the specified table. For uninitiated, the UPDATE statement is used to modify data in existing rows of a data table.
First, specify the name of the trigger that you want to create after the CREATE TRIGGER keywords. Second, use AFTER INSERT clause to specify the time to invoke the trigger. Third, specify the name of the table on which you want to create the trigger after the ON keyword.
In SQL Server, you can create DML triggers that execute code only when a specific column is updated. The trigger still fires, but you can test whether or not a specific column was updated, and then run code only if that column was updated. You can do this by using the UPDATE() function inside your trigger.
MySQL AFTER UPDATE triggers are invoked automatically after an update event occurs on the table associated with the triggers. In this syntax: First, specify the name of the trigger that you want to create in the CREATE TRIGGER clause. Second, use AFTER UPDATE clause to specify the time to invoke the trigger.
The way you are trying to set value to a column is an update. Because you are doing it after insert operation is completed.
You actually need a before
trigger.
And to assign the same new auto incremented value of primary key column of same table, you better get it from information_schema.tables
.
Example:
delimiter //
drop trigger if exists bi_table_name //
create trigger bi_table_name before insert on table_name
for each row begin
set @auto_id := ( SELECT AUTO_INCREMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='table_name'
AND TABLE_SCHEMA=DATABASE() );
set new.priority= @auto_id;
end;
//
delimiter ;
Note: Make sure that you don't have any pre-defined trigger with the same name and/or action. If have some, then drop them before creating the new.
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.
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