表中有FileName, UserName , FileId 三项
往表中插入FileName, UserName
一个FileName对应多个Usrname,想实现FileId的自增长,如下:
FileName UserName FileId
a 1 1
a 2 1
a 3 1
b 2 2
b 3 2
c 2 3我写了下面的触发器,显示语法错误,能告诉我下错在哪或有什么更好的解决办法么?CREATE TRIGGER `AddFileId` AFTER INSERT ON `userinformation`
FOR EACH ROW
as
begin
declare @NewId int,@i int,@NewFileName char(255)
select @NewFileName=Inserted.FlieName from Inserted
select @NewId=Inserted.Id from Inserted
if exists(select * from UserInformation where FileName=@NewFileName)
begin
select @i=select FileId form UserInformation where FileName=@NewFileName;
update UserInformation
set UserInformation.FileId=@i where UserInformation.Id=NewId;
end
else
begin
select @i=MAX(FileId) FROM UserInformation;
set @i=@i+i
update UserInformation
set UserInformation.FileId=@i where UserInformation.Id=NewId
end
end
第一次写触发器,实在没经验,谢谢帮忙了
往表中插入FileName, UserName
一个FileName对应多个Usrname,想实现FileId的自增长,如下:
FileName UserName FileId
a 1 1
a 2 1
a 3 1
b 2 2
b 3 2
c 2 3我写了下面的触发器,显示语法错误,能告诉我下错在哪或有什么更好的解决办法么?CREATE TRIGGER `AddFileId` AFTER INSERT ON `userinformation`
FOR EACH ROW
as
begin
declare @NewId int,@i int,@NewFileName char(255)
select @NewFileName=Inserted.FlieName from Inserted
select @NewId=Inserted.Id from Inserted
if exists(select * from UserInformation where FileName=@NewFileName)
begin
select @i=select FileId form UserInformation where FileName=@NewFileName;
update UserInformation
set UserInformation.FileId=@i where UserInformation.Id=NewId;
end
else
begin
select @i=MAX(FileId) FROM UserInformation;
set @i=@i+i
update UserInformation
set UserInformation.FileId=@i where UserInformation.Id=NewId
end
end
第一次写触发器,实在没经验,谢谢帮忙了
解决方案 »
- mssql数据库转成mysql数据库,表结构与表数据一同转换
- mysql修改数据问题
- 语句查询问题
- java程序中线程导致mysql数据库死锁
- ACMAIN_CHM !请教!
- MySQL NOT NULL / NULL
- mysql数据查询如何跳过为空的字段
- Linux9上phpMyAdmin连mysql:#2003 - Can't connect to MySQL server on '192.168.0.1' (111)
- 多表联合查询
- max_allowed_packet 值自动重置为1M
- 使用gunzip恢复备份文件报错
- 插入mysql数据库报Incorrect string value: '\xF0\x9F\x90\x82'错误
一张是fieldname, fieldid (fieldid设为自增长即可)
另一张是fieldid, username
select @NewFileName=Inserted.FlieName from Inserted MYSQL没有Inserted表
select @NewId=Inserted.Id from Inserted if exists(select * from UserInformation where FileName=@NewFileName) then ...
SET @b='';
SELECT *,@a:=IF(@b=FileName ,@a,@a+1),@b:=FileName FROM tt
SET @a=0;
SET @b='';
SELECT *,@a:=IF(@b=FileName ,@a,@a+1),@b:=FileName FROM tt
放到触发器了么?
没作用呀