首页 > 代码库 > 关于数据库字段重复时,名称自动加1的问题

关于数据库字段重复时,名称自动加1的问题

之前项目(类似网盘类的系统)中遇到的一个问题,每次创建一个新文件夹时在新文件夹(+1递增),我的解决方案是在数据库表中加触发器,这能减少代码的开发量,不用书写代码逻辑

触发器如下:

CREATE trigger [dbo].[tr_folder_after]
on [dbo].[storage_FolderUser]
    after insert
as
--@count:同名文件夹的个数,@newname新文件夹的名称
declare @count int,@newname nvarchar(20),@id int,@parentid int,@name nvarchar(20),@creator int;
select @id=id,@parentid=FolderID,@name=Name,@creator=Creator from Inserted;
select @count=count(ID) from [dbo].[storage_FolderUser] where Creator=@creator AND FolderID=@parentid AND Name=@name
if @count>1
begin
    set @newname=@name;
    set @count=0;
    while @count>=0
    begin
        if exists(select * from [dbo].[storage_FolderUser] where Creator=@creator AND FolderID=@parentid AND Name=@newname)
        begin
            set @count=@count+1;
            set @newname=@name+(+convert(varchar, @count)+);
        end
        else
        begin
            set @count=-1;
        end
    end
    UPDATE [dbo].[storage_FolderUser] SET Name=@newname WHERE ID=@id;
end

结果如下:

技术分享

将 新建文件夹(1) delete 后,再次执行插入数据后将依然按增长顺序补上

delete from [dbo].[storage_FolderUser] where id=2
insert into [dbo].[storage_FolderUser](FolderID,Name,Creator,Created)values(0,新建文件夹,626,getdate())

技术分享

 

关于数据库字段重复时,名称自动加1的问题