没理解你的要求,建议举例说明。比如有一个表 ta (id int, dt datetime, c1 varchar(10)) 需要dt取当前时间
有一个oracle 的数据表 有 A B C D是4个字段,但采集程序的sql语句只INSERT了C D 两个字段的数据,A B 是空,我需要采集程序插入数据时 自动把 A = C ; B =D 插入到 当前记录
如果A,B跟C,D有不同的..那使用触发器..---------------- 如果是你说的 A = C ; B =D 的..那A,B的作用是什么?
ORACLE不清楚,MYSQL: 在TRIGGER中, SET NEW.A=NEW SET NEW.B=NEW.D不知道你为什么要这样做,直接UPDATE不就OK了?
用触器就行了。mysql> create table t_rigofree (a int,b int, c int , d int); Query OK, 0 rows affected (0.08 sec)mysql> mysql> delimiter | mysql> mysql> CREATE TRIGGER testref BEFORE INSERT ON t_rigofree -> FOR EACH ROW BEGIN -> if new.a is null then -> set new.a=new.c; -> end if; -> if new.b is null then -> set new.b=new.d; -> end if; -> END; -> | Query OK, 0 rows affected (0.11 sec)mysql> mysql> delimiter ; mysql> insert into t_rigofree(a,b,c,d) values (1,2,3,4); Query OK, 1 row affected (0.53 sec)mysql> select * from t_rigofree; +------+------+------+------+ | a | b | c | d | +------+------+------+------+ | 1 | 2 | 3 | 4 | +------+------+------+------+ 1 row in set (0.00 sec)mysql> insert into t_rigofree(c,d) values (33,44); Query OK, 1 row affected (0.03 sec)mysql> select * from t_rigofree; +------+------+------+------+ | a | b | c | d | +------+------+------+------+ | 1 | 2 | 3 | 4 | | 33 | 44 | 33 | 44 | +------+------+------+------+ 2 rows in set (0.00 sec)mysql>
你的mysql 版本是多少
ERROR 1064 (42000): 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 'TRIGG ER test1 BEFORE INSERT ON t_rigofree
mysql> select version(); +----------------------+ | version() | +----------------------+ | 5.1.33-community-log | +----------------------+ 1 row in set (0.06 sec)mysql>建议你最好象我一样整屏贴,否则根本不知道你输入的语句是不是和我的一样?
在MYSQL5。X版本应该没有 问题,你的代码及版本
ERROR 1064 (42000): 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 'TRIGG ER test1 BEFORE INSERT ON t_rigofree FOR EACH ROW BEGIN set new.a = new.c; ' at line 1
Microsoft Windows XP [版本 5.1.2600] (C) 版权所有 1985-2001 Microsoft Corp. mysql 4.1.9 C:\Documents and Settings\Administrator>d:D:\>cd EasyPHP1-8/mysql/binD:\EasyPHP1-8\mysql\bin>mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 55 to server version: 4.1.9-maxType 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> use test Database changed mysql> select * from t_rigofree; Empty set (0.02 sec)mysql> show columns from t_rigofree; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | a | int(11) | YES | | NULL | | | b | int(11) | YES | | NULL | | | c | int(11) | YES | | NULL | | | d | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 4 rows in set (0.01 sec)mysql> delimiter | mysql> CREATE TRIGGER test1 BEFORE INSERT ON t_rigofree -> FOR EACH ROW BEGIN -> if new.a is null then -> set new.a=new.c; -> end if; -> if new.b is null then -> set new.b=new.d; -> end if; -> END; -> | ERROR 1064 (42000): 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 'TRIGG ER test1 BEFORE INSERT ON t_rigofree FOR EACH ROW BEGIN if new.a' at line 1 mysql>
1.4. MySQL Development RoadmapFeature MySQL Series Unions 4.0 Subqueries 4.1 R-trees 4.1 (for the MyISAM storage engine) Stored procedures 5.0 Views 5.0 Cursors 5.0 XA transactions 5.0 Triggers 5.0 and 5.1 Event scheduler 5.1 从MySQL官方文档清晰可见,自5.0起开始支持触发器!
如果A,B跟C,D有不同的..那使用触发器..----------------
如果是你说的
A = C ; B =D
的..那A,B的作用是什么?
在TRIGGER中,
SET NEW.A=NEW
SET NEW.B=NEW.D不知道你为什么要这样做,直接UPDATE不就OK了?
Query OK, 0 rows affected (0.08 sec)mysql>
mysql> delimiter |
mysql>
mysql> CREATE TRIGGER testref BEFORE INSERT ON t_rigofree
-> FOR EACH ROW BEGIN
-> if new.a is null then
-> set new.a=new.c;
-> end if;
-> if new.b is null then
-> set new.b=new.d;
-> end if;
-> END;
-> |
Query OK, 0 rows affected (0.11 sec)mysql>
mysql> delimiter ;
mysql> insert into t_rigofree(a,b,c,d) values (1,2,3,4);
Query OK, 1 row affected (0.53 sec)mysql> select * from t_rigofree;
+------+------+------+------+
| a | b | c | d |
+------+------+------+------+
| 1 | 2 | 3 | 4 |
+------+------+------+------+
1 row in set (0.00 sec)mysql> insert into t_rigofree(c,d) values (33,44);
Query OK, 1 row affected (0.03 sec)mysql> select * from t_rigofree;
+------+------+------+------+
| a | b | c | d |
+------+------+------+------+
| 1 | 2 | 3 | 4 |
| 33 | 44 | 33 | 44 |
+------+------+------+------+
2 rows in set (0.00 sec)mysql>
corresponds to your MySQL server version for the right syntax to use near 'TRIGG
ER test1 BEFORE INSERT ON t_rigofree
+----------------------+
| version() |
+----------------------+
| 5.1.33-community-log |
+----------------------+
1 row in set (0.06 sec)mysql>建议你最好象我一样整屏贴,否则根本不知道你输入的语句是不是和我的一样?
corresponds to your MySQL server version for the right syntax to use near 'TRIGG
ER test1 BEFORE INSERT ON t_rigofree
FOR EACH ROW BEGIN
set new.a = new.c;
' at line 1
(C) 版权所有 1985-2001 Microsoft Corp.
mysql 4.1.9
C:\Documents and Settings\Administrator>d:D:\>cd EasyPHP1-8/mysql/binD:\EasyPHP1-8\mysql\bin>mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 55 to server version: 4.1.9-maxType 'help;' or '\h' for help. Type '\c' to clear the buffer.mysql> use test
Database changed
mysql> select * from t_rigofree;
Empty set (0.02 sec)mysql> show columns from t_rigofree;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(11) | YES | | NULL | |
| b | int(11) | YES | | NULL | |
| c | int(11) | YES | | NULL | |
| d | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.01 sec)mysql> delimiter |
mysql> CREATE TRIGGER test1 BEFORE INSERT ON t_rigofree
-> FOR EACH ROW BEGIN
-> if new.a is null then
-> set new.a=new.c;
-> end if;
-> if new.b is null then
-> set new.b=new.d;
-> end if;
-> END;
-> |
ERROR 1064 (42000): 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 'TRIGG
ER test1 BEFORE INSERT ON t_rigofree
FOR EACH ROW BEGIN
if new.a' at line 1
mysql>
Unions 4.0
Subqueries 4.1
R-trees 4.1 (for the MyISAM storage engine)
Stored procedures 5.0
Views 5.0
Cursors 5.0
XA transactions 5.0
Triggers 5.0 and 5.1
Event scheduler 5.1 从MySQL官方文档清晰可见,自5.0起开始支持触发器!