trigger.result

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· RESULT 代码 · 共 1,965 行 · 第 1/4 页

drop table t1;
create table t1 (i int);
create trigger t1_bi before insert on t1 for each row return 0;
ERROR 42000: RETURN is only allowed in a FUNCTION
insert into t1 values (1);
drop table t1;
create table t1 (a varchar(64), b int);
create table t2 like t1;
create trigger t1_ai after insert on t1 for each row
set @a:= (select max(a) from t1);
insert into t1 (a) values
("Twas"),("brillig"),("and"),("the"),("slithy"),("toves"),
("Did"),("gyre"),("and"),("gimble"),("in"),("the"),("wabe");
create trigger t2_ai after insert on t2 for each row
set @a:= (select max(a) from t2);
insert into t2 select * from t1;
load data infile '../std_data_ln/words.dat' into table t1 (a);
drop trigger t1_ai;
drop trigger t2_ai;
create function f1() returns int return (select max(b) from t1);
insert into t1 values
("All",f1()),("mimsy",f1()),("were",f1()),("the",f1()),("borogoves",f1()),
("And",f1()),("the",f1()),("mome", f1()),("raths",f1()),("outgrabe",f1());
create function f2() returns int return (select max(b) from t2);
insert into t2 select a, f2() from t1;
load data infile '../std_data_ln/words.dat' into table t1 (a) set b:= f1();
drop table t1, t2;
drop function f1;
drop function f2;
create table t1(i int not null, j int not null, n numeric(15,2), primary key(i,j));
create table t2(i int not null, n numeric(15,2), primary key(i));
create trigger t1_ai after insert on t1 for each row
begin
declare sn numeric(15,2);
select sum(n) into sn from t1 where i=new.i;
replace into t2 values(new.i, sn);
end|
insert into t1 values
(1,1,10.00),(1,2,10.00),(1,3,10.00),(1,4,10.00),(1,5,10.00),
(1,6,10.00),(1,7,10.00),(1,8,10.00),(1,9,10.00),(1,10,10.00),
(1,11,10.00),(1,12,10.00),(1,13,10.00),(1,14,10.00),(1,15,10.00);
select * from t1;
i	j	n
1	1	10.00
1	2	10.00
1	3	10.00
1	4	10.00
1	5	10.00
1	6	10.00
1	7	10.00
1	8	10.00
1	9	10.00
1	10	10.00
1	11	10.00
1	12	10.00
1	13	10.00
1	14	10.00
1	15	10.00
select * from t2;
i	n
1	150.00
drop tables t1, t2;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
conn_id INT,
trigger_conn_id INT
);
CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
SET NEW.trigger_conn_id = CONNECTION_ID();
INSERT INTO t1 (conn_id, trigger_conn_id) VALUES (CONNECTION_ID(), -1);
INSERT INTO t1 (conn_id, trigger_conn_id) VALUES (CONNECTION_ID(), -1);
SELECT * FROM t1 WHERE conn_id != trigger_conn_id;
conn_id	trigger_conn_id
DROP TRIGGER t1_bi;
DROP TABLE t1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (i1 INT);
SET @save_sql_mode=@@sql_mode;
SET SQL_MODE='';
CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW
SET @x = 5/0;
SET SQL_MODE='traditional';
CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
SET @x = 5/0;
SET @x=1;
INSERT INTO t1 VALUES (@x);
SELECT @x;
@x
NULL
SET @x=2;
UPDATE t1 SET i1 = @x;
Warnings:
Error	1365	Division by 0
SELECT @x;
@x
NULL
SET SQL_MODE='';
SET @x=3;
INSERT INTO t1 VALUES (@x);
SELECT @x;
@x
NULL
SET @x=4;
UPDATE t1 SET i1 = @x;
Warnings:
Error	1365	Division by 0
Error	1365	Division by 0
SELECT @x;
@x
NULL
SET @@sql_mode=@save_sql_mode;
DROP TRIGGER t1_ai;
DROP TRIGGER t1_au;
DROP TABLE t1;
DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
CREATE TABLE t1 (i1 INT);
INSERT INTO t1 VALUES (3);
CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET i1 = 5;
CREATE PROCEDURE p2(INOUT i1 INT) DETERMINISTIC NO SQL SET i1 = i1 * 7;
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
BEGIN
CALL p1(NEW.i1);
CALL p2(NEW.i1);
END//
UPDATE t1 SET i1 = 11 WHERE i1 = 3;
DROP TRIGGER t1_bu;
DROP PROCEDURE p2;
DROP PROCEDURE p1;
INSERT INTO t1 VALUES (13);
CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 17;
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
CALL p1(OLD.i1);
UPDATE t1 SET i1 = 19 WHERE i1 = 13;
ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger
DROP TRIGGER t1_bu;
DROP PROCEDURE p1;
INSERT INTO t1 VALUES (23);
CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 29;
CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 FOR EACH ROW
CALL p1(OLD.i1);
UPDATE t1 SET i1 = 31 WHERE i1 = 23;
ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger
DROP TRIGGER t1_bu;
DROP PROCEDURE p1;
INSERT INTO t1 VALUES (37);
CREATE PROCEDURE p1(OUT i1 INT) DETERMINISTIC NO SQL SET @a = 41;
CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
CALL p1(NEW.i1);
UPDATE t1 SET i1 = 43 WHERE i1 = 37;
ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger
DROP TRIGGER t1_au;
DROP PROCEDURE p1;
INSERT INTO t1 VALUES (47);
CREATE PROCEDURE p1(INOUT i1 INT) DETERMINISTIC NO SQL SET @a = i1 * 49;
CREATE TRIGGER t1_au AFTER UPDATE ON t1 FOR EACH ROW
CALL p1(NEW.i1);
UPDATE t1 SET i1 = 51 WHERE i1 = 47;
ERROR 42000: OUT or INOUT argument 1 for routine test.p1 is not a variable or NEW pseudo-variable in BEFORE trigger
DROP TRIGGER t1_au;
DROP PROCEDURE p1;
SELECT * FROM t1;
i1
35
13
23
43
51
DROP TABLE t1;
create trigger wont_work after update on mysql.user for each row
begin
set @a:= 1;
end|
ERROR HY000: Triggers can not be created on system tables
use mysql|
create trigger wont_work after update on event for each row
begin
set @a:= 1;
end|
ERROR HY000: Triggers can not be created on system tables
use test|
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1(c INT);
CREATE TABLE t2(c INT);
CREATE DEFINER=1234567890abcdefGHIKL@localhost
TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1;
ERROR HY000: String '1234567890abcdefGHIKL' is too long for user name (should be no longer than 16)
CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY
TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW SET @a = 2;
ERROR HY000: String '1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY' is too long for host name (should be no longer than 60)
DROP TABLE t1;
DROP TABLE t2;
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
SET @save_sql_mode=@@sql_mode;
SET sql_mode='TRADITIONAL'|
create table t1 (id int(10) not null primary key, v int(10) )|
create table t2 (id int(10) not null primary key, v int(10) )|
create table t3 (id int(10) not null primary key, v int(10) )|
create table t4 (c int)|
create trigger t4_bi before insert on t4 for each row set @t4_bi_called:=1|
create trigger t4_bu before update on t4 for each row set @t4_bu_called:=1|
insert into t1 values(10, 10)|
set @a:=1/0|
Warnings:
Error	1365	Division by 0
select 1/0 from t1|
1/0
NULL
Warnings:
Error	1365	Division by 0
create trigger t1_bi before insert on t1 for each row set @a:=1/0|
insert into t1 values(20, 20)|
Warnings:
Error	1365	Division by 0
drop trigger t1_bi|
create trigger t1_bi before insert on t1 for each row
begin
insert into t2 values (new.id, new.v);
update t2 set v=v+1 where id= new.id;
replace t3 values (new.id, 0);
update t2, t3 set t2.v=new.v, t3.v=new.v where t2.id=t3.id;
create temporary table t5 select * from t1;
delete from t5;
insert into t5 select * from t1;
insert into t4 values (0);
set @check= (select count(*) from t5);
update t4 set c= @check;
drop temporary table t5;
set @a:=1/0;
end|
set @check=0, @t4_bi_called=0, @t4_bu_called=0|
insert into t1 values(30, 30)|
Warnings:
Error	1365	Division by 0
select @check, @t4_bi_called, @t4_bu_called|
@check	@t4_bi_called	@t4_bu_called
2	1	1
SET @@sql_mode=@save_sql_mode;
drop table t1;
drop table t2;
drop table t3;
drop table t4;
drop table if exists t1;
create table t1 (i int, j int key);
insert into t1 values (1,1), (2,2), (3,3);
create trigger t1_bu before update on t1 for each row
set new.j = new.j + 10;
update t1 set i= i+ 10 where j > 2;
select * from t1;
i	j
1	1
2	2
13	13
drop table t1;
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE TABLE t2 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
CREATE TRIGGER trg_t1 BEFORE DELETE on t1 FOR EACH ROW 
INSERT INTO t2 VALUES (OLD.a);
FLUSH STATUS;
TRUNCATE t1;
SHOW STATUS LIKE 'handler_delete';
Variable_name	Value
Handler_delete	0
SELECT COUNT(*) FROM t2;
COUNT(*)
0
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8);
DELETE FROM t2;
FLUSH STATUS;
DELETE FROM t1;
SHOW STATUS LIKE 'handler_delete';
Variable_name	Value
Handler_delete	8
SELECT COUNT(*) FROM t2;
COUNT(*)
8
DROP TRIGGER trg_t1;
DROP TABLE t1,t2;
drop table if exists t1;
drop function if exists f1;
create table t1 (i int);
create function f1() returns int return 10;
create trigger t1_bi before insert on t1 for each row set @a:= f1() + 10;
insert into t1 values ();
select @a;
@a
20
insert into t1 values ();
select @a;
@a
20
drop table t1;
drop function f1;
drop table if exists t1;
create table t1(a int, b varchar(50));
drop trigger not_a_trigger;
ERROR HY000: Trigger does not exist
drop trigger if exists not_a_trigger;
Warnings:
Note	1360	Trigger does not exist
create trigger t1_bi before insert on t1
for each row set NEW.b := "In trigger t1_bi";
insert into t1 values (1, "a");
drop trigger if exists t1_bi;
insert into t1 values (2, "b");
drop trigger if exists t1_bi;
Warnings:
Note	1360	Trigger does not exist
insert into t1 values (3, "c");
select * from t1;
a	b
1	In trigger t1_bi
2	b
3	c
drop table t1;
CREATE TABLE t1 (
id int NOT NULL DEFAULT '0',
a  varchar(10) NOT NULL,
b  varchar(10),
c  varchar(10),
d  timestamp NOT NULL,
PRIMARY KEY (id, a)
);
CREATE TABLE t2 (
fubar_id         int unsigned NOT NULL DEFAULT '0',
last_change_time datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY  (fubar_id)
);
CREATE TRIGGER fubar_change
AFTER UPDATE ON t1
FOR EACH ROW
BEGIN
INSERT INTO t2 (fubar_id, last_change_time)
SELECT DISTINCT NEW.id AS fubar_id, NOW() AS last_change_time
FROM t1 WHERE (id = NEW.id) AND (OLD.c != NEW.c)
ON DUPLICATE KEY UPDATE
last_change_time =
IF((fubar_id = NEW.id)AND(OLD.c != NEW.c),NOW(),last_change_time);
END
|
INSERT INTO t1 (id,a, b,c,d) VALUES
(1,'a','b','c',now()),(2,'a','b','c',now());
UPDATE t1 SET c='Bang!' WHERE id=1;
SELECT fubar_id FROM t2;
fubar_id
1
DROP TABLE t1,t2;
DROP TABLE IF EXISTS bug21825_A;
DROP TABLE IF EXISTS bug21825_B;
CREATE TABLE bug21825_A (id int(10));
CREATE TABLE bug21825_B (id int(10));
CREATE TRIGGER trgA AFTER INSERT ON bug21825_A
FOR EACH ROW
BEGIN
INSERT INTO bug21825_B (id) values (1);
END//
INSERT INTO bug21825_A (id) VALUES (10);
INSERT INTO bug21825_A (id) VALUES (20);
DROP TABLE bug21825_B;
DELETE FROM bug21825_A WHERE id = 20;
DROP TABLE bug21825_A;
DROP TABLE IF EXISTS bug22580_t1;
DROP PROCEDURE IF EXISTS bug22580_proc_1;
DROP PROCEDURE IF EXISTS bug22580_proc_2;
CREATE TABLE bug22580_t1 (a INT, b INT);
CREATE PROCEDURE bug22580_proc_2()
BEGIN
DROP TABLE IF EXISTS bug22580_tmp;
CREATE TEMPORARY TABLE bug22580_tmp (a INT);
DROP TABLE bug22580_tmp;
END||
CREATE PROCEDURE bug22580_proc_1()
BEGIN
CALL bug22580_proc_2();
END||
CREATE TRIGGER t1bu BEFORE UPDATE ON bug22580_t1
FOR EACH ROW 
BEGIN
CALL bug22580_proc_1();
END||
INSERT INTO bug22580_t1 VALUES (1,1);
DROP TABLE bug22580_t1;
DROP PROCEDURE bug22580_proc_1;
DROP PROCEDURE bug22580_proc_2;
DROP TRIGGER IF EXISTS trg27006_a_update;
DROP TRIGGER IF EXISTS trg27006_a_insert;
CREATE TABLE t1 (
`id` int(10) unsigned NOT NULL auto_increment,
`val` varchar(10) NOT NULL,
PRIMARY KEY  (`id`)
);
CREATE TABLE t2 like t1;
CREATE TRIGGER trg27006_a_insert AFTER INSERT ON t1 FOR EACH ROW
BEGIN
insert into t2 values (NULL,new.val);
END |
CREATE TRIGGER trg27006_a_update AFTER UPDATE ON t1 FOR EACH ROW
BEGIN
insert into t2 values (NULL,new.val);
END |
INSERT INTO t1(val) VALUES ('test1'),('test2');
SELECT * FROM t1;
id	val
1	test1
2	test2
SELECT * FROM t2;
id	val
1	test1
2	test2
INSERT INTO t1 VALUES (2,'test2') ON DUPLICATE KEY UPDATE val=VALUES(val);
INSERT INTO t1 VALUES (2,'test3') ON DUPLICATE KEY UPDATE val=VALUES(val);
INSERT INTO t1 VALUES (3,'test4') ON DUPLICATE KEY UPDATE val=VALUES(val);
SELECT * FROM t1;
id	val
1	test1
2	test3
3	test4
SELECT * FROM t2;
id	val
1	test1
2	test2
3	test3
4	test4
DROP TRIGGER trg27006_a_insert;
DROP TRIGGER trg27006_a_update;
drop table t1,t2;
drop table if exists t1, t2, t3;
create table t1 (i int);
create trigger t1_bi before insert on t1 for each row set new.i = 7;
create trigger t1_ai after insert on t1 for each row set @a := 7;
create table t2 (j int);
insert into t2 values (1), (2);
set @a:="";
create table if not exists t1 select * from t2;
Warnings:
Note	1050	Table 't1' already exists
select * from t1;
i
7
7
select @a;
@a
7
drop trigger t1_bi;
drop trigger t1_ai;
create table t3 (isave int);
create trigger t1_bi before insert on t1 for each row insert into t3 values (new.i);
create table if not exists t1 select * from t2;
Warnings:
Note	1050	Table 't1' already exists
select * from t1;
i
7
7
1
2
select * from t3;
isave
1
2
drop table t1, t2, t3;
CREATE TABLE t1 (id INTEGER);
CREATE TABLE t2 (id INTEGER);
INSERT INTO t2 VALUES (1),(2);
CREATE TRIGGER t1_test AFTER INSERT ON t1 FOR EACH ROW 
INSERT INTO t2 VALUES (new.id);
SELECT GET_LOCK('B26162',120);
GET_LOCK('B26162',120)
1
SELECT 'rl_acquirer', GET_LOCK('B26162',120), id FROM t2 WHERE id = 1;
SET SESSION LOW_PRIORITY_UPDATES=1;
SET GLOBAL LOW_PRIORITY_UPDATES=1;
INSERT INTO t1 VALUES (5);
SELECT 'rl_contender', id FROM t2 WHERE id > 1;
SELECT RELEASE_LOCK('B26162');
RELEASE_LOCK('B26162')
1
rl_acquirer	GET_LOCK('B26162',120)	id
rl_acquirer	1	1
SELECT RELEASE_LOCK('B26162');
RELEASE_LOCK('B26162')
1
rl_contender	id
rl_contender	2
DROP TRIGGER t1_test;
DROP TABLE t1,t2;