SET @NO_REFRESH = IF( '' = '', 0, 1);
USE test;
set @@global.max_heap_table_size  = 4294967295;
set @@session.max_heap_table_size = 4294967295;
drop table if exists tb3;
create table tb3 (
f118 char not null DEFAULT 'a', 
f119 char binary not null DEFAULT b'101', 
f120 char ascii not null DEFAULT b'101', 
f121 char(50), 
f122 char(50), 
f129 binary not null DEFAULT b'101', 
f130 tinyint not null DEFAULT 99, 
f131 tinyint unsigned not null DEFAULT 99, 
f132 tinyint zerofill not null DEFAULT 99, 
f133 tinyint unsigned zerofill not null DEFAULT 99, 
f134 smallint not null DEFAULT 999, 
f135 smallint unsigned not null DEFAULT 999, 
f136 smallint zerofill not null DEFAULT 999,  
f137 smallint unsigned zerofill not null DEFAULT 999, 
f138 mediumint not null DEFAULT 9999, 
f139 mediumint unsigned not null DEFAULT 9999, 
f140 mediumint zerofill not null DEFAULT 9999, 
f141 mediumint unsigned zerofill not null DEFAULT 9999, 
f142 int not null DEFAULT 99999, 
f143 int unsigned not null DEFAULT 99999, 
f144 int zerofill not null DEFAULT 99999, 
f145 int unsigned zerofill not null DEFAULT 99999, 
f146 bigint not null DEFAULT 999999, 
f147 bigint unsigned not null DEFAULT 999999, 
f148 bigint zerofill not null DEFAULT 999999, 
f149 bigint unsigned zerofill not null DEFAULT 999999, 
f150 decimal not null DEFAULT 999.999, 
f151 decimal unsigned not null DEFAULT 999.17, 
f152 decimal zerofill not null DEFAULT 999.999, 
f153 decimal unsigned zerofill, 
f154 decimal (0), 
f155 decimal (64), 
f156 decimal (0) unsigned, 
f157 decimal (64) unsigned, 
f158 decimal (0) zerofill, 
f159 decimal (64) zerofill, 
f160 decimal (0) unsigned zerofill, 
f161 decimal (64) unsigned zerofill, 
f162 decimal (0,0), 
f163 decimal (63,30), 
f164 decimal (0,0) unsigned, 
f165 decimal (63,30) unsigned, 
f166 decimal (0,0) zerofill, 
f167 decimal (63,30) zerofill, 
f168 decimal (0,0) unsigned zerofill, 
f169 decimal (63,30) unsigned zerofill, 
f170 numeric, 
f171 numeric unsigned, 
f172 numeric zerofill, 
f173 numeric unsigned zerofill, 
f174 numeric (0), 
f175 numeric (64) 
) engine = memory;
Warnings:
Note	1265	Data truncated for column 'f150' at row 1
Note	1265	Data truncated for column 'f151' at row 1
Note	1265	Data truncated for column 'f152' at row 1
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/memory_tb3.txt' into table tb3 ;

Testcase: 3.5:
--------------
create User test_general@localhost;
set password for test_general@localhost = password('PWD');
revoke ALL PRIVILEGES, GRANT OPTION FROM test_general@localhost;
create User test_super@localhost;
set password for test_super@localhost = password('PWD');
grant ALL on *.* to test_super@localhost with grant OPTION;
connect(localhost,test_general,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_super,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);

Testcase: 3.5.1.1:
------------------
use test;
Create trigger trg1_1 BEFORE INSERT 
on tb3 for each row set @test_before = 2, new.f142 = @test_before;
Create trigger trg1_2 AFTER INSERT 
on tb3 for each row set @test_after = 6;
Create trigger trg1_4 BEFORE UPDATE 
on tb3 for each row set @test_before = 27, 
new.f142 = @test_before, 
new.f122 = 'Before Update Trigger';
Create trigger trg1_3 AFTER UPDATE 
on tb3 for each row set @test_after = '15';
Create trigger trg1_5 BEFORE DELETE on tb3 for each row  
select count(*) into @test_before from tb3 as tr_tb3 
where f121 = 'Test 3.5.1.1';
Create trigger trg1_6 AFTER DELETE on tb3 for each row  
select count(*) into @test_after from tb3 as tr_tb3 
where f121 = 'Test 3.5.1.1';
set @test_before = 1;
set @test_after = 5;
select @test_before, @test_after;
@test_before	@test_after
1	5
Insert into tb3 (f121, f122, f142, f144, f134) 
values ('Test 3.5.1.1', 'First Row', @test_before, @test_after, 1);
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
f121	f122	f142	f144	f134
Test 3.5.1.1	First Row	2	0000000005	1
select @test_before, @test_after;
@test_before	@test_after
2	6
set @test_before = 18;
set @test_after = 8;
select @test_before, @test_after;
@test_before	@test_after
18	8
Update tb3 set  tb3.f122 = 'Update', 
tb3.f142 = @test_before, 
tb3.f144 = @test_after 
where tb3.f121 = 'Test 3.5.1.1';
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
f121	f122	f142	f144	f134
Test 3.5.1.1	Before Update Trigger	27	0000000008	1
select @test_before, @test_after;
@test_before	@test_after
27	15
Insert into tb3 (f121, f122, f142, f144, f134) 
values ('Test 3.5.1.1', 'Second Row', 5, 6, 2);
set @test_before = 0;
set @test_after = 0;
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
f121	f122	f142	f144	f134
Test 3.5.1.1	Before Update Trigger	27	0000000008	1
Test 3.5.1.1	Second Row	2	0000000006	2
select @test_before, @test_after;
@test_before	@test_after
0	0
Delete from tb3 where f121 = 'Test 3.5.1.1' and f134 = 2;
select f121, f122, f142, f144, f134 from tb3 where f121 = 'Test 3.5.1.1';
f121	f122	f142	f144	f134
Test 3.5.1.1	Before Update Trigger	27	0000000008	1
select @test_before, @test_after;
@test_before	@test_after
2	1
drop trigger trg1_1;
drop trigger trg1_2;
drop trigger trg1_3;
drop trigger trg1_4;
drop trigger trg1_5;
drop trigger trg1_6;
delete from tb3 where f121='Test 3.5.1.1';

Testcase: 3.5.1.2:
------------------
Create trigger trg_1 after insert 
on tb3 for each statement set @x= 1;
ERROR 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 'statement set @x= 1' at line 2
drop trigger trg_1;

Testcase 3.5.1.3:
-----------------
CREATE TRIGGER trg3_1 on tb3 BEFORE INSERT for each row set new.f120 = 't';
ERROR 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 'on tb3 BEFORE INSERT for each row set new.f120 = 't'' at line 1
CREATE trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's';
ERROR 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 'trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's'' at line 1
CREATE TRIGGER trg3_3 Before DELETE on tb3 set @ret1 = 'test' for each row;
ERROR 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 'set @ret1 = 'test' for each row' at line 1
CREATE TRIGGER trg3_4 DELETE AFTER on tb3 set @ret1 = 'test' for each row;
ERROR 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 'DELETE AFTER on tb3 set @ret1 = 'test' for each row' at line 1
CREATE for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test';
ERROR 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 'for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test'' at line 1
drop trigger trg3_1;
drop trigger trg3_2;
drop trigger trg3_3;
drop trigger trg3_4;
drop trigger trg3_5;

Testcase: 3.5.1.5:
------------------
CREATE TRIGGER trg4_1 AFTER on tb3 for each row set new.f120 = 'e';
ERROR 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 'on tb3 for each row set new.f120 = 'e'' at line 1
CREATE TRIGGER trg4_2 INSERT on tb3 for each set row  new.f120 = 'f';
ERROR 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 'INSERT on tb3 for each set row  new.f120 = 'f'' at line 1
CREATE TRIGGER trg4_3 BEFORE INSERT tb3 for each row set new.f120 = 'g';
ERROR 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 'tb3 for each row set new.f120 = 'g'' at line 1
CREATE TRIGGER trg4_4 AFTER UPDATE on tb3 for each set new.f120 = 'g';
ERROR 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 'set new.f120 = 'g'' at line 1
CREATE trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g';
ERROR 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 'trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g'' at line 1
CREATE TRIGGER trg4_6 BEFORE DELETE for each row set new.f120 = 'g';
ERROR 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 'for each row set new.f120 = 'g'' at line 1
drop trigger trg4_1;
drop trigger trg4_2;
drop trigger trg4_3;
drop trigger trg4_4;
drop trigger trg4_5;
drop trigger trg4_6;

Testcase 3.5.1.6: - Need to fix
-------------------------------

Testcase 3.5.1.7: - need to fix
-------------------------------
drop table if exists t1;
Warnings:
Note	1051	Unknown table 't1'
create table t1 (f1 int, f2 char(25),f3 int) engine=memory;
CREATE TRIGGER trg5_1 BEFORE INSERT on test.t1 
for each row set new.f3 = '14';
CREATE TRIGGER trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ
BEFORE UPDATE on test.t1 for each row set new.f3 = '42';
insert into t1 (f2) values ('insert 3.5.1.7');
select * from t1;
f1	f2	f3
NULL	insert 3.5.1.7	14
update t1 set f2='update 3.5.1.7';
select * from t1;
f1	f2	f3
NULL	update 3.5.1.7	42
select trigger_name from information_schema.triggers;
trigger_name
trg5_1
trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWX
drop trigger trg5_1;
drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ;
drop table t1;

