MySQL trigger to insert into another table before delete operation

Question

I've written something like this and this is not working. How?

mysql> create trigger deleteStudentTrigger
-> before delete on tblStudent for each row
-> begin
-> insert into tblDeletedStudents select * from tblStudent where StudentId = new.StudentId;
-> end;$

ERROR 1363 (HY000): There is no NEW row in on DELETE trigger

The error message says it all!

jarlh
– jarlh

2017-05-08 07:11:44 +00:00
Commented May 8, 2017 at 7:11 — jarlh
– jarlh, Commented May 8, 2017 at 7:11
Yeah, I know. I posted that to tell what I tried.

Mohith Kovela
– Mohith Kovela

2017-05-08 08:29:04 +00:00
Commented May 8, 2017 at 8:29 — Mohith Kovela
– Mohith Kovela, Commented May 8, 2017 at 8:29

Alexey · Accepted Answer · 2017-05-08 07:13:58Z

3

As stated in the error, there is no NEW row, only OLD (because when you delete something, it's not new you know) So it should be

mysql> create trigger deleteStudentTrigger
-> before delete on tblStudent for each row
-> begin
-> insert into tblDeletedStudents select * from tblStudent where StudentId = old.StudentId;
-> end;$

For more info refer to MySQL documentation

answered May 8, 2017 at 7:13

Alexey

2,4881 gold badge21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mohith Kovela Over a year ago

No error. But INSERT operation to tblDeletedStudents not happening.

jarlh Over a year ago

Shouldn't it be insert into tblDeletedStudents values (old.StudentId, old.anothercolumn, ...)?

Alexey Over a year ago

insert select... is also possible. Well in your case I would test separately if the insert itself is ok (meaning table schema is the same for both tables)

Collectives™ on Stack Overflow

MySQL trigger to insert into another table before delete operation

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related