关于数据库字段重复时，名称自动加1的问题
时间：2021-07-01 10:21:17
帮助过：49人阅读
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的问题
标签：ora blog 写代码 image varchar set class 个数 update