Testcase 3.5.1.8:
-----------------
CREATE TRIGGER trg12* before insert on tb3 for each row set new.f120 = 't';
ERROR 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 '* before insert on tb3 for each row set new.f120 = 't'' at line 1
CREATE TRIGGER trigger before insert on tb3 for each row set new.f120 = 't';
ERROR 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 'trigger before insert on tb3 for each row set new.f120 = 't'' at line 1
CREATE TRIGGER 100 before insert on tb3 for each row set new.f120 = 't';
ERROR 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 '100 before insert on tb3 for each row set new.f120 = 't'' at line 1
CREATE TRIGGER @@view before insert on tb3 for each row set new.f120 = 't';
ERROR 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 '@@view before insert on tb3 for each row set new.f120 = 't'' at line 1
CREATE TRIGGER @name before insert on tb3 for each row set new.f120 = 't';
ERROR 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 '@name before insert on tb3 for each row set new.f120 = 't'' at line 1
CREATE TRIGGER tb3.trg6_1 BEFORE INSERT on test.tb3 
for each row set new.f120 ='X';
ERROR HY000: Trigger in wrong schema
drop database if exists trig_db;
create database trig_db;
use trig_db;
create table t1 (f1 integer) engine = memory;
use test;
CREATE TRIGGER trig_db.trg6_2 AFTER INSERT on tb3 
for each row set @ret_trg6_2 = 5;
ERROR HY000: Trigger in wrong schema
use trig_db;
CREATE TRIGGER trg6_3 AFTER INSERT on test.tb3 
for each row set @ret_trg6_3 = 18;
ERROR HY000: Trigger in wrong schema
use test;
drop database trig_db;
drop trigger trg6_1;
drop trigger trg6_3;

Testcase 3.5.1.9:(cannot be inplemented at this point)
------------------------------------------------------

Testcase 3.5.1.10:
------------------
CREATE TRIGGER trg7_1 BEFORE UPDATE on tb3 for each row set new.f120 ='X';
CREATE TRIGGER trg7_1 AFTER INSERT on tb3 for each row set @x ='Y';
ERROR HY000: Trigger already exists
drop trigger trg7_1;

Testcase 3.5.1.?:
-----------------
drop table if exists t1;
drop table if exists t2;
create table t1 (f1 char(50), f2 integer) engine = memory;
create table t2 (f1 char(50), f2 integer) engine = memory;
create trigger trig before insert on t1 
for each row set new.f1 ='trig t1';
create trigger trig before update on t2 
for each row set new.f1 ='trig t2';
ERROR HY000: Trigger already exists
insert into t1 value ('insert to t1',1);
select * from t1;
f1	f2
trig t1	1
update t1 set f1='update to t1';
select * from t1;
f1	f2
update to t1	1
insert into t2 value ('insert to t2',2);
update t2 set f1='update to t1';
select * from t2;
f1	f2
update to t1	2
drop table t1;
drop table t2;
drop trigger trig;

Testcase 3.5.1.11:
------------------
drop database if exists trig_db1;
drop database if exists trig_db2;
drop database if exists trig_db3;
create database trig_db1;
create database trig_db2;
create database trig_db3;
use trig_db1;
create table t1 (f1 char(50), f2 integer) engine = memory;
create trigger trig before insert on t1 
for each row set new.f1 ='trig1', @test_var1='trig1';
use trig_db2;
create table t2 (f1 char(50), f2 integer) engine = memory;
create trigger trig before insert on t2 
for each row set new.f1 ='trig2', @test_var2='trig2';
use trig_db3;
create table t1 (f1 char(50), f2 integer) engine = memory;
create trigger trig before insert on t1 
for each row set new.f1 ='trig3', @test_var3='trig3';
set @test_var1= '', @test_var2= '', @test_var3= '';
use trig_db1;
insert into t1 (f1,f2) values ('insert to db1 t1',1);
insert into trig_db1.t1 (f1,f2) values ('insert to db1 t1 from db1',2);
insert into trig_db2.t2 (f1,f2) values ('insert to db2 t2 from db1',3);
insert into trig_db3.t1 (f1,f2) values ('insert to db3 t1 from db1',4);
select @test_var1, @test_var2, @test_var3;
@test_var1	@test_var2	@test_var3
trig1	trig2	trig3
select * from t1;
f1	f2
trig1	1
trig1	2
select * from trig_db2.t2;
f1	f2
trig2	3
select * from trig_db3.t1;
f1	f2
trig3	4
select * from t1;
f1	f2
trig1	1
trig1	2
use test;
drop database trig_db1;
drop database trig_db2;
drop database trig_db3;

Testcase 3.5.2.1/2/3:
---------------------
drop database if exists trig_db1;
drop database if exists trig_db2;
create database trig_db1;
create database trig_db2;
use trig_db1;
create table t1 (f1 char(50), f2 integer) engine = memory;
create table trig_db2.t1 (f1 char(50), f2 integer) engine = memory;
create trigger trig1_b before insert on t1 
for each row set @test_var1='trig1_b';
create trigger trig_db1.trig1_a after insert on t1 
for each row set @test_var2='trig1_a';
create trigger trig_db2.trig2 before insert on trig_db2.t1 
for each row set @test_var3='trig2';
select trigger_schema, trigger_name, event_object_table
from information_schema.triggers;
trigger_schema	trigger_name	event_object_table
trig_db1	trig1_b	t1
trig_db1	trig1_a	t1
trig_db2	trig2	t1
set @test_var1= '', @test_var2= '', @test_var3= '';
insert into t1 (f1,f2) values ('insert to db1 t1 from db1',352);
insert into trig_db2.t1 (f1,f2) values ('insert to db2 t1 from db1',352);
select @test_var1, @test_var2, @test_var3;
@test_var1	@test_var2	@test_var3
trig1_b	trig1_a	trig2
drop database trig_db1;
drop database trig_db2;

Testcase 3.5.3:
---------------
drop database if exists priv_db;
create database priv_db;
use priv_db;
create table t1 (f1 char(20));
create User test_noprivs@localhost;
set password for test_noprivs@localhost = password('PWD');
create User test_yesprivs@localhost;
set password for test_yesprivs@localhost = password('PWD');

Testcase 3.5.3.2/6:
-------------------
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant ALL  on *.* to test_noprivs@localhost;
revoke SUPER  on *.* from test_noprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant SUPER on *.* to test_yesprivs@localhost;
grant SELECT on priv_db.t1 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);

Testcase 3.5.3.2:
-----------------
select current_user;
current_user
test_noprivs@localhost
use priv_db;
create trigger trg1_1 before INSERT on t1 for each row 
set new.f1 = 'trig 3.5.3.2_1-no';
ERROR 42000: Access denied; you need the SUPER privilege for this operation
use priv_db;
insert into t1 (f1) values ('insert 3.5.3.2-no');
select f1 from t1;
f1
insert 3.5.3.2-no
select current_user;
current_user
test_yesprivs@localhost
use priv_db;
create trigger trg1_2 before INSERT  on t1 for each row 
set new.f1 = 'trig 3.5.3.2_2-yes';
use priv_db;
insert into t1 (f1) values ('insert 3.5.3.2-yes');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes

Testcase 3.5.3.6:
-----------------
use priv_db;
drop trigger trg1_2;
ERROR 42000: Access denied; you need the SUPER privilege for this operation
use priv_db;
insert into t1 (f1) values ('insert 3.5.3.6-yes');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
use priv_db;
drop trigger trg1_2;
use priv_db;
insert into t1 (f1) values ('insert 3.5.3.6-no');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
drop trigger trg1_2;

Testcase 3.5.3.7a:
------------------
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant ALL  on *.* to test_noprivs@localhost;
revoke UPDATE  on *.* from test_noprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT SELECT, INSERT, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant SUPER, UPDATE on *.* to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT UPDATE, SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
select current_user;
current_user
test_noprivs@localhost
use priv_db;
show grants;
Grants for test_noprivs@localhost
GRANT SELECT, INSERT, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no

Trigger create disabled - should fail - Bug 8884
------------------------------------------------
insert into t1 (f1) values ('insert 3.5.3.7-1a');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
drop trigger trg4a_1;
use priv_db;
select current_user;
current_user
test_yesprivs@localhost
show grants;
Grants for test_yesprivs@localhost
GRANT UPDATE, SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
create trigger trg4a_2 before INSERT  on t1 for each row 
set new.f1 = 'trig 3.5.3.7-2a';
insert into t1 (f1) values ('insert 3.5.3.7-2b');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
drop trigger trg4a_2;

Testcase 3.5.3.7b:
------------------
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant SUPER on *.* to test_noprivs;
grant ALL  on priv_db.* to test_noprivs@localhost;
revoke UPDATE  on priv_db.* from test_noprivs@localhost;
show grants for test_noprivs;
Grants for test_noprivs@%
GRANT SUPER ON *.* TO 'test_noprivs'@'%'
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant SUPER on *.* to test_yesprivs@localhost;
grant UPDATE on priv_db.* to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
show grants;
Grants for test_noprivs@localhost
GRANT USAGE ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `priv_db`.* TO 'test_noprivs'@'localhost'
use priv_db;

Trigger create disabled - should fail - Bug 8884
------------------------------------------------
insert into t1 (f1) values ('insert 3.5.3.7-1b');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
insert 3.5.3.7-1b
update t1 set  f1 = 'update 3.5.3.7-1b' where f1 = 'insert 3.5.3.7-1b';
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
update 3.5.3.7-1b
drop trigger trg4b_1;
show grants;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT UPDATE ON `priv_db`.* TO 'test_yesprivs'@'localhost'
use priv_db;
create trigger trg4b_2 before UPDATE  on t1 for each row 
set new.f1 = 'trig 3.5.3.7-2b';
insert into t1 (f1) values ('insert 3.5.3.7-2b');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
update 3.5.3.7-1b
insert 3.5.3.7-2b
update t1 set  f1 = 'update 3.5.3.7-2b' where f1 = 'insert 3.5.3.7-2b';
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
update 3.5.3.7-1b
trig 3.5.3.7-2b
drop trigger trg4b_2;

Testcase 3.5.3.7c
-----------------
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant SUPER on *.* to test_noprivs@localhost;
grant ALL  on priv_db.t1 to test_noprivs@localhost;
revoke UPDATE  on priv_db.t1 from test_noprivs@localhost;
show grants for test_noprivs;
Grants for test_noprivs@%
GRANT SUPER ON *.* TO 'test_noprivs'@'%'
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant SUPER on *.* to test_yesprivs@localhost;
grant UPDATE on priv_db.t1 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
show grants;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT, INSERT, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
use priv_db;

