一条SqlServer2000的触发器语句转换为mysql5.0的触发器语句

0 0

一条SqlServer2000的触发器语句转换为mysql5.0的触发器语句10

以下是一条SqlServer2000的触发器语句，现希望转换成mysql5.0的触发器语句

该语句主要实现在Product表中插入一条记录时，将主键“Product_id”变成“CP001”、“CP002”.....的形式
,只要是能实现这个功能就行了！！！

CREATE trigger Product_id
on dbo.Product
instead OF insert
as
begin
declare @preStr varchar(10)
declare @tempID varchar(16)
declare @preTl varchar(2)
select * into #temptb from inserted
select @preTl=max(Product_id ) from inserted
set @preStr=@preTl
declare @sequNum int
select @tempID=max(Product_id ) from Product where Product_id like @preStr+'%'
if(@tempID is null)
set @sequNum=1
else
set @sequNum=cast(subString(@tempID,3,3) as int)+1
set @tempID=right('000'+cast(@sequNum as varchar),3)
set @tempID=@preStr+@tempID
update #temptb set Product_id =@tempID
insert into Product select * from #temptb
end

静候各位高手回音~！

问题补充：答案如下：

mysql>
mysql> delimiter |
mysql>
mysql> CREATE TRIGGER tr_t_product_bi BEFORE INSERT ON product
    ->   FOR EACH ROW BEGIN
    ->          declare max_id int;
    ->
    ->          select substring(max(product_id),3) into max_id from t_zqaq_520;

    ->          if max_id is null then
    ->                  set max_id=0;
    ->          end if;
    ->          set max_id=max_id+1;
    ->          set new.product_id = concat('CP',right(10000+max_id,3));
    ->   END;
    -> |
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> delimiter ;

MySQL 数据库

2010年2月11日 18:21

zqaq520
5
0 0 0

2个答案按时间排序按投票排序

0 0

采纳的答案

create trigger update_product_primary_key before insert on Product for each row begin select count(*) into @count from Product; set new.Product_id =concat('CP',lpad(@count + 1,3,'0')); end;

2010年2月12日 15:04

Ihavegotyou
945
0 0 0

添加评论

0 0

create trigger update_product_primary_key before insert on Product for each row begin select count(*) into @count from Product; set new.Product_id = concat('CP000',@count + 1); end;

2010年2月12日 14:35

Ihavegotyou
945
0 0 0

添加评论

已解决问题

未解决问题

排行榜

查看全部排名>>