After trigger start does not work in Mysql
I am trying to set a trigger in mysql. actually i am trying to insert the last inserted record from the notes table into the log_trigger but the below code is not working.
DELIMITER $$
CREATE TRIGGER `after_note`
AFTER INSERT ON `notes`
FOR EACH ROW
BEGIN
INSERT INTO log_trigger (NoteId, customerContact, customer, users, note, NoteCreatedTs)
SELECT Id, customerContact, customer, users, note, CreatedBy FROM notes ORDER BY id DESC LIMIT 1
END$$
DELIMITER ;
suggest
+3
source to share
2 answers
Instead of referencing a physical row from the actual notes table, refer to a pseudo-row New
(which will match the triggering table's column definitions):
INSERT INTO log_trigger (NoteId, customerContact, customer, users, note, NoteCreatedTs)
SELECT New.PostId, New.customerContact, New.customer, New.users, New.note, New.CreatedBy;
New
will already contain the newly inserted row - you no longer need to search for it.
+1
source to share
You don't need to use select
to fetch the latest data from the same table where the trigger is used. You can use a keyword new
for the same.
delimiter //
create trigger after_note after insert on notes
for each row
begin
insert into log_trigger
(NoteId, customerContact, customer, users, note, NoteCreatedTs)
values
(new.id,new.customerContact,new.customer,new.users,new.note,new.CreatedBy);
end;//
delimiter ;
+2
source to share