MySQL uses triggers to prohibit the modification and deletion of a record

Scenario:
There are very important records in this table. This record cannot be modified or deleted. Don't talk about setting permissions or anything, it does not meet the requirements of superiors. Preventing modification is very simple, just order new.xxx=old.xxx in the trigger, and delete requires rollback.

in principle:

Unfortunately, the insert, update, and delete operations on this table cannot be performed in the trigger in MYSQL to avoid recursive loop triggering.

For modifying data, you can only use set to operate before the operation (before), insert and delete can only use the second table to achieve the desired purpose

The structure of the table is as follows:

Insert picture description here


suppose the record with id 8 is not allowed to be modified or deleted:

Insert picture description here


the record with id 8 is not allowed to be modified:

delimiter //
CREATE TRIGGER bli_haha 
 BEFORE UPDATE ON `bas_list_info`
 FOR EACH ROW 
 BEGIN  
 IF NEW.id = 8 THEN
 SET NEW.remarks = OLD.remarks,NEW.active= OLD.active ;
 END IF;
 END; //

result:

Insert picture description here

The record with id 8 is not allowed to be deleted (using error report):

delimiter //
create procedure rb()
begin
rollback;
end //

delimiter //
create trigger trigger1 before delete on `bas_list_info`
for each row
begin
if old.id=8 then
call rb ;
end if ;
end //

In this way, an error is reported when the record is deleted, and it cannot be deleted

result:

Insert picture description here


Insert picture description here