I have a registration table, in which I stored details of super admin and admin and other users also. my question is even If I perform a truncate operation on that table, still, the super admin and admin entries should not be removed. It has to be there always as a default entry.

Here is my registration table.

So, I want to freeze the entry of super admin and admin with the ID and password and It should not be changed.

Store procedure I tried

CodePudding user response：

If you really want to truncate and still retain certain line, use a temporary table to keep the rows intended to stay. Then insert them back to the registration table. This can be done in a procedure. My apologies in advance as my page has trouble displaying the image so the base table name and all its column names are purely based on assumptions.

create table registration (id int primary key auto_increment,user varchar(20));
insert registration (user) values ('admin'),('root'),('super admin');
delimiter //
drop procedure if exists truncate_reg//
create procedure truncate_reg()
begin
drop temporary table if exists temp_registration;
create temporary table temp_registration select * from registration where user='admin' or user='super admin';
truncate registration ;
insert registration select * from temp_registration;

end//
delimiter ;
call truncate_reg;
select * from registration;
-- result:
1, admin
3, super admin