CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = test1.a1;
DELETE FROM test3 WHERE a3 = test1.a1;
UPDATE test4 SET b4 = b4+1 WHERE a4 =test1.a1;
END
错误提示如下:
(影响 0 条记录)
(耗费 62 ms)(影响 0 条记录)
(耗费 63 ms)(影响 0 条记录)
(耗费 62 ms)(影响 0 条记录)
(耗费 63 ms)错误代码: 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 '' at line 3
(耗费 0 ms)错误代码: 1054
Unknown column 'test1.a1' in 'where clause'
(耗费 0 ms)错误代码: 1054
Unknown column 'test1.a1' in 'where clause'
(耗费 0 ms)错误代码: 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 1
(耗费 0 ms)
刚接触MYSQL看的中文版的帮助文档,我是按文档中的代码输入的,但是报错了。。 悲催的
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = test1.a1;
DELETE FROM test3 WHERE a3 = test1.a1;
UPDATE test4 SET b4 = b4+1 WHERE a4 =test1.a1;
END
错误提示如下:
(影响 0 条记录)
(耗费 62 ms)(影响 0 条记录)
(耗费 63 ms)(影响 0 条记录)
(耗费 62 ms)(影响 0 条记录)
(耗费 63 ms)错误代码: 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 '' at line 3
(耗费 0 ms)错误代码: 1054
Unknown column 'test1.a1' in 'where clause'
(耗费 0 ms)错误代码: 1054
Unknown column 'test1.a1' in 'where clause'
(耗费 0 ms)错误代码: 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 1
(耗费 0 ms)
刚接触MYSQL看的中文版的帮助文档,我是按文档中的代码输入的,但是报错了。。 悲催的
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4+1 WHERE a4 =NEW.a1;
END
Query OK, 0 rows affected (0.43 sec)mysql> CREATE TABLE test2(a2 INT);
Query OK, 0 rows affected (0.01 sec)mysql> CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.02 sec)mysql> CREATE TABLE test4(
-> a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> b4 INT DEFAULT 0
-> );
Query OK, 0 rows affected (0.01 sec)mysql>
mysql>
mysql> delimiter //
mysql> CREATE TRIGGER testref BEFORE INSERT ON test1
-> FOR EACH ROW BEGIN
-> INSERT INTO test2 SET a2 = test1.a1;
-> DELETE FROM test3 WHERE a3 = test1.a1;
-> UPDATE test4 SET b4 = b4+1 WHERE a4 =test1.a1;
-> END//
Query OK, 0 rows affected (0.18 sec)mysql> delimiter ;
mysql>
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = new.a1;
DELETE FROM test3 WHERE a3 = new.a1;
UPDATE test4 SET b4 = b4+1 WHERE a4 =new.a1;
END
这样是没有错误的。
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);-- 在这之前加上间隔符
delimiter $$
use test $$
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW
BEGIN
INSERT INTO test2 SET a2 = test1.a1;
DELETE FROM test3 WHERE a3 = test1.a1;
UPDATE test4 SET b4 = b4+1 WHERE a4 =test1.a1;
END
激活触发程序时,对于触发程序引用的所有OLD和NEW列,需要具有SELECT权限,对于作为SET赋值目标的所有NEW列,需要具有UPDATE权限。
1.DELIMITER的应用
2.OLD.XXX和NEW.XXX分别表示旧行中某列和新行中某列的意思