trigger.test

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

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);
--error ER_SP_NOT_VAR_ARG
UPDATE t1 SET i1 = 19 WHERE i1 = 13;
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);
--error ER_SP_NOT_VAR_ARG
UPDATE t1 SET i1 = 31 WHERE i1 = 23;
DROP TRIGGER t1_bu;
DROP PROCEDURE p1;

# Check that NEW.x pseudo variable is read-only in the AFTER TRIGGER.
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);
--error ER_SP_NOT_VAR_ARG
UPDATE t1 SET i1 = 43 WHERE i1 = 37;
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);
--error ER_SP_NOT_VAR_ARG
UPDATE t1 SET i1 = 51 WHERE i1 = 47;
DROP TRIGGER t1_au;
DROP PROCEDURE p1;

# Post requisite.
SELECT * FROM t1;

DROP TABLE t1;

#
# Bug #18005: Creating a trigger on mysql.event leads to server crash on
# scheduler startup
#
# Bug #18361: Triggers on mysql.user table cause server crash
#
# We don't allow triggers on the mysql schema
delimiter |;
--error ER_NO_TRIGGERS_ON_SYSTEM_SCHEMA
create trigger wont_work after update on mysql.user for each row
begin
 set @a:= 1;
end|
# Try when we're already using the mysql schema
use mysql|
--error ER_NO_TRIGGERS_ON_SYSTEM_SCHEMA
create trigger wont_work after update on event for each row
begin
 set @a:= 1;
end|
use test|
delimiter ;|


#
# Test for BUG#16899: Possible buffer overflow in handling of DEFINER-clause.
#

# Prepare.

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
--enable_warnings

CREATE TABLE t1(c INT);
CREATE TABLE t2(c INT);

--error ER_WRONG_STRING_LENGTH
CREATE DEFINER=1234567890abcdefGHIKL@localhost
  TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET @a = 1;

--error ER_WRONG_STRING_LENGTH
CREATE DEFINER=some_user_name@1234567890abcdefghij1234567890abcdefghij1234567890abcdefghijQWERTY
  TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW SET @a = 2;

# Cleanup.

DROP TABLE t1;
DROP TABLE t2;

#
# Bug#20028 Function with select return no data
# 

--disable_warnings
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
--enable_warnings

SET @save_sql_mode=@@sql_mode;

delimiter |;
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|
select 1/0 from t1|

create trigger t1_bi before insert on t1 for each row set @a:=1/0|

insert into t1 values(20, 20)|

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)|
select @check, @t4_bi_called, @t4_bu_called|

delimiter ;|

SET @@sql_mode=@save_sql_mode;

drop table t1;
drop table t2;
drop table t3;
drop table t4;

#
# Bug#20670 "UPDATE using key and invoking trigger that modifies
#            this key does not stop"
#

--disable_warnings
drop table if exists t1;
--enable_warnings
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;
# This should not work indefinitely and should cause
# expected result
update t1 set i= i+ 10 where j > 2;
select * from t1;
drop table t1;

#
# Bug#23556 TRUNCATE TABLE still maps to DELETE
#
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';
SELECT COUNT(*) FROM t2;

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';
SELECT COUNT(*) FROM t2;

DROP TRIGGER trg_t1;
DROP TABLE t1,t2;

#
# Bug #23651 "Server crashes when trigger which uses stored function
#             invoked from different connections".
#
--disable_warnings
drop table if exists t1;
drop function if exists f1;
--enable_warnings
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;
connection addconroot1;
insert into t1 values ();
select @a;
connection default;
drop table t1;
drop function f1;

#
# Bug#23703: DROP TRIGGER needs an IF EXISTS
#

--disable_warnings
drop table if exists t1;
--enable_warnings

create table t1(a int, b varchar(50));

-- error ER_TRG_DOES_NOT_EXIST
drop trigger not_a_trigger;

drop trigger if exists not_a_trigger;

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;
insert into t1 values (3, "c");

select * from t1;

drop table t1;

#
# Bug#25398: crash when a trigger contains a SELECT with 
#            trigger fields in the select list under DISTINCT
#

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)
);

DELIMITER |;

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
|

DELIMITER ;|

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;

DROP TABLE t1,t2;

#
# Bug#21285 (Incorrect message error deleting records in a table with a
#           trigger for inserting)
#

--disable_warnings
DROP TABLE IF EXISTS bug21825_A;
DROP TABLE IF EXISTS bug21825_B;
--enable_warnings

CREATE TABLE bug21825_A (id int(10));
CREATE TABLE bug21825_B (id int(10));

delimiter //;

CREATE TRIGGER trgA AFTER INSERT ON bug21825_A
FOR EACH ROW
BEGIN
  INSERT INTO bug21825_B (id) values (1);
END//
delimiter ;//

INSERT INTO bug21825_A (id) VALUES (10);
INSERT INTO bug21825_A (id) VALUES (20);

DROP TABLE bug21825_B;

# Must pass, the missing table in the insert trigger should not matter.
DELETE FROM bug21825_A WHERE id = 20;

DROP TABLE bug21825_A;

#
# Bug#22580 (DROP TABLE in nested stored procedure causes strange dependancy
# error)
#

--disable_warnings
DROP TABLE IF EXISTS bug22580_t1;
DROP PROCEDURE IF EXISTS bug22580_proc_1;
DROP PROCEDURE IF EXISTS bug22580_proc_2;
--enable_warnings

CREATE TABLE bug22580_t1 (a INT, b INT);

DELIMITER ||;

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||

DELIMITER ;||

# Must pass, the actions of the update trigger should not matter
INSERT INTO bug22580_t1 VALUES (1,1);

DROP TABLE bug22580_t1;
DROP PROCEDURE bug22580_proc_1;
DROP PROCEDURE bug22580_proc_2;

#
# Bug#27006: AFTER UPDATE triggers not fired with INSERT ... ON DUPLICATE
#
--disable_warnings
DROP TRIGGER IF EXISTS trg27006_a_update;
DROP TRIGGER IF EXISTS trg27006_a_insert;
--enable_warnings

CREATE TABLE t1 (
  `id` int(10) unsigned NOT NULL auto_increment,
  `val` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`)
);
CREATE TABLE t2 like t1;
DELIMITER |;

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 |
DELIMITER ;|

INSERT INTO t1(val) VALUES ('test1'),('test2');
SELECT * FROM t1;
SELECT * FROM t2;
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;
SELECT * FROM t2;
DROP TRIGGER trg27006_a_insert;
DROP TRIGGER trg27006_a_update;
drop table t1,t2;

#
# Bug #20903 "Crash when using CREATE TABLE .. SELECT and triggers"
#

--disable_warnings
drop table if exists t1, t2, t3;
--enable_warnings
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;
select * from t1;
select @a;
# Let us check that trigger that involves table also works ok.
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;
select * from t1;
select * from t3;
drop table t1, t2, t3;

disconnect addconroot1;
disconnect addconroot2;
disconnect addconwithoutdb;
#
# Bug #26162: Trigger DML ignores low_priority_updates setting
#
CREATE TABLE t1 (id INTEGER);
CREATE TABLE t2 (id INTEGER);

INSERT INTO t2 VALUES (1),(2);

# trigger that produces the high priority insert, but should be low, adding
# LOW_PRIORITY fixes this
CREATE TRIGGER t1_test AFTER INSERT ON t1 FOR EACH ROW 
  INSERT INTO t2 VALUES (new.id);