CODEWITHSUNDEEP
mysql
user1956527
user1956527

Reputation: 73

Trigger to prevent delete on table

How can I prevent deletion of any row for specific tables in MySQL 5.1?

I don't need any where criteria, I simply do not want any row in these tables to be deleted.

Must be done on the MySQL level, as we also use bought software that accepts deletion. We use MySQL 5.1.66-cll

I have tried to tweak different triggers out there for this, but i am not able to succeed.

Upvotes: 4

Views: 5224

Answers (1)

Ike Walker
Ike Walker

Reputation: 65587

As others have mentioned in the comments, the proper way to do this is usually via table level grants.

However, it can be accomplished with a trigger as you requested.

One way to do this is to use a "before delete" trigger, and execute a simple SQL statement that will always cause an error. For example, you can call a non-existent stored procedure like "do_not_delete".

Try something like this:

delimiter $$

drop trigger if exists tr_your_table_no_deletes $$

create trigger tr_your_table_no_deletes before delete on your_table for each row begin
  call do_not_delete();
end $$

delimiter ;

Upvotes: 2

Related Questions