有一张表class里面有班级号cid，班主任姓名name,我想在插入数据的时候触发，当新插入一条数据时，如果cid在表中已经存在，那该cid对应的班主任姓名就更新成当前插入name，（比如表中已经由了班级号001，班主任张三，我新插入的是001，李四，那么触发之后的结果变成表中班级号001，班主任李四）这个触发器该怎么写啊我写的有问题：
CREATE
TRIGGER `TGR_UpdateClass2` BEFORE INSERT ON `t_class`
FOR EACH ROW BEGIN
DECLARE @cid;
DECLARE @name;
IF EXISTS(SELECT 1 FROM t_class WHERE cid = @cid)
THEN
UPDATE t_class SET new.name = @name WHERE @cid = new.cid
END IF;
END;
$$
应该怎么改，求帮助~~急用
如果写成删除会报这个错误，
误代码： 1442
Can't update table 't_class' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
TRIGGER `TGR_UpdateClass2` BEFORE INSERT ON `t_class`
FOR EACH ROW BEGIN
IF EXISTS(SELECT 1 FROM t_class WHERE cid = new.cid)
THEN
UPDATE t_class SET name = new.name WHERE cid = new.cid
END IF;
END;追问
这样写会报错：错误代码： 1064
追答是的使用触发器更新同一个表是会碰到问题，可以考虑使用存储过程试试：
mysql> delimiter ||
mysql> create procedure p_UpdateClass2(in i_cid int,in i_name varchar(10))
-> begin
-> if exists(select 1 from t_class where cid=i_cid) then
-> update t_class set name=i_name where cid=i_cid;
-> end if;
-> insert into td values(i_cid,i_name);
-> end;
-> ||
Query OK, 0 rows affected (0.00 sec)
mysql> call p_td(1,'x');
-> ||
Query OK, 1 row affected (0.00 sec)
mysql> select * from td ||
6 rows in set (0.00 sec)
mysql>
