--> 测试数据
create table tb (id int auto_increment primary key ,name varchar(100))--要求:
--向表中插入记录时,当name='张三' 或者'李四'
--入一条记录 name=‘王五'
--下面是我写的
create trigger t
after insert on tb
for each row
begin
if new.name='张三' or new.name='张三'
insert into tb(name) values('李四')
end if
end
/*
错误码: 1064
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 'insert into tb(name) values('李四')
end if
end' at line 6*/
帮忙看一下
create table tb (id int auto_increment primary key ,name varchar(100))--要求:
--向表中插入记录时,当name='张三' 或者'李四'
--入一条记录 name=‘王五'
--下面是我写的
create trigger t
after insert on tb
for each row
begin
if new.name='张三' or new.name='张三'
insert into tb(name) values('李四')
end if
end
/*
错误码: 1064
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 'insert into tb(name) values('李四')
end if
end' at line 6*/
帮忙看一下
after insert on tb
for each row
begin
if new.name='张三' or new.name='张三' then
insert into tb(name) values('李四');
end if;
end
after insert on tb
for each row
begin
if new.name='张三' or new.name='张三' then
insert into tb(name) values('李四');
end if;
end
/*
错误码: 1064
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 'insert into tb(name) values('李四')' at line 6Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000错误码: 1064
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 'end if' at line 1Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000错误码: 1064
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 'end' at line 1Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
*/
mysql>delimiter $$
mysql> create trigger t before insert on tb for each row
-> begin
-> if new.name='张三' or new.name='李四' then
-> set new.name='李四';
-> end if;
-> end $$
mysql> create table tb (id int auto_increment primary key ,name varchar(100));
Query OK, 0 rows affected (0.09 sec)mysql>
mysql> delimiter //
mysql> create trigger t after insert on tb
-> for each row
-> begin
-> if new.name='张三' or new.name='张三' then -- 缺少 then
-> insert into tb(name) values('李四'); -- 缺少分号
-> end if;
-> end//
Query OK, 0 rows affected (0.06 sec)mysql> delimiter ;
mysql> insert into tb(name) values ('张三');
ERROR 1442 (HY000): Can't update table 'tb' in stored function/trigger because i
t is already used by statement which invoked this stored function/trigger.
mysql>
mysql> create trigger t before insert on tb for each row
-> begin
-> if new.name='张三' or new.name='李四' then
-> set new.name='李四';
-> end if;
-> end
-> $$
Query OK, 0 rows affected (0.03 sec)mysql> delimiter ;
mysql> insert into tb (name) values ('张三');
Query OK, 1 row affected (0.01 sec)mysql> select *from tb;
+----+------+
| id | name |
+----+------+
| 1 | 李四 |
+----+------+
1 row in set (0.00 sec)
create trigger tr_main_aaa
before insert
on t_aaa for each row
begin
delete from t_aaa where a_id=2;
end 无法操作本表,比较纠结啊,斑竹给解释下,触发器无法操作本表,是不是和锁有关系
大侠那我的逻辑如何实现?
本想用 event的 但是 版本太低
before insert:
set new.name='123'
方法二: 触发器中,插入到另一张结构完全相同的表,然后查询时使用 union all 总之这种需求很怪异! 业务上的逻辑应该在程序中实现而不是数据库层。
第二个方法不行,呵呵
我们公司让我们必须每天都得登陆公司的oa,连续两天不登陆的罚款,
说到这你知道我想干什么了吧
另征集定时执行mysql数据库任
http://topic.csdn.net/u/20091020/22/c51bb709-6dbc-4fd1-b874-077714ed547e.html