求一条mysql5.0的触发器语句

[乌哩码嗏]

语句主要实现在“Product”表中插入一条记录时，将主键“Product_id”变成“CP001”、“CP002”.....的形式

[乌哩码嗏]

product_id为char(5)

[ACMAIN_CHM]

你的 insert 语句是什么？

[ACMAIN_CHM]

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>

[ACMAIN_CHM]

其实你自己看一下MYSQL文档中的例子就应该可以写出来了。

MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html

[乌哩码嗏]

呵呵~成功了~
其实我对sQL并不太熟悉呀~
谢谢楼上的仁兄~分给你了!

[百年树人]

Product_id原来的形式是什么

不一定要用触发器，用函数也可以