CODEWITHSUNDEEP
mysqltriggers
Rikhi Sahu
Rikhi Sahu

Reputation: 655

After Insert trigger not working in Mysql

i am trying to set trigger in mysql. actually i am trying to insert last inserted record from notes table into log_trigger but 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 ;

please suggest

Upvotes: 3

Views: 1409

Answers (2)

StuartLC
StuartLC

Reputation: 107237

Instead of referencing the physical row from the actual notes table, reference the New pseudo row (which will match the column definitions of the underlying table being triggered):

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 don't need to find it again.

SqlFiddle here

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You do not need to use select to select last data from the same table where the trigger is used. You can use new keyword 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 ;

Upvotes: 2

Related Questions