Re: How to prevent data deletion from MySQL table

[Peter Brawley]

Calling a non-existent procedure from a Delete Trigger will work, but it's a hack and may have undesirable side-effects, for example if the deletion command occurs in a transaction, all cmds in the transaction will roll back.

The most straightforward method is to withhold DELETE permission for that table from all users except perhaps yourself.

Next alternative will require a bit of code: create a guard table that references the table in a foreign key constraint ...

create table table1guard( table1ID int primary key, foreign key(table1ID) references table1(table1ID);

... and use Triggers to ensure that all table1 primary keys exist in table1guard.