有一个例子看看吧
以下是一条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 ;
以下是一条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 ;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货