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

乌哩码嗏 网页设计师  2010-02-11 02:01:13
以下是一条SqlServer2000的触发器语句,现希望转换成mysql5.0的触发器语句

该语句主要实现在Product表中插入一条记录时,将主键“Product_id”变成“XX001”的形式

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


静候各位高手回音~!
...全文
54 点赞 收藏 8
写回复
8 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
lissh2 2010-02-12
B明········
回复
乌哩码嗏 2010-02-12
呵呵~又是你~
算啦,总算解决,都给你了
回复
ACMAIN_CHM 2010-02-12
[code=SQL]mysql> create table t_zqaq_520(product_id char(5) primary key default '',col int);
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> delimiter |
mysql>
mysql> CREATE TRIGGER tr_t_zqaq_520_bi BEFORE INSERT ON t_zqaq_520
-> 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> insert into t_zqaq_520 (col) values (1);
Query OK, 1 row affected (0.08 sec)

mysql> select * from t_zqaq_520;
+------------+------+
| product_id | col |
+------------+------+
| CP001 | 1 |
+------------+------+
1 row in set (0.00 sec)

mysql> insert into t_zqaq_520 (col) values (2);
Query OK, 1 row affected (0.06 sec)

mysql> select * from t_zqaq_520;
+------------+------+
| product_id | col |
+------------+------+
| CP001 | 1 |
| CP002 | 2 |
+------------+------+
2 rows in set (0.00 sec)

mysql>
[/code]
回复
Siramizu 2010-02-11
不会sql server,根据你说的
该语句主要实现在Product表中插入一条记录时,将主键“Product_id”变成“XX001”的形式
如下

DELIMITER $$
DROP TRIGGER IF EXISTS `st_product`$$/*删除现有触发器*/
CREATE TRIGGER `st_product`/*替换成你想要的触发器名*/ BEFORE INSERT ON `Product`/*替换成实际的表名*/ FOR EACH ROW
BEGIN
SET NEW.`Product_id` = CONCAT('XX', LPAD(CONCAT(NEW.`Product_id`), 3, '0'));
END$$
DELIMITER ;
回复
乌哩码嗏 2010-02-11
回楼上的~
我用的是MYSQL5.0
回复
ACMAIN_CHM 2010-02-11
你的MYSQL是什么版本的?
回复
乌哩码嗏 2010-02-11
补充一下~
我实际是想该语句主要实现在Product表中插入一条记录时,将主键“Product_id”变成“CP001”、“CP002”.....的形式
回复
乌哩码嗏 2010-02-11
感谢楼上的仁兄~但是语句报错哦~
我输入如下:
DELIMITER $$
DROP TRIGGER IF EXISTS `st_product`$$
CREATE TRIGGER `bookstore`.`st_product` BEFORE INSERT ON `Product` FOR EACH ROW
BEGIN
SET NEW.`Product_id` = CONCAT('XX', LPAD(CONCAT(NEW.`Product_id`), 3, '0'));
END$$
DELIMITER ;

出错如下:
Script line: 2 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS `st_product`' at line 1
回复
相关推荐
发帖
MySQL
创建于2007-09-28

5.5w+

社区成员

MySQL相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2010-02-11 02:01
社区公告
暂无公告