MySQL触发系统

Question

我正在尝试创建一个MySQL触发器来复制自动增量列。

自动增量列称为"iBmsId“，重复列被简单地称为"id”。

以下是我的疑问：

CREATE TRIGGER t_bms_ID_Update AFTER INSERT ON t_bms SET id = iBmsId

我在SQL中得到了以下错误：

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 'SET id = iBmsId' at line 1:

有什么想法吗？这是我第一次使用触发器。

Answer 1

您正在执行after insert，并且无法在触发器内更新相同的表。因此，您可能需要使用before insert，并且要为另一列设置auto_incremented主键值并不是直接的。

after insert触发器意味着插入已经发生，可以使用new和old关键字在插入之前使用插入的值或值。在您的示例中，您希望使用触发器中的主键值和after insert来模拟另一个列值，尽管您可以轻松地获得插入的主键值作为new.iBmsId，但是您不能更新相同的表。所以它需要before insert，如果你想要用触发器来完成的话。

在这里，你怎么做？

delimiter //
create trigger t_bms_ID_Update before insert on t_bms 
for each row
 begin
  SET new.id = (select iBmsId from t_bms order by iBmsId DESC LIMIT 1) + 1;
end ;//

delimiter ;

现在，如果您的表是空的，并且是第一次插入，那么使用上面的触发器，您可能会得到id值为null，所以更好的版本来处理它

delimiter //
create trigger t_bms_ID_Update before insert on t_bms 
for each row
 begin
  declare new_id int;
  SET new_id = (select iBmsId from t_bms order by iBmsId DESC LIMIT 1) + 1;
  if new_id is null then
   set new_id = 1;
  end if ;
  set new.id = new_id ;
end ;//

delimiter ;

这是一个测试用例，

mysql> create table test (id int, mimic int);
Query OK, 0 rows affected (0.15 sec)


mysql> delimiter //
mysql> create trigger test_ins before insert on test
    -> for each row 
    ->  begin
    ->  declare new_mimic int;
    ->  set new_mimic = (select id from test order by id desc limit 1) + 1 ;
    ->  if new_mimic is null then 
    ->   set new_mimic = 1 ;
    ->  end if;
    ->  set new.mimic = new_mimic;
    -> end ;//
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test values (1,0);
Query OK, 1 row affected (0.04 sec)

mysql> select * from test ;
+------+-------+
| id   | mimic |
+------+-------+
|    1 |     1 |
+------+-------+
1 row in set (0.00 sec)

mysql> insert into test values (2,0);
Query OK, 1 row affected (0.03 sec)

mysql> select * from test ;
+------+-------+
| id   | mimic |
+------+-------+
|    1 |     1 |
|    2 |     2 |
+------+-------+
2 rows in set (0.00 sec)