Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

update table column after insert new record using MySQL triggers

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.

like image 677
Mohammad Saberi Avatar asked Mar 12 '14 05:03

Mohammad Saberi


People also ask

Can we use update in trigger?

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.

How do you create a trigger after insert?

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.

How do you execute a trigger only when a specific column is updated MySQL?

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.

Can we update a trigger in MySQL?

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.


1 Answers

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.

like image 110
Ravinder Reddy Avatar answered Oct 15 '22 12:10

Ravinder Reddy