Trigger create disabled - should fail - Bug 8884
------------------------------------------------
insert into t1 (f1) values ('insert 3.5.3.7-1c');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
update 3.5.3.7-1b
trig 3.5.3.7-2b
insert 3.5.3.7-1c
drop trigger trg4c_1;
show grants;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT UPDATE ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
use priv_db;
create trigger trg4c_2 before INSERT  on t1 for each row 
set new.f1 = 'trig 3.5.3.7-2c';
insert into t1 (f1) values ('insert 3.5.3.7-2c');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
update 3.5.3.7-1b
trig 3.5.3.7-2b
insert 3.5.3.7-1c
trig 3.5.3.7-2c
drop trigger trg4c_2;

Testcase 3.5.3.7d:
------------------
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant SUPER on *.* to test_noprivs@localhost;
grant SELECT (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
show grants for test_noprivs;
Grants for test_noprivs@%
GRANT SUPER ON *.* TO 'test_noprivs'@'%'
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant SUPER on *.* to test_yesprivs@localhost;
grant UPDATE (f1) on priv_db.t1 to test_yesprivs@localhost;
show grants for test_noprivs;
Grants for test_noprivs@%
GRANT SUPER ON *.* TO 'test_noprivs'@'%'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
show grants;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT (f1), INSERT (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
use priv_db;

Trigger create disabled - should fail - Bug 8884
------------------------------------------------
insert into t1 (f1) values ('insert 3.5.3.7-1d');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
update 3.5.3.7-1b
trig 3.5.3.7-2b
insert 3.5.3.7-1c
trig 3.5.3.7-2c
insert 3.5.3.7-1d
drop trigger trg4d_1;
show grants;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT UPDATE (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
use priv_db;
create trigger trg4d_2 before INSERT  on t1 for each row 
set new.f1 = 'trig 3.5.3.7-2d';
insert into t1 (f1) values ('insert 3.5.3.7-2d');
select f1 from t1;
f1
insert 3.5.3.2-no
trig 3.5.3.2_2-yes
trig 3.5.3.2_2-yes
insert 3.5.3.6-no
insert 3.5.3.7-1a
trig 3.5.3.7-2a
update 3.5.3.7-1b
trig 3.5.3.7-2b
insert 3.5.3.7-1c
trig 3.5.3.7-2c
insert 3.5.3.7-1d
trig 3.5.3.7-2d
drop trigger trg4d_2;

Testcase 3.5.3.8a:
------------------
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant ALL  on *.* to test_noprivs@localhost;
revoke SELECT  on *.* from test_noprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant SUPER, SELECT on *.* to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT SELECT, SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
select current_user;
current_user
test_noprivs@localhost
use priv_db;
show grants;
Grants for test_noprivs@localhost
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'

Trigger create disabled - should fail - Bug 8887
------------------------------------------------
set @test_var = 'before trig 3.5.3.8-1a';
select @test_var;
@test_var
before trig 3.5.3.8-1a
insert into t1 (f1) values ('insert 3.5.3.8-1a');
select @test_var;
@test_var
before trig 3.5.3.8-1a
drop trigger trg5a_1;
use priv_db;
select current_user;
current_user
test_yesprivs@localhost
show grants;
Grants for test_yesprivs@localhost
GRANT SELECT, SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
create trigger trg5a_2 before INSERT  on t1 for each row 
set @test_var= new.f1;
set @test_var= 'before trig 3.5.3.8-2a';
select @test_var;
@test_var
before trig 3.5.3.8-2a
insert into t1 (f1) values ('insert 3.5.3.8-2a');
select @test_var;
@test_var
insert 3.5.3.8-2a
drop trigger trg5a_2;

Testcase: 3.5.3.8b
------------------
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant SUPER on *.* to test_noprivs@localhost;
grant ALL  on priv_db.* to test_noprivs@localhost;
revoke SELECT  on priv_db.* from test_noprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `priv_db`.* TO 'test_noprivs'@'localhost'
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant SUPER on *.* to test_yesprivs@localhost;
grant SELECT on priv_db.* to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
show grants;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE ON `priv_db`.* TO 'test_noprivs'@'localhost'
use priv_db;

Trigger create disabled - should fail - Bug 8887
------------------------------------------------
set @test_var= 'before trig 3.5.3.8-1b';
insert into t1 (f1) values ('insert 3.5.3.8-1b');
select @test_var;
@test_var
before trig 3.5.3.8-1b
update t1 set  f1= 'update 3.5.3.8-1b' where f1 = 'insert 3.5.3.8-1b';
select @test_var;
@test_var
before trig 3.5.3.8-1b
drop trigger trg5b_1;
show grants;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT ON `priv_db`.* TO 'test_yesprivs'@'localhost'
use priv_db;
create trigger trg5b_2 before UPDATE  on t1 for each row 
set @test_var= new.f1;
set @test_var= 'before trig 3.5.3.8-2b';
insert into t1 (f1) values ('insert 3.5.3.8-2b');
select @test_var;
@test_var
before trig 3.5.3.8-2b
update t1 set  f1= 'update 3.5.3.8-2b' where f1 = 'insert 3.5.3.8-2b';
select @test_var;
@test_var
update 3.5.3.8-2b
drop trigger trg5b_2;

Testcase 3.5.3.8c:
------------------
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant SUPER on *.* to test_noprivs@localhost;
grant ALL  on priv_db.t1 to test_noprivs@localhost;
revoke SELECT  on priv_db.t1 from test_noprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant SUPER on *.* to test_yesprivs@localhost;
grant SELECT on priv_db.t1 to test_yesprivs@localhost;
show grants for test_yesprivs@localhost;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
show grants;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
use priv_db;

Trigger create disabled - should fail - Bug 8887
------------------------------------------------
set @test_var= 'before trig 3.5.3.8-1c';
insert into t1 (f1) values ('insert 3.5.3.8-1c');
select @test_var;
@test_var
before trig 3.5.3.8-1c
drop trigger trg5c_1;
show grants;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
use priv_db;
create trigger trg5c_2 before INSERT  on t1 for each row 
set @test_var= new.f1;
set @test_var='before trig 3.5.3.8-2c';
insert into t1 (f1) values ('insert 3.5.3.8-2c');
select @test_var;
@test_var
insert 3.5.3.8-2c
drop trigger trg5c_2;

Testcase: 3.5.3.8d:
-------------------
revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost;
grant SUPER on *.* to test_noprivs@localhost;
grant UPDATE (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
grant SUPER on *.* to test_yesprivs@localhost;
grant SELECT (f1) on priv_db.t1 to test_yesprivs@localhost;
show grants for test_noprivs@localhost;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
connect(localhost,test_noprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
connect(localhost,test_yesprivs,PWD,test,MASTER_MYPORT,MASTER_MYSOCK);
show grants;
Grants for test_noprivs@localhost
GRANT SUPER ON *.* TO 'test_noprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT INSERT (f1), UPDATE (f1) ON `priv_db`.`t1` TO 'test_noprivs'@'localhost'
use priv_db;

Trigger create disabled - should fail - Bug 8887
------------------------------------------------
set @test_var='before trig 3.5.3.8-1d';
insert into t1 (f1) values ('insert 3.5.3.8-1d');
select @test_var;
@test_var
before trig 3.5.3.8-1d
drop trigger trg5d_1;
show grants;
Grants for test_yesprivs@localhost
GRANT SUPER ON *.* TO 'test_yesprivs'@'localhost' IDENTIFIED BY PASSWORD '*C49735D016A099C0CF104EF9183F374A54CA2576'
GRANT SELECT (f1) ON `priv_db`.`t1` TO 'test_yesprivs'@'localhost'
use priv_db;
create trigger trg5d_2 before INSERT  on t1 for each row 
set @test_var= new.f1;
set @test_var='before trig 3.5.3.8-2d';
insert into t1 (f1) values ('insert 3.5.3.8-2d');
select @test_var;
@test_var
insert 3.5.3.8-2d
drop trigger trg5d_2;
drop database if exists priv_db;
drop user test_yesprivs@localhost;
drop user test_noprivs@localhost;
drop user test_noprivs;

Testcase 3.5.4:
---------------
use test;

Testcase 3.5.4.1:
-----------------
create database db_drop;
Use db_drop;
create table t1 (f1 char(30)) engine=memory;
grant INSERT, SELECT on db_drop.t1 to test_general;
Use db_drop;
Create trigger trg1 BEFORE INSERT on t1 
for each row set new.f1='Trigger 3.5.4.1';
Use db_drop;
Insert into t1 values ('Insert error 3.5.4.1');
Select * from t1;
f1
Trigger 3.5.4.1
drop trigger trg1;
select trigger_schema, trigger_name, event_object_table
from information_schema.triggers;
trigger_schema	trigger_name	event_object_table
Insert into t1 values ('Insert no trigger 3.5.4.1');
Select * from t1;
f1
Trigger 3.5.4.1
Insert no trigger 3.5.4.1
drop trigger trg1;
drop database if exists db_drop;
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';

Testcase 3.5.4.2:
-----------------
create database db_drop2;
Use db_drop2;
drop table if exists t1_432 ;
create table t1_432 (f1 char (30)) engine=memory;
Drop trigger tr_does_not_exit;
ERROR HY000: Trigger does not exist
drop table if exists t1_432 ;
drop database  if exists db_drop2;

Testcase 3.5.4.3:
-----------------
create database db_drop3;
Use db_drop3;
drop table if exists t1_433 ;
drop table if exists t1_433a ;
create table t1_433 (f1 char (30)) engine=memory;
create table t1_433a (f1a char (5)) engine=memory;
CREATE TRIGGER trg3 BEFORE INSERT on t1_433 for each row 
set new.f1 = 'Trigger 3.5.4.3';
Drop trigger t1.433.trg3;
ERROR 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 '.trg3' at line 1
Drop trigger db_drop3.t1.433.trg3;
ERROR 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 '.433.trg3' at line 1
Drop trigger mysql.trg3;
ERROR HY000: Trigger does not exist
Drop trigger tbx.trg3;
ERROR HY000: Trigger does not exist
Drop trigger db_drop3.trg3;
drop table if exists t1_433;
drop table if exists t1_433a;
drop database if exists db_drop3;

Testcase 3.5.4.4:
-----------------
create database db_drop4;
Use db_drop4;
create table t1 (f1 char(30)) engine=memory;
grant INSERT, SELECT on db_drop4.t1 to test_general;
Create trigger trg4 BEFORE INSERT on t1 
for each row set new.f1='Trigger 3.5.4.4';
Use db_drop4;
Insert into t1 values ('Insert 3.5.4.4');
Select * from t1;
f1
Trigger 3.5.4.4
Drop database db_drop4;
Show databases;
Database
information_schema
mysql
test
select trigger_schema, trigger_name, event_object_table
from information_schema.triggers
where information_schema.triggers.trigger_name='trg4';
trigger_schema	trigger_name	event_object_table
create database db_drop4;
Use db_drop4;
create table t1 (f1 char(30)) engine=memory;
grant INSERT, SELECT on db_drop4.t1 to test_general;
Insert into t1 values ('2nd Insert 3.5.4.4');
Select * from t1;
f1
2nd Insert 3.5.4.4
drop trigger trg4;
ERROR HY000: Trigger does not exist
drop database if exists db_drop4;
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';

Testcase 3.5.4.5:
-----------------
create database db_drop5;
Use db_drop5;
create table t1 (f1 char(50)) engine=memory;
grant INSERT, SELECT on t1 to test_general;
Create trigger trg5 BEFORE INSERT on t1 
for each row set new.f1='Trigger 3.5.4.5';
Use db_drop5;
Insert into t1 values ('Insert 3.5.4.5');
Select * from t1;
f1
Trigger 3.5.4.5
Drop table t1;
Show tables;
Tables_in_db_drop5
select trigger_schema, trigger_name, event_object_table
from information_schema.triggers
where information_schema.triggers.trigger_name='trg5';
trigger_schema	trigger_name	event_object_table
create table t1 (f1 char(50)) engine=memory;
grant INSERT, SELECT on t1 to test_general;
Insert into t1 values ('2nd Insert 3.5.4.5');
Select * from t1;
f1
2nd Insert 3.5.4.5
drop trigger trg5;
ERROR HY000: Trigger does not exist
drop database if exists db_drop5;
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';

Testcase 3.5.5:
---------------
use test;

Testcase 3.5.5.1:
-----------------
Create trigger trg1 before INSERT on t100 for each row set new.f2=1000;
ERROR 42S02: Table 'test.t100' doesn't exist

Testcase 3.5.5.2:
-----------------
Create temporary table t1_temp (f1 bigint signed, f2 bigint unsigned);
Create trigger trg2 before INSERT 
on t1_temp for each row set new.f2=9999;
ERROR HY000: Trigger's 't1_temp' is view or temporary table
drop table t1_temp;

Testcase 3.5.5.3:
-----------------
Create view vw3 as select f118 from tb3;
Create trigger trg3 before INSERT 
on vw3 for each row set new.f118='s';
ERROR HY000: 'test.vw3' is not BASE TABLE
drop view vw3;

Testcase 3.5.5.4:
-----------------
create database dbtest_one;
create database dbtest_two;
use dbtest_two;
create table t2 (f1 char(15));
use dbtest_one;
create trigger trg4 before INSERT
on dbtest_two.t2 for each row set new.f1='trig 3.5.5.4';
ERROR HY000: Trigger in wrong schema
grant INSERT, SELECT on dbtest_two.t2 to test_general;
grant SELECT on dbtest_one.* to test_general;
use dbtest_two;
Insert into t2 values ('1st Insert 3.5.5.4');
Warnings:
Warning	1265	Data truncated for column 'f1' at row 1
Select * from t2;
f1
1st Insert 3.5.
use dbtest_one;
Insert into dbtest_two.t2 values ('2nd Insert 3.5.5.4');
Warnings:
Warning	1265	Data truncated for column 'f1' at row 1
Select * from dbtest_two.t2;
f1
1st Insert 3.5.
2nd Insert 3.5.
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';
DROP DATABASE if exists dbtest_one;
drop database if EXISTS dbtest_two;

Testcase 3.5.6:
---------------
use test;

Testcase 3.5.6.1 (see Testcase 3.5.1.1)
---------------------------------------

Testcase 3.5.6.2 (see Testcase 3.5.1.1)
---------------------------------------

Testcase 3.5.6.3:
-----------------
Create trigger trg3_1 DURING UPDATE on tb3 for each row set new.f132=25;
ERROR 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 'DURING UPDATE on tb3 for each row set new.f132=25' at line 1
Create trigger trg3_2 TIME INSERT on tb3 for each row set new.f132=15;
ERROR 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 'TIME INSERT on tb3 for each row set new.f132=15' at line 1
drop trigger tb3.trg3_1;
drop trigger tb3.trg3_2;

Testcase 3.5.6.4 (see Testcase 3.5.1.1)
---------------------------------------

Testcase 3.5.6.5 (see Testcase 3.5.1.1)
---------------------------------------

Testcase 3.5.7.1 (see Testcase 3.5.1.1)
---------------------------------------

Testcase 3.5.7.2 (see Testcase 3.5.1.1)
---------------------------------------

Testcase 3.5.7.3 (see Testcase 3.5.1.1)
---------------------------------------

Testcase 3.5.7.4:
-----------------
Create trigger trg4_1 BEFORE SELECT on tb3 for each row set new.f132=5;
ERROR 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 'SELECT on tb3 for each row set new.f132=5' at line 1
Create trigger trg4_2 AFTER VALUE on tb3 for each row set new.f132=1;
ERROR 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 'VALUE on tb3 for each row set new.f132=1' at line 1
drop trigger tb3.trg4_1;
drop trigger tb3.trg4_2;

Testcase 3.5.7.5 / 3.5.7.6:
---------------------------
Create trigger trg5_1 BEFORE INSERT 
on tb3 for each row set new.f122='Trigger1 3.5.7.5/6';
Create trigger trg5_2 BEFORE INSERT 
on tb3 for each row set new.f122='Trigger2 3.5.7.5';
ERROR HY000: Trigger already exists
Insert into tb3 (f121,f122) values ('Test 3.5.7.5/6','Insert 3.5.7.5');
Select f121,f122 from tb3 where f121='Test 3.5.7.5/6';
f121	f122
Test 3.5.7.5/6	Trigger1 3.5.7.5/6
update tb3 set f122='Update 3.5.7.6' where f121= 'Test 3.5.7.5/6';
Select f121,f122 from tb3 where f121='Test 3.5.7.5/6';
f121	f122
Test 3.5.7.5/6	Update 3.5.7.6
drop trigger trg5_1;
drop trigger trg5_2;
delete from tb3 where f121='Test 3.5.7.5/6';

Testcase 3.5.7.7 / 3.5.7.8:
---------------------------
set @test_var='Before trig 3.5.7.7';
Create trigger trg6_1 AFTER INSERT 
on tb3 for each row set @test_var='Trigger1 3.5.7.7/8';
Create trigger trg6_2 AFTER INSERT 
on tb3 for each row set @test_var='Trigger2 3.5.7.7';
ERROR HY000: Trigger already exists
select @test_var;
@test_var
Before trig 3.5.7.7
Insert into tb3 (f121,f122) values ('Test 3.5.7.7/8','Insert 3.5.7.7');
Select f121,f122 from tb3 where f121='Test 3.5.7.7/8';
f121	f122
Test 3.5.7.7/8	Insert 3.5.7.7
select @test_var;
@test_var
Trigger1 3.5.7.7/8
update tb3 set f122='Update 3.5.7.8' where f121= 'Test 3.5.7.7/8';
Select f121,f122 from tb3 where f121='Test 3.5.7.7/8';
f121	f122
Test 3.5.7.7/8	Update 3.5.7.8
select @test_var;
@test_var
Trigger1 3.5.7.7/8
drop trigger trg6_1;
drop trigger trg6_2;
delete from tb3 where f121='Test 3.5.7.7/8';

Testcase 3.5.7.9/10:
--------------------
Create trigger trg7_1 BEFORE UPDATE 
on tb3 for each row set new.f122='Trigger1 3.5.7.9/10';
Create trigger trg7_2 BEFORE UPDATE 
on tb3 for each row set new.f122='Trigger2 3.5.7.9';
ERROR HY000: Trigger already exists
Insert into tb3 (f121,f122) values ('Test 3.5.7.9/10','Insert 3.5.7.9');
Select f121,f122 from tb3 where f121='Test 3.5.7.9/10';
f121	f122
Test 3.5.7.9/10	Insert 3.5.7.9
update tb3 set f122='update 3.5.7.10' where f121='Test 3.5.7.9/10';
Select f121,f122 from tb3 where f121='Test 3.5.7.9/10';
f121	f122
Test 3.5.7.9/10	Trigger1 3.5.7.9/10
drop trigger trg7_1;
drop trigger trg7_2;
delete from tb3 where f121='Test 3.5.7.9/10';

Testcase 3.5.7.11/12:
---------------------
set @test_var='Before trig 3.5.7.11';
Create trigger trg8_1 AFTER UPDATE 
on tb3 for each row set @test_var='Trigger 3.5.7.11/12';
Create trigger trg8_2 AFTER UPDATE 
on tb3 for each row set @test_var='Trigger2 3.5.7.11';
ERROR HY000: Trigger already exists
select @test_var;
@test_var
Before trig 3.5.7.11
Insert into tb3 (f121,f122) values ('Test 3.5.7.11/12','Insert 3.5.7.11/12');
select @test_var;
@test_var
Before trig 3.5.7.11
Select f121,f122 from tb3 where f121='Test 3.5.7.11/12';
f121	f122
Test 3.5.7.11/12	Insert 3.5.7.11/12
update tb3 set f122='update 3.5.7.12' where f121='Test 3.5.7.11/12';
Select f121,f122 from tb3 where f121='Test 3.5.7.11/12';
f121	f122
Test 3.5.7.11/12	update 3.5.7.12
select @test_var;
@test_var
Trigger 3.5.7.11/12
delete from tb3 where f121='Test 3.5.7.11/12';
drop trigger trg8_1;
drop trigger trg8_2;
delete from tb3 where f121='Test 3.5.7.11/12';

Testcase 3.5.7.13/14:
---------------------
set @test_var=1;
Create trigger trg9_1 BEFORE DELETE 
on tb3 for each row set @test_var=@test_var+1;
Create trigger trg9_2 BEFORE DELETE 
on tb3 for each row set @test_var=@test_var+10;
ERROR HY000: Trigger already exists
select @test_var;
@test_var
1
Insert into tb3 (f121,f122) values ('Test 3.5.7.13/14','Insert 3.5.7.13');
Select f121,f122 from tb3 where f121='Test 3.5.7.13/14';
f121	f122
Test 3.5.7.13/14	Insert 3.5.7.13
select @test_var;
@test_var
1
delete from tb3 where f121='Test 3.5.7.13/14';
Select f121,f122 from tb3 where f121='Test 3.5.7.13/14';
f121	f122
select @test_var;
@test_var
2
delete from tb3 where f121='Test 3.5.7.13/14';
select @test_var;
@test_var
2
drop trigger trg9_1;
drop trigger trg9_2;
delete from tb3 where f121='Test 3.5.7.13/14';

Testcase 3.5.7.15/16:
---------------------
set @test_var=1;
Create trigger trg_3_406010_1 AFTER DELETE 
on tb3 for each row set @test_var=@test_var+5;
Create trigger trg_3_406010_2 AFTER DELETE 
on tb3 for each row set @test_var=@test_var+50;
ERROR HY000: Trigger already exists
Create trigger trg_3_406010_1 AFTER INSERT 
on tb3 for each row set @test_var=@test_var+1;
ERROR HY000: Trigger already exists
select @test_var;
@test_var
1
Insert into tb3 (f121,f122) values ('Test 3.5.7.15/16','Insert 3.5.7.15/16');
Select f121,f122 from tb3 where f121='Test 3.5.7.15/16';
f121	f122
Test 3.5.7.15/16	Insert 3.5.7.15/16
select @test_var;
@test_var
1
delete from tb3 where f121='Test 3.5.7.15/16';
Select f121,f122 from tb3 where f121='Test 3.5.7.15/16';
f121	f122
select @test_var;
@test_var
6
delete from tb3 where f121='Test 3.5.7.15/16';
select @test_var;
@test_var
6
drop trigger trg_3_406010_1;
drop trigger trg_3_406010_2;
delete from tb3 where f121='Test 3.5.7.15/16';

Testcase 3.5.7.17 (see Testcase 3.5.1.1)
----------------------------------------

Testcase 3.5.8.1: (implied in previous tests)
---------------------------------------------

Testcase 3.5.8.2: (implied in previous tests)
---------------------------------------------

Testcase 3.5.8.3/4:
-------------------
create database db_test;
grant SELECT, INSERT, UPDATE, DELETE on db_test.* to test_general;
grant LOCK TABLES on db_test.* to test_general;
Use db_test;
create table t1_i ( 
i120 char ascii not null DEFAULT b'101',
i136 smallint zerofill not null DEFAULT 999,
i144 int zerofill not null DEFAULT 99999,
i163 decimal (63,30)) engine=memory;
create table t1_u ( 
u120 char ascii not null DEFAULT b'101',
u136 smallint zerofill not null DEFAULT 999,
u144 int zerofill not null DEFAULT 99999,
u163 decimal (63,30)) engine=memory;
create table t1_d ( 
d120 char ascii not null DEFAULT b'101',
d136 smallint zerofill not null DEFAULT 999,
d144 int zerofill not null DEFAULT 99999,
d163 decimal (63,30)) engine=memory;
Insert into t1_u values ('a',111,99999,999.99);
Insert into t1_u values ('b',222,99999,999.99);
Insert into t1_u values ('c',333,99999,999.99);
Insert into t1_u values ('d',222,99999,999.99);
Insert into t1_u values ('e',222,99999,999.99);
Insert into t1_u values ('f',333,99999,999.99);
Insert into t1_d values ('a',111,99999,999.99);
Insert into t1_d values ('b',222,99999,999.99);
Insert into t1_d values ('c',333,99999,999.99);
Insert into t1_d values ('d',444,99999,999.99);
Insert into t1_d values ('e',222,99999,999.99);
Insert into t1_d values ('f',222,99999,999.99);

3.5.8.4 - multiple SQL
----------------------
use test;
Create trigger trg1 AFTER INSERT on tb3 for each row
BEGIN
insert into db_test.t1_i 
values (new.f120, new.f136, new.f144, new.f163);
update db_test.t1_u 
set u144=new.f144, u163=new.f163
where u136=new.f136; 
delete from db_test.t1_d where d136= new.f136;
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u 
where u136= new.f136; 
END//
Use test;
set @test_var=0;
Insert into tb3 (f120, f122, f136, f144, f163) 
values ('1', 'Test 3.5.8.4', 222, 23456, 1.05);
Select f120, f122, f136, f144, f163 from tb3 where f122= 'Test 3.5.8.4';
f120	f122	f136	f144	f163
1	Test 3.5.8.4	00222	0000023456	1.050000000000000000000000000000
select * from db_test.t1_i;
i120	i136	i144	i163
1	00222	0000023456	1.050000000000000000000000000000
select * from db_test.t1_u;
u120	u136	u144	u163
a	00111	0000099999	999.990000000000000000000000000000
b	00222	0000023456	1.050000000000000000000000000000
c	00333	0000099999	999.990000000000000000000000000000
d	00222	0000023456	1.050000000000000000000000000000
e	00222	0000023456	1.050000000000000000000000000000
f	00333	0000099999	999.990000000000000000000000000000
select * from db_test.t1_d;
d120	d136	d144	d163
a	00111	0000099999	999.990000000000000000000000000000
c	00333	0000099999	999.990000000000000000000000000000
d	00444	0000099999	999.990000000000000000000000000000
select @test_var;
@test_var
3.150000000000000000000000000000

3.5.8.4 - single SQL - insert
-----------------------------
Create trigger trg2 BEFORE UPDATE on tb3 for each row
insert into db_test.t1_i 
values (new.f120, new.f136, new.f144, new.f163);
update tb3 set f120='I', f122='Test 3.5.8.4-Single Insert'
		 where f122='Test 3.5.8.4';
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
f120	f122	f136	f144	f163
I	Test 3.5.8.4-Single Insert	00222	0000023456	1.050000000000000000000000000000
select * from db_test.t1_i;
i120	i136	i144	i163
1	00222	0000023456	1.050000000000000000000000000000
I	00222	0000023456	1.050000000000000000000000000000

3.5.8.4 - single SQL - update
-----------------------------
drop trigger trg2;
Create trigger trg3 BEFORE UPDATE on tb3 for each row
update db_test.t1_u 
set u120=new.f120
where u136=new.f136;
update tb3 set f120='U', f122='Test 3.5.8.4-Single Update'
		 where f122='Test 3.5.8.4-Single Insert';
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
f120	f122	f136	f144	f163
U	Test 3.5.8.4-Single Update	00222	0000023456	1.050000000000000000000000000000
select * from db_test.t1_u;
u120	u136	u144	u163
a	00111	0000099999	999.990000000000000000000000000000
U	00222	0000023456	1.050000000000000000000000000000
c	00333	0000099999	999.990000000000000000000000000000
U	00222	0000023456	1.050000000000000000000000000000
U	00222	0000023456	1.050000000000000000000000000000
f	00333	0000099999	999.990000000000000000000000000000

3.5.8.3/4 - single SQL - delete
-------------------------------
drop trigger trg3;
Create trigger trg4 AFTER UPDATE on tb3 for each row
delete from db_test.t1_d where d136= new.f136;
update tb3 set f120='D', f136=444, 
f122='Test 3.5.8.4-Single Delete'
		 where f122='Test 3.5.8.4-Single Update';
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
f120	f122	f136	f144	f163
D	Test 3.5.8.4-Single Delete	00444	0000023456	1.050000000000000000000000000000
select * from db_test.t1_d;
d120	d136	d144	d163
a	00111	0000099999	999.990000000000000000000000000000
c	00333	0000099999	999.990000000000000000000000000000

3.5.8.3/4 - single SQL - select
-------------------------------
drop trigger trg4;
Create trigger trg5 AFTER UPDATE on tb3 for each row
select sum(db_test.t1_u.u163) into @test_var from db_test.t1_u 
where u136= new.f136;
set @test_var=0;
update tb3 set f120='S', f136=111, 
f122='Test 3.5.8.4-Single Select'
		 where f122='Test 3.5.8.4-Single Delete';
Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%';
f120	f122	f136	f144	f163
S	Test 3.5.8.4-Single Select	00111	0000023456	1.050000000000000000000000000000
select @test_var;
@test_var
999.990000000000000000000000000000
drop trigger trg1;
drop trigger trg5;
drop database if exists db_test;
delete from tb3 where f122 like 'Test 3.5.8.4%';
revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost';

Testcase 3.5.8.5 (IF):
----------------------
create trigger trg2 before insert on tb3 for each row
BEGIN
IF new.f120='1' then
set @test_var='one', new.f120='2';
ELSEIF new.f120='2' then
set @test_var='two', new.f120='3';
ELSEIF new.f120='3' then
set @test_var='three', new.f120='4';
END IF;
IF (new.f120='4') and (new.f136=10) then
set @test_var2='2nd if', new.f120='d';
ELSE 
set @test_var2='2nd else', new.f120='D';
END IF;
END//
set @test_var='Empty', @test_var2=0;
Insert into tb3 (f120, f122, f136) values ('1', 'Test 3.5.8.5-if', 101);
select f120, f122, f136, @test_var, @test_var2 
from tb3 where f122 = 'Test 3.5.8.5-if';
f120	f122	f136	@test_var	@test_var2
D	Test 3.5.8.5-if	00101	one	2nd else
Insert into tb3 (f120, f122, f136) values ('2', 'Test 3.5.8.5-if', 102);
select f120, f122, f136, @test_var, @test_var2 
from tb3 where f122 = 'Test 3.5.8.5-if';
f120	f122	f136	@test_var	@test_var2
D	Test 3.5.8.5-if	00101	two	2nd else
D	Test 3.5.8.5-if	00102	two	2nd else
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 10);
select f120, f122, f136, @test_var, @test_var2 
from tb3 where f122 = 'Test 3.5.8.5-if';
f120	f122	f136	@test_var	@test_var2
D	Test 3.5.8.5-if	00101	three	2nd if
D	Test 3.5.8.5-if	00102	three	2nd if
d	Test 3.5.8.5-if	00010	three	2nd if
Insert into tb3 (f120, f122, f136) values ('3', 'Test 3.5.8.5-if', 103);
select f120, f122, f136, @test_var, @test_var2 
from tb3 where f122 = 'Test 3.5.8.5-if';
f120	f122	f136	@test_var	@test_var2
D	Test 3.5.8.5-if	00101	three	2nd else
D	Test 3.5.8.5-if	00102	three	2nd else
d	Test 3.5.8.5-if	00010	three	2nd else
D	Test 3.5.8.5-if	00103	three	2nd else
create trigger trg3 before update on tb3 for each row
BEGIN
ELSEIF new.f120='2' then
END IF;
END//
ERROR 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 'ELSEIF new.f120='2' then
END IF;
END' at line 3
drop trigger trg3//
create trigger trg4 before update on tb3 for each row
BEGIN
IF (new.f120='4') and (new.f136=10) then
set @test_var2='2nd if', new.f120='d';
ELSE 
set @test_var2='2nd else', new.f120='D';
END//
ERROR 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 '' at line 7
drop trigger trg4;
drop trigger trg2;
delete from tb3 where f121='Test 3.5.8.5-if';

Testcase 3.5.8.5-case:
----------------------
create trigger trg3 before insert on tb3 for each row
BEGIN
SET new.f120=char(ascii(new.f120)-32);
CASE
when new.f136<100 then set new.f136=new.f136+120;
when new.f136<10 then set new.f144=777;
when new.f136>100 then set new.f120=new.f136-1;
END case;
CASE
when new.f136=200 then set @test_var=CONCAT(new.f120, '=');
ELSE set @test_var=concat(new.f120, '*');
END case;
CASE new.f144
when 1 then set @test_var=concat(@test_var, 'one');
when 2 then set @test_var=concat(@test_var, 'two');
when 3 then set @test_var=concat(@test_var, 'three');
when 4 then set @test_var=concat(@test_var, 'four');
when 5 then set @test_var=concat(@test_var, 'five');
when 6 then set @test_var=concat(@test_var, 'six');
when 7 then set @test_var=concat(@test_var, 'seven');
when 8 then set @test_var=concat(@test_var, 'eight');
when 9 then set @test_var=concat(@test_var, 'nine');
when 10 then set @test_var=concat(@test_var, 'ten');
when 11 then set @test_var=concat(@test_var, 'eleven');
when 12 then set @test_var=concat(@test_var, 'twelve');
when 13 then set @test_var=concat(@test_var, 'thirteen');
when 14 then set @test_var=concat(@test_var, 'fourteen');
when 15 then set @test_var=concat(@test_var, 'fifteen');
ELSE set @test_var=CONCAT(new.f120, '*', new.f144);
END case;
END//
set @test_var='Empty';
Insert into tb3 (f120, f122, f136, f144) 
values ('a', 'Test 3.5.8.5-case', 5, 7);
select f120, f122, f136, f144, @test_var 
from tb3 where f122 = 'Test 3.5.8.5-case';
f120	f122	f136	f144	@test_var
A	Test 3.5.8.5-case	00125	0000000007	A*seven
Insert into tb3 (f120, f122, f136, f144) 
values ('b', 'Test 3.5.8.5-case', 71,16);
select f120, f122, f136, f144, @test_var 
from tb3 where f122 = 'Test 3.5.8.5-case';
f120	f122	f136	f144	@test_var
A	Test 3.5.8.5-case	00125	0000000007	B*0000000016
B	Test 3.5.8.5-case	00191	0000000016	B*0000000016
Insert into tb3 (f120, f122, f136, f144) 
values ('c', 'Test 3.5.8.5-case', 80,1);
select f120, f122, f136, f144, @test_var 
from tb3 where f122 = 'Test 3.5.8.5-case';
f120	f122	f136	f144	@test_var
A	Test 3.5.8.5-case	00125	0000000007	C=one
B	Test 3.5.8.5-case	00191	0000000016	C=one
C	Test 3.5.8.5-case	00200	0000000001	C=one
Insert into tb3 (f120, f122, f136) 
values ('d', 'Test 3.5.8.5-case', 152);
Warnings:
Warning	1265	Data truncated for column 'f120' at row 1
select f120, f122, f136, f144, @test_var 
from tb3 where f122 = 'Test 3.5.8.5-case';
f120	f122	f136	f144	@test_var
A	Test 3.5.8.5-case	00125	0000000007	1*0000099999
B	Test 3.5.8.5-case	00191	0000000016	1*0000099999
C	Test 3.5.8.5-case	00200	0000000001	1*0000099999
1	Test 3.5.8.5-case	00152	0000099999	1*0000099999
Insert into tb3 (f120, f122, f136, f144) 
values ('e', 'Test 3.5.8.5-case', 200, 8);
Warnings:
Warning	1265	Data truncated for column 'f120' at row 1
select f120, f122, f136, f144, @test_var 
from tb3 where f122 = 'Test 3.5.8.5-case';
f120	f122	f136	f144	@test_var
A	Test 3.5.8.5-case	00125	0000000007	1=eight
B	Test 3.5.8.5-case	00191	0000000016	1=eight
C	Test 3.5.8.5-case	00200	0000000001	1=eight
1	Test 3.5.8.5-case	00152	0000099999	1=eight
1	Test 3.5.8.5-case	00200	0000000008	1=eight
Insert into tb3 (f120, f122, f136, f144) 
values ('f', 'Test 3.5.8.5-case', 100, 8);
select f120, f122, f136, f144, @test_var 
from tb3 where f122 = 'Test 3.5.8.5-case';
f120	f122	f136	f144	@test_var
A	Test 3.5.8.5-case	00125	0000000007	1=eight
B	Test 3.5.8.5-case	00191	0000000016	1=eight
C	Test 3.5.8.5-case	00200	0000000001	1=eight
1	Test 3.5.8.5-case	00152	0000099999	1=eight
1	Test 3.5.8.5-case	00200	0000000008	1=eight
create trigger trg3a before update on tb3 for each row
BEGIN
CASE
when new.f136<100 then set new.f120='p';
END//
ERROR 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 '' at line 5
drop trigger trg3a;
drop trigger trg3;
delete from tb3 where f121='Test 3.5.8.5-case';

Testcase 3.5.8.5-loop/leave:
----------------------------
Create trigger trg4 after insert on tb3 for each row
BEGIN 
set @counter=0, @flag='Initial';
Label1: loop 
if new.f136<new.f144 then
set @counter='Nothing to loop';
leave Label1; 
else
set @counter=@counter+1;
if new.f136=new.f144+@counter then
set @counter=concat(@counter, ' loops');
leave Label1;
end if; 
end if; 
iterate label1; 
set @flag='Final';
END loop Label1; 
END//
Insert into tb3 (f122, f136, f144) 
values ('Test 3.5.8.5-loop', 2, 8);
select @counter, @flag;
@counter	@flag
Nothing to loop	Initial
Insert into tb3 (f122, f136, f144) 
values ('Test 3.5.8.5-loop', 11, 8);
select @counter, @flag;
@counter	@flag
3 loops	Initial
Create trigger trg4_2 after update on tb3 for each row
BEGIN 
Label1: loop 
set @counter=@counter+1;
END;  
END//
ERROR 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 ';  
END' at line 5
drop trigger trg4_2;
drop trigger trg4;
delete from tb3 where f122='Test 3.5.8.5-loop';

Testcase 3.5.8.5-repeat:
------------------------
Create trigger trg6 after insert on tb3 for each row
BEGIN
rp_label: REPEAT 
SET @counter1 = @counter1 + 1; 
IF (@counter1 MOD 2 = 0) THEN ITERATE rp_label; 	
END IF;
SET @counter2 = @counter2 + 1; 
UNTIL @counter1> new.f136 END REPEAT rp_label;
END//
set @counter1= 0, @counter2= 0;
Insert into tb3 (f122, f136) 
values ('Test 3.5.8.5-repeat', 13);
select @counter1, @counter2;
@counter1	@counter2
15	8
Create trigger trg6_2 after update on tb3 for each row
BEGIN
REPEAT 
SET @counter2 = @counter2 + 1; 
END//
ERROR 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 'END' at line 5
drop trigger trg6;
delete from tb3 where f122='Test 3.5.8.5-repeat';

Testcase 3.5.8.5-while:
-----------------------
Create trigger trg7 after insert on tb3 for each row
wl_label: WHILE @counter1 < new.f136 DO 
SET @counter1 = @counter1 + 1; 
IF (@counter1 MOD 2 = 0) THEN ITERATE wl_label; 	
END IF;
SET @counter2 = @counter2 + 1; 
END WHILE wl_label//
set @counter1= 0, @counter2= 0;
Insert into tb3 (f122, f136) 
values ('Test 3.5.8.5-while', 7);
select @counter1, @counter2;
@counter1	@counter2
7	4
Create trigger trg7_2 after update on tb3 for each row
BEGIN
WHILE @counter1 < new.f136 
SET @counter1 = @counter1 + 1; 
END//
ERROR 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 'SET @counter1 = @counter1 + 1; 
END' at line 4
delete from tb3 where f122='Test 3.5.8.5-while';
drop trigger trg7;

Testcase 3.5.8.6: (requirement void)
------------------------------------

Testcase 3.5.8.7: (Disabled as a result of bug _____)
-----------------------------------------------------

Testcase 3.5.9.1/2:
-------------------
Create trigger trg1 BEFORE UPDATE on tb3 for each row 
set new.f142 = 94087, @counter=@counter+1;
TotalRows
19
Affected
18
NotAffected
1
NewValuew
0
set @counter=0;
Update tb3 Set f142='1' where f130<100;
select count(*) as ExpectedChanged, @counter as TrigCounter 
from tb3 where f142=94087;
ExpectedChanged	TrigCounter
18	18
select count(*) as ExpectedNotChange from tb3 
where f130<100 and f142<>94087;
ExpectedNotChange
0
select count(*) as NonExpectedChanged from tb3 
where f130>=130 and f142=94087;
NonExpectedChanged
0
drop trigger trg1;

Testcase 3.5.9.3:
-----------------
Create trigger trg2_a before update on tb3 for each row
set @tr_var_b4_118=old.f118, @tr_var_b4_121=old.f121,
@tr_var_b4_122=old.f122, @tr_var_b4_136=old.f136,
@tr_var_b4_163=old.f163;
Create trigger trg2_b after update on tb3 for each row
set @tr_var_af_118=old.f118, @tr_var_af_121=old.f121,
@tr_var_af_122=old.f122, @tr_var_af_136=old.f136,
@tr_var_af_163=old.f163;
Create trigger trg2_c before delete on tb3 for each row
set @tr_var_b4_118=old.f118, @tr_var_b4_121=old.f121,
@tr_var_b4_122=old.f122, @tr_var_b4_136=old.f136,
@tr_var_b4_163=old.f163;
Create trigger trg2_d after delete on tb3 for each row
set @tr_var_af_118=old.f118, @tr_var_af_121=old.f121,
@tr_var_af_122=old.f122, @tr_var_af_136=old.f136,
@tr_var_af_163=old.f163;
@tr_var_b4_118	@tr_var_b4_121	@tr_var_b4_122	@tr_var_b4_136	@tr_var_b4_163
0	0	0	0	0
@tr_var_af_118	@tr_var_af_121	@tr_var_af_122	@tr_var_af_136	@tr_var_af_163
0	0	0	0	0
Insert into tb3 (f122, f136, f163) 
values ('Test 3.5.9.3', 7, 123.17);
Update tb3 Set f136=8 where f122='Test 3.5.9.3';
select f118, f121, f122, f136, f163 from tb3 where f122='Test 3.5.9.3';
f118	f121	f122	f136	f163
a	NULL	Test 3.5.9.3	00008	123.170000000000000000000000000000
select  @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, 
@tr_var_b4_136, @tr_var_b4_163;
@tr_var_b4_118	@tr_var_b4_121	@tr_var_b4_122	@tr_var_b4_136	@tr_var_b4_163
a	NULL	Test 3.5.9.3	7	123.170000000000000000000000000000
select  @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, 
@tr_var_af_136, @tr_var_af_163;
@tr_var_af_118	@tr_var_af_121	@tr_var_af_122	@tr_var_af_136	@tr_var_af_163
a	NULL	Test 3.5.9.3	7	123.170000000000000000000000000000
@tr_var_b4_118	@tr_var_b4_121	@tr_var_b4_122	@tr_var_b4_136	@tr_var_b4_163
0	0	0	0	0
@tr_var_af_118	@tr_var_af_121	@tr_var_af_122	@tr_var_af_136	@tr_var_af_163
0	0	0	0	0
delete from tb3 where f122='Test 3.5.9.3';
select f118, f121, f122, f136, f163 from tb3 where f122='Test 3.5.9.3';
f118	f121	f122	f136	f163
select  @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, 
@tr_var_b4_136, @tr_var_b4_163;
@tr_var_b4_118	@tr_var_b4_121	@tr_var_b4_122	@tr_var_b4_136	@tr_var_b4_163
a	NULL	Test 3.5.9.3	8	123.170000000000000000000000000000
select  @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, 
@tr_var_af_136, @tr_var_af_163;
@tr_var_af_118	@tr_var_af_121	@tr_var_af_122	@tr_var_af_136	@tr_var_af_163
a	NULL	Test 3.5.9.3	8	123.170000000000000000000000000000
drop trigger trg2_a;
drop trigger trg2_b;
drop trigger trg2_c;
drop trigger trg2_d;

Testcase 3.5.9.4:
-----------------
Create trigger trg3_a before insert on tb3 for each row
set @tr_var_b4_118=new.f118, @tr_var_b4_121=new.f121,
@tr_var_b4_122=new.f122, @tr_var_b4_136=new.f136,
@tr_var_b4_151=new.f151, @tr_var_b4_163=new.f163;
Create trigger trg3_b after insert on tb3 for each row
set @tr_var_af_118=new.f118, @tr_var_af_121=new.f121,
@tr_var_af_122=new.f122, @tr_var_af_136=new.f136,
@tr_var_af_151=new.f151, @tr_var_af_163=new.f163;
Create trigger trg3_c before update on tb3 for each row
set @tr_var_b4_118=new.f118, @tr_var_b4_121=new.f121,
@tr_var_b4_122=new.f122, @tr_var_b4_136=new.f136,
@tr_var_b4_151=new.f151, @tr_var_b4_163=new.f163;
Create trigger trg3_d after update on tb3 for each row
set @tr_var_af_118=new.f118, @tr_var_af_121=new.f121,
@tr_var_af_122=new.f122, @tr_var_af_136=new.f136,
@tr_var_af_151=new.f151, @tr_var_af_163=new.f163;
@tr_var_b4_118	@tr_var_b4_121	@tr_var_b4_122	@tr_var_b4_136	@tr_var_b4_151	@tr_var_b4_163
0	0	0	0	0	0
@tr_var_af_118	@tr_var_af_121	@tr_var_af_122	@tr_var_af_136	@tr_var_af_151	@tr_var_af_163
0	0	0	0	0	0
Insert into tb3 (f122, f136, f151, f163) 
values ('Test 3.5.9.4', 7, DEFAULT, 995.24);
select f118, f121, f122, f136, f151, f163 from tb3 
where f122 like 'Test 3.5.9.4%';
f118	f121	f122	f136	f151	f163
a	NULL	Test 3.5.9.4	00007	999	995.240000000000000000000000000000
select  @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, 
@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
@tr_var_b4_118	@tr_var_b4_121	@tr_var_b4_122	@tr_var_b4_136	@tr_var_b4_151	@tr_var_b4_163
a	NULL	Test 3.5.9.4	7	999	995.240000000000000000000000000000
select  @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, 
@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
@tr_var_af_118	@tr_var_af_121	@tr_var_af_122	@tr_var_af_136	@tr_var_af_151	@tr_var_af_163
a	NULL	Test 3.5.9.4	7	999	995.240000000000000000000000000000
@tr_var_b4_118	@tr_var_b4_121	@tr_var_b4_122	@tr_var_b4_136	@tr_var_b4_151	@tr_var_b4_163
0	0	0	0	0	0
@tr_var_af_118	@tr_var_af_121	@tr_var_af_122	@tr_var_af_136	@tr_var_af_151	@tr_var_af_163
0	0	0	0	0	0
Update tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL
where f122='Test 3.5.9.4';
Warnings:
Warning	1263	Column set to default value; NULL supplied to NOT NULL column 'f136' at row 20
select f118, f121, f122, f136, f151, f163 from tb3 
where f122 like 'Test 3.5.9.4-trig';
f118	f121	f122	f136	f151	f163
a	NULL	Test 3.5.9.4-trig	00000	999	NULL
select  @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, 
@tr_var_b4_136, @tr_var_b4_151, @tr_var_b4_163;
@tr_var_b4_118	@tr_var_b4_121	@tr_var_b4_122	@tr_var_b4_136	@tr_var_b4_151	@tr_var_b4_163
a	NULL	Test 3.5.9.4-trig	0	999	NULL
select  @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, 
@tr_var_af_136, @tr_var_af_151, @tr_var_af_163;
@tr_var_af_118	@tr_var_af_121	@tr_var_af_122	@tr_var_af_136	@tr_var_af_151	@tr_var_af_163
a	NULL	Test 3.5.9.4-trig	0	999	NULL
drop trigger trg3_a;
drop trigger trg3_b;
drop trigger trg3_c;
drop trigger trg3_d;
delete from tb3 where f122='Test 3.5.9.4-trig';

Testcase 3.5.9.5: (implied in previous tests)
---------------------------------------------

Testcase 3.5.9.6:
-----------------
create trigger trg4a before insert on tb3 for each row
set @temp1= old.f120;
ERROR HY000: There is no OLD row in on INSERT trigger
create trigger trg4b after insert on tb3 for each row
set old.f120= 'test';
ERROR HY000: Updating of OLD row is not allowed in trigger
drop trigger trg4a;
drop trigger trg4b;

Testcase 3.5.9.7: (implied in previous tests)
---------------------------------------------

Testcase 3.5.9.8: (implied in previous tests)
---------------------------------------------

Testcase 3.5.9.9:
-----------------
create trigger trg5a before DELETE on tb3 for each row
set @temp1=new.f122;
ERROR HY000: There is no NEW row in on DELETE trigger
create trigger trg5b after DELETE on tb3 for each row
set new.f122='test';
ERROR HY000: There is no NEW row in on DELETE trigger
drop trigger trg5a;
drop trigger trg5b;

Testcase 3.5.9.10: (implied in previous tests)
----------------------------------------------

Testcase 3.5.9.11: covered by 3.5.9.9
-------------------------------------

Testcase 3.5.9.12: covered by 3.5.9.6
-------------------------------------

Testcase 3.5.9.13:
------------------
create trigger trg6a before UPDATE on tb3 for each row
set old.f118='C', new.f118='U';
ERROR HY000: Updating of OLD row is not allowed in trigger
create trigger trg6b after INSERT on tb3 for each row
set old.f136=163, new.f118='U';
ERROR HY000: Updating of OLD row is not allowed in trigger
create trigger trg6c after UPDATE on tb3 for each row
set old.f136=NULL;
ERROR HY000: Updating of OLD row is not allowed in trigger
drop trigger trg6a;
drop trigger trg6b;
drop trigger trg6c;

Testcase 3.5.9.14: (implied in previous tests)
----------------------------------------------

Testcase 3.5.10.1/2/3:
----------------------
Create view vw11 as select * from tb3
where f122 like 'Test 3.5.10.1/2/3%';
Create trigger trg1a before insert on tb3 
for each row set new.f163=111.11;
Create trigger trg1b after insert on tb3 
for each row set @test_var='After Insert';
Create trigger trg1c before update on tb3 
for each row set new.f121='Y', new.f122='Test 3.5.10.1/2/3-Update';
Create trigger trg1d after update on tb3 
for each row set @test_var='After Update';
Create trigger trg1e before delete on tb3 
for each row set @test_var=5;
Create trigger trg1f after delete on tb3 
for each row set @test_var= 2* @test_var+7;
Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 1);
Insert into vw11 (f122, f151) values ('Test 3.5.10.1/2/3', 2);
Insert into vw11 (f122, f151) values ('Not in View', 3);
select f121, f122, f151, f163 
from tb3 where f122 like 'Test 3.5.10.1/2/3%';
f121	f122	f151	f163
NULL	Test 3.5.10.1/2/3	2	111.110000000000000000000000000000
NULL	Test 3.5.10.1/2/3	1	111.110000000000000000000000000000
select f121, f122, f151, f163 from vw11;
f121	f122	f151	f163
NULL	Test 3.5.10.1/2/3	2	111.110000000000000000000000000000
NULL	Test 3.5.10.1/2/3	1	111.110000000000000000000000000000
select f121, f122, f151, f163 
from tb3 where f122 like 'Not in View';
f121	f122	f151	f163
NULL	Not in View	3	111.110000000000000000000000000000
Update vw11 set f163=1;
select f121, f122, f151, f163 from tb3 
where f122 like 'Test 3.5.10.1/2/3%';
f121	f122	f151	f163
Y	Test 3.5.10.1/2/3-Update	2	1.000000000000000000000000000000
Y	Test 3.5.10.1/2/3-Update	1	1.000000000000000000000000000000
select f121, f122, f151, f163 from vw11;
f121	f122	f151	f163
Y	Test 3.5.10.1/2/3-Update	2	1.000000000000000000000000000000
Y	Test 3.5.10.1/2/3-Update	1	1.000000000000000000000000000000
set @test_var=0;
Select @test_var as 'before delete';
before delete
0
delete from vw11 where f151=1;
select f121, f122, f151, f163 from tb3 
where f122 like 'Test 3.5.10.1/2/3%';
f121	f122	f151	f163
Y	Test 3.5.10.1/2/3-Update	2	1.000000000000000000000000000000
select f121, f122, f151, f163 from vw11;
f121	f122	f151	f163
Y	Test 3.5.10.1/2/3-Update	2	1.000000000000000000000000000000
Select @test_var as 'after delete';
after delete
17
drop view vw11;
drop trigger trg1a;
drop trigger trg1b;
drop trigger trg1c;
drop trigger trg1d;
drop trigger trg1e;
drop trigger trg1f;
delete from tb3 where f122 like 'Test 3.5.10.1/2/3%';

Testcase 3.5.10.4:
------------------
create table tb_load (f1 int, f2 char(25),f3 int) engine=memory;
Create trigger trg4 before insert on tb_load 
for each row set new.f3=-(new.f1 div 5), @counter= @counter+1;
set @counter= 0;
select @counter as 'Rows Loaded Before';
Rows Loaded Before
0
load data infile 'MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table tb_load;
select @counter as 'Rows Loaded After';
Rows Loaded After
10
Select * from tb_load limit 10;
f1	f2	f3
-5000	a`	1000
-4999	aaa	999
-4998	abaa	999
-4997	acaaa	999
-4996	adaaaa	999
-4995	aeaaaaa	999
-4994	afaaaaaa	998
-4993	agaaaaaaa	998
-4992	a^aaaaaaaa	998
-4991	a_aaaaaaaaa	998
drop trigger trg4;
drop table tb_load;

Testcase 3.5.10.5: (implemented in trig_frkey.test)
---------------------------------------------------

Testcase 3.5.10.6: (implemented in trig_frkey.test)
---------------------------------------------------

Testcase 3.5.10.extra:
----------------------
create table t1_sp (var136 tinyint, var151 decimal) engine=memory;
create trigger trg before insert on t1_sp 
for each row set @counter=@counter+1;
create procedure trig_sp()
begin
declare done int default 0;
declare var151 decimal;
declare var136 tinyint;
declare cur1 cursor for select f136, f151 from tb3;
declare continue handler for sqlstate '01000' set done = 1;
open cur1;
fetch cur1 into var136, var151;
wl_loop: WHILE NOT done DO 
insert into t1_sp values (var136, var151);
fetch cur1 into var136, var151;
END WHILE wl_loop;
close cur1;
end//
set @counter=0;
select @counter;
@counter
0
call trig_sp();
ERROR 02000: No data to FETCH
select @counter;
@counter
20
select count(*) from tb3;
count(*)
20
select count(*) from t1_sp;
count(*)
20
drop procedure trig_sp;
drop trigger trg;
drop table t1_sp;

Testcase 3.5.11.1 (implemented in trig_perf.test)
-------------------------------------------------
drop user test_general@localhost;
drop user test_general;
drop user test_super@localhost;

Testcase y.y.y.2: Check for triggers starting triggers
------------------------------------------------------
use test;
drop table if exists t1;
drop table if exists t2_1;
drop table if exists t2_2;
drop table if exists t2_3;
drop table if exists t2_4;
drop table if exists t3;
create table t1 (f1 integer);
create table t2_1 (f1 integer);
create table t2_2 (f1 integer);
create table t2_3 (f1 integer);
create table t2_4 (f1 integer);
create table t3 (f1 integer);
insert into t1 values (1);
create trigger tr1 after insert on t1 for each row 
BEGIN
insert into t2_1 (f1) values (new.f1+1);
insert into t2_2 (f1) values (new.f1+1);
insert into t2_3 (f1) values (new.f1+1);
insert into t2_4 (f1) values (new.f1+1);
END//
create trigger tr2_1 after insert on t2_1 for each row 
insert into t3 (f1) values (new.f1+10);
create trigger tr2_2 after insert on t2_2 for each row 
insert into t3 (f1) values (new.f1+100);
create trigger tr2_3 after insert on t2_3 for each row 
insert into t3 (f1) values (new.f1+1000);
create trigger tr2_4 after insert on t2_4 for each row 
insert into t3 (f1) values (new.f1+10000);
insert into t1 values (1);
select * from t3;
f1
12
102
1002
10002
drop trigger tr1;
drop trigger tr2_1;
drop trigger tr2_2;
drop trigger tr2_3;
drop trigger tr2_4;
drop table t1, t2_1, t2_2, t2_3, t2_4, t3;

Testcase y.y.y.3: Circular trigger reference
--------------------------------------------
use test;
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
create table t1 (f1 integer) engine = memory;
create table t2 (f2 integer) engine = memory;
create table t3 (f3 integer) engine = memory;
create table t4 (f4 integer) engine = memory;
insert into t1 values (0);
create trigger tr1 after insert on t1 
for each row insert into t2 (f2) values (new.f1+1);
create trigger tr2 after insert on t2 
for each row insert into t3 (f3) values (new.f2+1);
create trigger tr3 after insert on t3 
for each row insert into t4 (f4) values (new.f3+1);
create trigger tr4 after insert on t4 
for each row insert into t1 (f1) values (new.f4+1);
insert into t1 values (1);
ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
select * from t1;
f1
0
1
select * from t2;
f2
2
select * from t3;
f3
3
select * from t4;
f4
4
drop trigger tr1;
drop trigger tr2;
drop trigger tr3;
drop trigger tr4;
drop table t1;
drop table t2;
drop table t3;
drop table t4;

Testcase y.y.y.4: Recursive trigger/SP references (disabled bug 11889)
----------------------------------------------------------------------
set @sql_mode='traditional';
create table t1_sp (
count integer, 
var136 tinyint, 
var151 decimal) engine=memory;
create procedure trig_sp()
begin
declare done int default 0;
declare var151 decimal;
declare var136 tinyint;
declare cur1 cursor for select f136, f151 from tb3;
declare continue handler for sqlstate '01000' set done = 1;
set @counter= @counter+1;
open cur1;
fetch cur1 into var136, var151;
wl_loop: WHILE NOT done DO 
insert into t1_sp values (@counter, var136, var151);
fetch cur1 into var136, var151;
END WHILE wl_loop;
close cur1;
end//
create trigger trg before insert on t1_sp 
for each row call trig_sp();
set @counter=0;
select @counter;
@counter
0
call trig_sp();
ERROR HY000: Recursive stored routines are not allowed.
select @counter;
@counter
1
select count(*) from tb3;
count(*)
20
select count(*) from t1_sp;
count(*)
0
drop procedure trig_sp;
drop trigger trg;
drop table t1_sp;

Testcase y.y.y.5: Roleback of nested trigger references
-------------------------------------------------------
set @@sql_mode='traditional';
use test;
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
create table t1 (f1 integer) engine = memory;
create table t2 (f2 integer) engine = memory;
create table t3 (f3 integer) engine = memory;
create table t4 (f4 tinyint) engine = memory;
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) default NULL
) ENGINE=MEMORY DEFAULT CHARSET=latin1
insert into t1 values (1);
create trigger tr1 after insert on t1 
for each row insert into t2 (f2) values (new.f1+1);
create trigger tr2 after insert on t2 
for each row insert into t3 (f3) values (new.f2+1);
create trigger tr3 after insert on t3 
for each row insert into t4 (f4) values (new.f3+1000);
set autocommit=0;
start transaction;
insert into t1 values (1);
ERROR 22003: Out of range value adjusted for column 'f4' at row 1
commit;
select * from t1;
f1
1
1
select * from t2;
f2
2
select * from t3;
f3
3
drop trigger tr1;
drop trigger tr2;
drop trigger tr3;
drop table t1;
drop table t2;
drop table t3;
drop table t4;