0

How to reject delete statement in before delete trigger (mysql) ?

Sample use case : I have a record with specified id that I want to will not be deleted.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Behrouz.M
  • 3,445
  • 6
  • 37
  • 64

2 Answers2

2

Look here, same thing but with an insert-trigger: How to abort INSERT operation in MySql trigger?

Community
  • 1
  • 1
esaj
  • 15,875
  • 5
  • 38
  • 52
2

You could create a table that uses a foreign key constrait referencing your ID column (provided that is the primary key of your table). The insert a row into that "child" table that references ID = 0 and remove all update/delete privileges for your user from that table.

Now if someone tries to delete the row with ID = 0 in the "parent" table that will fail due to an existing child row in the other table.

  • this is very bad idea ( at least for me, because we have more than 130 table in our db) – Behrouz.M Feb 27 '11 at 10:53
  • @Behrouz: Does this mean you need this "protection" for every table? Sounds like a very strange setup then.. –  Feb 27 '11 at 11:02
  • not for each tables, but I have a lot of relations between tables, and this solutions make it more complex. I wrote a ORM , that generates SOAP web services, adding an extra useless column, make out webservices ugly and error prone – Behrouz.M Feb 27 '11 at 11:08
  • You don't add an extra *column* (because the column **id** is already there) you add an extra *table* which can totally be ignored by your ORM as it only serves to purpose to prevent deletion. –  Feb 27 '11 at 11:29