diff options
| author | unknown <hhunger@hh-nb.hungers> | 2007-02-06 13:35:54 +0100 |
|---|---|---|
| committer | unknown <hhunger@hh-nb.hungers> | 2007-02-06 13:35:54 +0100 |
| commit | ee5eb8bbe11eb055f73b1abc40d718e310d6b6c5 (patch) | |
| tree | c6bc38df732957ee9ed554246aac43d2d7940306 /mysql-test/suite/funcs_1/triggers | |
| parent | 13390debf6aa979ca31437d687eaa9ae2740ad2f (diff) | |
| download | mariadb-git-ee5eb8bbe11eb055f73b1abc40d718e310d6b6c5.tar.gz | |
copy from test-extra-5.1 to main tree
BitKeeper/etc/ignore:
Added mysql-test/suite/funcs_1/r/innodb_views.warnings mysql-test/suite/funcs_1/r/memory_trig_03e.warnings mysql-test/suite/funcs_1/r/memory_views.warnings mysql-test/suite/funcs_1/r/myisam_trig_03e.warnings mysql-test/suite/funcs_1/r/myisam_views.warnings mysql-test/suite/funcs_1/r/ndb_trig_03e.warnings mysql-test/suite/funcs_1/r/ndb_views.warnings mysql-test/suite/partitions/r/diff mysql-test/suite/partitions/r/partition_bit_ndb.warnings mysql-test/suite/partitions/r/partition_special_innodb.warnings mysql-test/suite/partitions/r/partition_special_myisam.warnings storage/archive/archive_reader mysql-test/suite/funcs_1/r/innodb_trig_03e.warnings to the ignore list
mysql-test/suite/funcs_2/include/check_charset.inc:
inserted newline at the end of file.
mysql-test/suite/objects/include/drop_all.inc:
inserted newline at the end of file.
mysql-test/suite/partitions/include/partition_key_32col.inc:
inserted newline at the end of file.
mysql-test/suite/rpl/data/rpl_mixed.dat:
inserted newline at the end of file.
mysql-test/suite/rpl/include/rpl_mixed_check_event.inc:
inserted newline at the end of file.
mysql-test/suite/rpl/include/rpl_mixed_check_select.inc:
inserted newline at the end of file.
mysql-test/suite/rpl/include/rpl_mixed_check_user.inc:
inserted newline at the end of file.
mysql-test/suite/rpl/include/rpl_mixed_check_view.inc:
inserted newline at the end of file.
Diffstat (limited to 'mysql-test/suite/funcs_1/triggers')
17 files changed, 7655 insertions, 0 deletions
diff --git a/mysql-test/suite/funcs_1/triggers/trig_frkey.inc b/mysql-test/suite/funcs_1/triggers/trig_frkey.inc new file mode 100644 index 00000000000..5d6b6a72b1e --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/trig_frkey.inc @@ -0,0 +1,93 @@ +################################################################# +# This file inclde tests that address the foreign key cases of +# the following requirements since they are specific to innodb. +# Other test cases for these requirements are included in the +# triggers_master.test file. +################################################################# + +--disable_abort_on_error + +#Section x.x.x.1 +# Test case: Verifing that a trigger that activates a primary key results in +# the primary key acting correctly on the foreign key +let $message= Testcase x.x.x.1:; +--source include/show_msg.inc + + + --disable_warnings + DROP TABLE IF EXISTS t0, t1, t2; + --enable_warnings + + eval CREATE TABLE t0 (col1 char(50)) ENGINE=$engine_type; + eval CREATE TABLE t1 (id INT NOT NULL, col1 char(50), + PRIMARY KEY (id)) ENGINE=$engine_type; + eval CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT, + INDEX par_ind (f_id), col1 char(50), + FOREIGN KEY (f_id) REFERENCES t1(id) + ON DELETE SET NULL) ENGINE=$engine_type; + + insert into t1 values (1,'Department A'); + insert into t1 values (2,'Department B'); + insert into t1 values (3,'Department C'); + insert into t2 values (1,2,'Emp 1'); + insert into t2 values (2,2,'Emp 2'); + insert into t2 values (3,2,'Emp 3'); + + create trigger trig after insert on t0 for each row + delete from t1 where col1=new.col1; + + select * from t2; +lock tables t0 write, t1 write; + insert into t0 values ('Department B'); +unlock tables; + select * from t2; + +# Cleanup + drop trigger trig; + drop table t2, t1; + + +#Section x.x.x.2 +# Test case: Checking that triggers can be used as a way to address missing foreign +# key definition +let $message= Testcase x.x.x.2:; +--source include/show_msg.inc + + --disable_warnings + DROP TABLE IF EXISTS t1, t2; + --enable_warnings + + eval CREATE TABLE t1 (id INT NOT NULL, col1 char(50), + PRIMARY KEY (id)) ENGINE=$engine_type; + eval CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT, + INDEX par_ind (f_id), col1 char(50), + FOREIGN KEY (f_id) REFERENCES t1(id) + ON UPDATE CASCADE) ENGINE=$engine_type; + + insert into t1 values (1,'Department A'); + insert into t1 values (2,'Department B'); + insert into t1 values (3,'Department C'); + insert into t2 values (1,2,'Emp 1'); + insert into t2 values (2,3,'Emp 2'); + + --error 1452 + insert into t2 values (3,4,'Emp 3'); + + create trigger tr_t2 before insert on t2 for each row + insert into t1 values(new.f_id, concat('New Department ', new.f_id)); + +lock tables t1 write, t2 write; + insert into t2 values (3,4,'Emp 3'); +unlock tables; + + select * from t1; + select * from t2; + +# Cleanup + drop trigger tr_t2; + drop table t2, t1, t0; + + +let $message= Foreign Key tests disabled (bug 11472 - stored in trig_frkey2.test); +--source include/show_msg.inc + diff --git a/mysql-test/suite/funcs_1/triggers/trig_frkey2.inc b/mysql-test/suite/funcs_1/triggers/trig_frkey2.inc new file mode 100644 index 00000000000..51cc2a81d18 --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/trig_frkey2.inc @@ -0,0 +1,244 @@ +################################################################# +# This file inclde tests that address the foreign key cases of +# the following requirements since they are specific to innodb. +# Other test cases for these requirements are included in the +# triggers_master.test file. +################################################################# + +--disable_abort_on_error + +# OBN - The following tests are disabled until triggers are supported with forign +# keys in innodb (foreign keys tests dispabled - bug 11472) +################################################################################# +#Section x.x.x.3 +# Test case: Similar to 3.5.10.5 but with ten tables to see if multiple triggers +# can be executed at once +let $message= Testcase x.x.x.3:; +--source include/show_msg.inc + + --disable_warnings + DROP TABLE IF EXISTS t1, t2; + --enable_warnings + + eval CREATE TABLE t0 (col1 char(50)) ENGINE=$engine_type; + eval CREATE TABLE t1 (id INT NOT NULL, col1 char(50), + PRIMARY KEY (id)) ENGINE=$engine_type; + eval CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT, INDEX par_ind + (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id) + ON DELETE SET NULL) ENGINE=$engine_type; + eval CREATE TABLE t3 (id INT PRIMARY KEY, f_id INT, INDEX par_ind + (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id) + ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type; + eval CREATE TABLE t4 (id INT PRIMARY KEY, f_id INT, INDEX par_ind + (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id) + ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type; + eval CREATE TABLE t5 (id INT PRIMARY KEY, f_id INT, INDEX par_ind + (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id) + ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type; + eval CREATE TABLE t6 (id INT PRIMARY KEY, f_id INT, INDEX par_ind + (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id) + ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type; + eval CREATE TABLE t7 (id INT PRIMARY KEY, f_id INT, INDEX par_ind + (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id) + ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type; + eval CREATE TABLE t8 (id INT PRIMARY KEY, f_id INT, INDEX par_ind + (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id) + ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type; + eval CREATE TABLE t9 (id INT PRIMARY KEY, f_id INT, INDEX par_ind + (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id) + ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type; + eval CREATE TABLE t10(id INT PRIMARY KEY, f_id INT, INDEX par_ind + (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id) + ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type; + eval CREATE TABLE t11(id INT PRIMARY KEY, f_id INT, INDEX par_ind + (f_id), col1 char(50), FOREIGN KEY (f_id) REFERENCES t1(id) + ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type; + + create trigger tr1 after update on t2 for each row + insert into t0 values ('tr_t2'); + create trigger tr2 after update on t3 for each row + insert into t0 values ('tr_t3'); + create trigger tr3 after update on t4 for each row + insert into t0 values ('tr_t4'); + create trigger tr3 after update on t5 for each row + insert into t0 values ('tr_t5'); + create trigger tr4 after update on t6 for each row + insert into t0 values ('tr_t6'); + create trigger tr5 after update on t7 for each row + insert into t0 values ('tr_t7'); + create trigger tr5 after update on t8 for each row + insert into t0 values ('tr_t8'); + create trigger tr6 after update on t9 for each row + insert into t0 values ('tr_t9'); + create trigger tr7 after update on t10 for each row + insert into t0 values ('tr_t10'); + create trigger tr8 after update on t11 for each row + insert into t0 values ('tr_t11'); + + insert into t1 values (1,'Department A'); + insert into t1 values (2,'Department B'); + insert into t1 values (3,'Department C'); + + insert into t2 values (1,2,'Employee'); + insert into t3 values (1,2,'Employee'); + insert into t4 values (1,2,'Employee'); + insert into t5 values (1,2,'Employee'); + insert into t6 values (1,2,'Employee'); + insert into t7 values (1,2,'Employee'); + insert into t8 values (1,2,'Employee'); + insert into t9 values (1,2,'Employee'); + insert into t10 values (1,2,'Employee'); + insert into t11 values (1,2,'Employee'); + + select * from t1; + select * from t2; + select * from t3; + select * from t4; + select * from t5; + select * from t6; + select * from t7; + select * from t8; + select * from t9; + select * from t10; + select * from t11; + + delete from t1 where id=2; + select * from t1; + select * from t2; + select * from t3; + select * from t4; + select * from t5; + select * from t6; + select * from t7; + select * from t8; + select * from t9; + select * from t10; + select * from t11; + + select * from t0; + +# Cleanup + drop trigger tr1; + drop trigger tr2; + drop trigger tr3; + drop trigger tr4; + drop trigger tr5; + drop trigger tr6; + drop trigger tr7; + drop trigger tr8; + drop trigger tr9; + drop trigger tr10; + drop table t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t1,t0; + + + + +#Section 3.5.10.5 +# Test case: Ensure that every trigger that should be activated by every possible +# type of implicit update of its subject table (e.g. a FOREIGN KEY SET +# DEFAULT action or an UPDATE of a view based on the subject table) +# is indeed activated correctly. +let $message= Testcase 3.5.10.5 (foreign keys):; +--source include/show_msg.inc + + + --disable_warnings + DROP TABLE IF EXISTS t1, t2; + --enable_warnings + + eval CREATE TABLE t1 (id INT NOT NULL, col1 char(50), + PRIMARY KEY (id)) ENGINE=$engine_type; + eval CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT, + INDEX par_ind (f_id), col1 char(50), + FOREIGN KEY (f_id) REFERENCES t1(id) + ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=$engine_type; + create trigger tr_t2 after update on t2 + for each row set @counter=@counter+1; + + insert into t1 values (1,'Department A'); + insert into t1 values (2,'Department B'); + insert into t1 values (3,'Department C'); + insert into t2 values (1,2,'Emp 1'); + insert into t2 values (2,2,'Emp 2'); + insert into t2 values (3,2,'Emp 3'); + insert into t2 values (4,2,'Emp 4'); + insert into t2 values (5,2,'Emp 5'); + insert into t2 values (6,3,'Emp 6'); + set @counter=0; + + select * from t1; + select * from t2; + select @counter; + + update t1 set id=4 where id=3; + select * from t1; + select * from t2; + select @counter; + + delete from t1 where id=2; + select * from t1; + select * from t2; + select @counter; + +# This is to verify that the trigger works when updated directly + update t2 set col1='Emp 5a' where id=5; + select * from t2; + select @counter; + +# Cleanup + drop trigger tr_t2; + drop table t2, t1; + + +#Section 3.5.10.6 +# Test case: Ensure that every trigger that should be activated by every possible +# type of implicit deletion from its subject table (e.g. a FOREIGN KEY +# CASCADE action or a DELETE from a view based on the subject table) +# is indeed activated correctly. +let $message= Testcase 3.5.10.6 (foreign keys):; +--source include/show_msg.inc + + --disable_warnings + DROP TABLE IF EXISTS t1, t2; + --enable_warnings + + eval CREATE TABLE t1 (id INT NOT NULL, col1 char(50), + PRIMARY KEY (id)) ENGINE=$engine_type; + eval CREATE TABLE t2 (id INT PRIMARY KEY, f_id INT, + INDEX par_ind (f_id), col1 char(50), + FOREIGN KEY (f_id) REFERENCES t1(id) + ON DELETE CASCADE) ENGINE=$engine_type; + + create trigger tr_t2 before delete on t2 + for each row set @counter=@counter+1; + + insert into t1 values (1,'Department A'); + insert into t1 values (2,'Department B'); + insert into t1 values (3,'Department C'); + insert into t2 values (1,2,'Emp 1'); + insert into t2 values (2,2,'Emp 2'); + insert into t2 values (3,2,'Emp 3'); + insert into t2 values (4,2,'Emp 4'); + insert into t2 values (5,2,'Emp 5'); + insert into t2 values (6,3,'Emp 6'); + set @counter=0; + + select * from t1; + select * from t2; + select @counter; + + delete from t1 where id=2; + + select * from t1; + select * from t2; + select @counter; + +# This is to verify that the trigger works when deleted directly + delete from t2 where id=6; + select * from t2; + select @counter; + +# Cleanup + drop trigger tr_t2; + drop table t2, t1; + diff --git a/mysql-test/suite/funcs_1/triggers/triggers_0102.inc b/mysql-test/suite/funcs_1/triggers/triggers_0102.inc new file mode 100644 index 00000000000..ce06e0c0c3e --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/triggers_0102.inc @@ -0,0 +1,447 @@ +#====================================================================== +# +# Trigger Tests +# (test case numbering refer to requirement document TP v1.1) +#====================================================================== + +# OBM - ToDo +############ +# 1. Performace +############################################### + +--disable_abort_on_error + +##################################################### +################# Section 3.5.1 ##################### +# Syntax checks for CREATE TRIGGER and DROP TRIGGER # +##################################################### + +#Section 3.5.1.1 +# Testcase: Ensure that all clauses that should be supported are supported. +let $message= Testcase: 3.5.1.1:; +--source include/show_msg.inc +# OBN - This test case tests basic trigger definition and execution +# of INSERT/UPDATE/DELETE actions and BEFORE/AFTER timings. +# As such it covers the equirements in sections 3.5.6.1, 3.5.6.2, +# 3.5.6.4, 3.5.6.5, 3.5.7.1, 3.5.7.2, 3.5.7.3, 3.5.7.17 below. +# - Note currently as a result of limitations with locking tables in +# triggers, a specifc lockingof the tables is done. +# Once fixed, the locking and alias referances should be removed + +use test; +# Trigger Definition + 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'; +# Trigger Execution Insert (before and after) + set @test_before = 1; + set @test_after = 5; + select @test_before, @test_after; + 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'; + select @test_before, @test_after; + +# Trigger Execution Update (before and after) + set @test_before = 18; + set @test_after = 8; + select @test_before, @test_after; + 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'; + select @test_before, @test_after; + +# Trigger Execution Delete (before and after) + 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'; + select @test_before, @test_after; + 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'; + select @test_before, @test_after; + +#Cleanup + --disable_warnings + --error 0, 1360 + drop trigger trg1_1; + --error 0, 1360 + drop trigger trg1_2; + --error 0, 1360 + drop trigger trg1_3; + --error 0, 1360 + drop trigger trg1_4; + --error 0, 1360 + drop trigger trg1_5; + --error 0, 1360 + drop trigger trg1_6; + --enable_warnings + delete from tb3 where f121='Test 3.5.1.1'; + --enable_warnings + +#Section 3.5.1.2 +# Testcase: Ensure that all clauses that should not be supported are disallowed +# with an appropriate error message. +let $message= Testcase: 3.5.1.2:; +--source include/show_msg.inc + + --error 1064 + Create trigger trg_1 after insert + on tb3 for each statement set @x= 1; + +#Cleanup + --disable_warnings + --error 0, 1360 + drop trigger trg_1; + --enable_warnings + + +#Section 3.5.1.3 +# Testcase: Ensure that all supported clauses are supported only in the correct order. +let $message= Testcase 3.5.1.3:; +--source include/show_msg.inc + --error 1064 + CREATE TRIGGER trg3_1 on tb3 BEFORE INSERT for each row set new.f120 = 't'; + + --error 1064 + CREATE trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's'; + + --error 1064 + CREATE TRIGGER trg3_3 Before DELETE on tb3 set @ret1 = 'test' for each row; + + --error 1064 + CREATE TRIGGER trg3_4 DELETE AFTER on tb3 set @ret1 = 'test' for each row; + + --error 1064 + CREATE for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test'; + +#Cleanup +# OBN - Although none of the above should have been created we should do a cleanup +# since if they have been created, not dropping them will affect following +# tests. + --disable_warnings + --error 0, 1360 + drop trigger trg3_1; + --error 0, 1360 + drop trigger trg3_2; + --error 0, 1360 + drop trigger trg3_3; + --error 0, 1360 + drop trigger trg3_4; + --error 0, 1360 + drop trigger trg3_5; + --enable_warnings + + +#Section 3.5.1.4 +# Testcase: Ensure that an appropriate error message is returned if a clause +# is out-of-order in an SQL statement. +# OBN - FIXME - Missing 3.5.1.4 need to add + +#Section 3.5.1.5 +# Testcase: Ensure that all clauses that are defined to be mandatory are indeed +# required to be mandatory by the MySQL server and tools +let $message= Testcase: 3.5.1.5:; +--source include/show_msg.inc + + --error 1064 + CREATE TRIGGER trg4_1 AFTER on tb3 for each row set new.f120 = 'e'; + + --error 1064 + CREATE TRIGGER trg4_2 INSERT on tb3 for each set row new.f120 = 'f'; + + --error 1064 + CREATE TRIGGER trg4_3 BEFORE INSERT tb3 for each row set new.f120 = 'g'; + + --error 1064 + CREATE TRIGGER trg4_4 AFTER UPDATE on tb3 for each set new.f120 = 'g'; + + --error 1064 + CREATE trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g'; + + --error 1064 + CREATE TRIGGER trg4_6 BEFORE DELETE for each row set new.f120 = 'g'; + +#Cleanup +# OBN - Although none of the above should have been created we should do a cleanup +# since if they have been created, not dropping them will affect following +# tests. + --disable_warnings + --error 0, 1360 + drop trigger trg4_1; + --error 0, 1360 + drop trigger trg4_2; + --error 0, 1360 + drop trigger trg4_3; + --error 0, 1360 + drop trigger trg4_4; + --error 0, 1360 + drop trigger trg4_5; + --error 0, 1360 + drop trigger trg4_6; + --enable_warnings + +#Section 3.5.1.6 +# Testcase: Ensure that any clauses that are defined to be optional are indeed +# trated as optional by MySQL server and tools +let $message= Testcase 3.5.1.6: - Need to fix; +--source include/show_msg.inc +# OBN - FIXME - Missing 3.5.1.6 need to add + +#Section 3.5.1.7 +# Testcase: Ensure that all valid, fully-qualified, and non-qualified, +# trigger names are accepted, at creation time. +let $message= Testcase 3.5.1.7: - need to fix; +--source include/show_msg.inc + + drop table if exists t1; + eval create table t1 (f1 int, f2 char(25),f3 int) engine=$engine_type; + 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; + update t1 set f2='update 3.5.1.7'; + select * from t1; + select trigger_name from information_schema.triggers order by trigger_name; + +#Cleanup + --disable_warnings + --error 0, 1360 + drop trigger trg5_1; + # The above trigger should be dropped since the name was trimmed. + drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ; + drop table t1; + +#Section 3.5.1.8 +# Testcase: Ensure that any invalid trigger name is never accepted, and that an +# appropriate error message is returned when the name is rejected. +let $message= Testcase 3.5.1.8:; +--source include/show_msg.inc + + --error 1064 + CREATE TRIGGER trg12* before insert on tb3 for each row set new.f120 = 't'; + + --error 1064 + CREATE TRIGGER trigger before insert on tb3 for each row set new.f120 = 't'; + + --error 1064 + CREATE TRIGGER 100 before insert on tb3 for each row set new.f120 = 't'; + + --error 1064 + CREATE TRIGGER @@view before insert on tb3 for each row set new.f120 = 't'; + + --error 1064 + CREATE TRIGGER @name before insert on tb3 for each row set new.f120 = 't'; + + --error 1435 + CREATE TRIGGER tb3.trg6_1 BEFORE INSERT on test.tb3 + for each row set new.f120 ='X'; + + --disable_warnings + drop database if exists trig_db; + --enable_warnings + create database trig_db; + use trig_db; + eval create table t1 (f1 integer) engine = $engine_type; + + # Can't create a trigger in a different database + use test; + --error 1435 + CREATE TRIGGER trig_db.trg6_2 AFTER INSERT on tb3 + for each row set @ret_trg6_2 = 5; + + # Can't create a trigger refrencing a table in a different db + use trig_db; + --error 1435 + CREATE TRIGGER trg6_3 AFTER INSERT on test.tb3 + for each row set @ret_trg6_3 = 18; + + use test; + +#Cleanup + --disable_warnings + drop database trig_db; +# OBN - Although none of the above should have been created we should do a cleanup +# since if they have been created, not dropping them will affect following +# tests. + --error 0, 1360 + drop trigger trg6_1; + --error 0, 1360 + drop trigger trg6_3; + --enable_warnings + +#Section 3.5.1.9 +#Testcase: Ensure that a reference to a non-existent trigger is rejected with +# an appropriate error message. +let $message= Testcase 3.5.1.9:(cannot be inplemented at this point); +--source include/show_msg.inc + + +#Section 3.5.1.10 +#Testcase: Ensure that it is not possible to create two triggers with the same name on +# the same table +let $message= Testcase 3.5.1.10:; +--source include/show_msg.inc + + CREATE TRIGGER trg7_1 BEFORE UPDATE on tb3 for each row set new.f120 ='X'; + + --error 1359 + CREATE TRIGGER trg7_1 AFTER INSERT on tb3 for each row set @x ='Y'; + +#Cleanup + --disable_warnings + --error 0, 1360 + drop trigger trg7_1; + --enable_warnings + + +#Section 3.5.1.? +# Testcase: Ensure that it is not possible to create two or more triggers with +# the same name, provided each is associated with a different table. +let $message= Testcase 3.5.1.?:; +--source include/show_msg.inc + + --disable_warnings + drop table if exists t1; + drop table if exists t2; + --enable_warnings + eval create table t1 (f1 char(50), f2 integer) engine = $engine_type; + eval create table t2 (f1 char(50), f2 integer) engine = $engine_type; + + create trigger trig before insert on t1 + for each row set new.f1 ='trig t1'; + + --error 1359 + create trigger trig before update on t2 + for each row set new.f1 ='trig t2'; + + insert into t1 value ('insert to t1',1); + select * from t1; + update t1 set f1='update to t1'; + select * from t1; + insert into t2 value ('insert to t2',2); + update t2 set f1='update to t1'; + select * from t2; + +#Cleanup + --disable_warnings + drop table t1; + drop table t2; + --error 0, 1360 + drop trigger trig; + --enable_warnings + + +#Section 3.5.1.11 +# Testcase: Ensure that it is possible to create two or more triggers with +# the same name, provided each resides in a different database +let $message= Testcase 3.5.1.11:; +--source include/show_msg.inc + + --disable_warnings + drop database if exists trig_db1; + drop database if exists trig_db2; + drop database if exists trig_db3; + --enable_warnings + create database trig_db1; + create database trig_db2; + create database trig_db3; + use trig_db1; + eval create table t1 (f1 char(50), f2 integer) engine = $engine_type; + create trigger trig before insert on t1 + for each row set new.f1 ='trig1', @test_var1='trig1'; + use trig_db2; + eval create table t2 (f1 char(50), f2 integer) engine = $engine_type; + create trigger trig before insert on t2 + for each row set new.f1 ='trig2', @test_var2='trig2'; + use trig_db3; + eval create table t1 (f1 char(50), f2 integer) engine = $engine_type; + 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; + select * from t1 order by f2; + select * from trig_db2.t2; + select * from trig_db3.t1; + select * from t1 order by f2; + use test; + +#Cleanup + --disable_warnings + drop database trig_db1; + drop database trig_db2; + drop database trig_db3; + --enable_warnings + +########################################### +################ Section 3.5.2 ############ +# Check for the global nature of Triggers # +########################################### + +#Section 3.5.2.1 +# Test case: Ensure that if a trigger created without a qualifying database +# name belongs to the database in use at creation time. +#Section 3.5.2.2 +# Test case: Ensure that if a trigger created with a qualifying database name +# belongs to the database specified. +#Section 3.5.2.3 +# Test case: Ensure that if a trigger created with a qualifying database name +# does not belong to the database in use at creation time unless +# the qualifying database name identifies the database that is +# also in use at creation time. +let $message= Testcase 3.5.2.1/2/3:; +--source include/show_msg.inc + + + --disable_warnings + drop database if exists trig_db1; + drop database if exists trig_db2; + --enable_warnings + create database trig_db1; + create database trig_db2; + use trig_db1; + eval create table t1 (f1 char(50), f2 integer) engine = $engine_type; + eval create table trig_db2.t1 (f1 char(50), f2 integer) engine = $engine_type; + 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 order by trigger_name; + + 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; + +#Cleanup + --disable_warnings + drop database trig_db1; + drop database trig_db2; diff --git a/mysql-test/suite/funcs_1/triggers/triggers_03.inc b/mysql-test/suite/funcs_1/triggers/triggers_03.inc new file mode 100644 index 00000000000..4821b47099a --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/triggers_03.inc @@ -0,0 +1,693 @@ +#====================================================================== +# +# Trigger Tests +# (test case numbering refer to requirement document TP v1.1) +#====================================================================== + +--disable_abort_on_error + +########################################### +################ Section 3.5.3 ############ +# Check for the global nature of Triggers # +########################################### + +# General setup to be used in all testcases of 3.5.3 +let $message= Testcase 3.5.3:; +--source include/show_msg.inc + + --disable_warnings + drop database if exists priv_db; + --enable_warnings + create database priv_db; + use priv_db; + eval create table t1 (f1 char(20)) engine= $engine_type; + + 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'); + +#Section 3.5.3.1 / 3.5.3.2 +# Test case: Ensure TRIGGER privilege is required to create a trigger +#Section 3.5.3.3 / 3.5.3.4 +# Test case: Ensure that root always has the TRIGGER privilege. +# OMR - No need to test this since SUPER priv is an existing one and not related +# or added for triggers (TP 2005-06-06) +#Section 3.5.3.5 / 3.5.3.6 +# Test case: Ensure that the TRIGGER privilege is required to drop a trigger. +let $message= Testcase 3.5.3.2/6:; +--source include/show_msg.inc + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; + grant ALL on *.* to test_noprivs@localhost; + revoke TRIGGER on *.* from test_noprivs@localhost; + show grants for test_noprivs@localhost; + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant TRIGGER on *.* to test_yesprivs@localhost; +# Adding the minimal priv to be able to set to the db + grant SELECT on priv_db.t1 to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + +let $message= Testcase 3.5.3.2:; +--source include/show_msg.inc + + connection no_privs; + select current_user; + use priv_db; + +# error 1227 is better, as it says, that not the privilege + --error 1142,1227 + create trigger trg1_1 before INSERT on t1 for each row + set new.f1 = 'trig 3.5.3.2_1-no'; + + connection default; + use priv_db; + insert into t1 (f1) values ('insert 3.5.3.2-no'); + select f1 from t1 order by f1; + + connection yes_privs; + select current_user; + use priv_db; + + create trigger trg1_2 before INSERT on t1 for each row + set new.f1 = 'trig 3.5.3.2_2-yes'; + + connection default; + select current_user; + use priv_db; + + --error 1143 + insert into t1 (f1) values ('insert 3.5.3.2-yes'); + select f1 from t1 order by f1; + + grant UPDATE on priv_db.t1 to test_yesprivs@localhost; + insert into t1 (f1) values ('insert 3.5.3.2-yes'); + select f1 from t1 order by f1; + +let $message= Testcase 3.5.3.6:; +--source include/show_msg.inc + + connection no_privs; + use priv_db; + + --error 1142,1227 + drop trigger trg1_2; + + connection default; + use priv_db; + insert into t1 (f1) values ('insert 3.5.3.6-yes'); + select f1 from t1 order by f1; + + connection yes_privs; + use priv_db; + + drop trigger trg1_2; + + connection default; + use priv_db; + insert into t1 (f1) values ('insert 3.5.3.6-no'); + select f1 from t1 order by f1; + +# Cleanup + --disable_warnings + connection default; + --error 0, 1360 + drop trigger trg1_2; + disconnect no_privs; + disconnect yes_privs; + --enable_warnings + + +#Section 3.5.3.7 +# Test case: Ensure that use of the construct "SET NEW. <column name> = <value>" +# fails at CREATE TRIGGER time, if the current user does not have the +# UPDATE privilege on the column specified +# Note: As a result of bug 8884 the triggers are actually created. +# Disabled because of bug 8884 + +# --- 3.5.3.7a - Privs set on a global level +let $message=Testcase 3.5.3.7a:; +--source include/show_msg.inc + + 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; + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant TRIGGER, UPDATE on *.* to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs_424a,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs_424a,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + + connection no_privs_424a; + select current_user; + use priv_db; + show grants; + select f1 from t1 order by f1; + +let $message= Trigger create disabled - should fail - Bug 8884; +--source include/show_msg.inc +# --error 1227 +# create trigger trg4a_1 before INSERT on t1 for each row +# set new.f1 = 'trig 3.5.3.7-1a'; + + connection default; + insert into t1 (f1) values ('insert 3.5.3.7-1a'); + select f1 from t1 order by f1; + --error 0, 1360 + drop trigger trg4a_1; + + connection yes_privs_424a; + use priv_db; + select current_user; + show grants; + create trigger trg4a_2 before INSERT on t1 for each row + set new.f1 = 'trig 3.5.3.7-2a'; + + connection default; + + insert into t1 (f1) values ('insert 3.5.3.7-2b'); + select f1 from t1 order by f1; + +# Cleanup + --disable_warnings + drop trigger trg4a_2; + disconnect no_privs_424a; + disconnect yes_privs_424a; + --enable_warnings + +# --- 3.5.3.7b - Privs set on a database level +let $message= Testcase 3.5.3.7b:; +--source include/show_msg.inc + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; + grant TRIGGER 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; + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant TRIGGER on *.* to test_yesprivs@localhost; + grant UPDATE on priv_db.* to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs_424b,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs_424b,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + + connection no_privs_424b; + show grants; + use priv_db; + +let $message= Trigger create disabled - should fail - Bug 8884; +--source include/show_msg.inc +# --error 1227 +# create trigger trg4b_1 before UPDATE on t1 for each row +# set new.f1 = 'trig 3.5.3.7-1b'; + + connection default; + insert into t1 (f1) values ('insert 3.5.3.7-1b'); + select f1 from t1 order by f1; + update t1 set f1 = 'update 3.5.3.7-1b' where f1 = 'insert 3.5.3.7-1b'; + select f1 from t1 order by f1; + --error 0, 1360 + drop trigger trg4b_1; + + connection yes_privs_424b; + show grants; + use priv_db; + create trigger trg4b_2 before UPDATE on t1 for each row + set new.f1 = 'trig 3.5.3.7-2b'; + + connection default; + + insert into t1 (f1) values ('insert 3.5.3.7-2b'); + select f1 from t1 order by f1; + update t1 set f1 = 'update 3.5.3.7-2b' where f1 = 'insert 3.5.3.7-2b'; + select f1 from t1 order by f1; +# Cleanup + --disable_warnings + drop trigger trg4b_2; + disconnect no_privs_424b; + disconnect yes_privs_424b; + --enable_warnings + +# --- 3.5.3.7c - Privs set on a table level +let $message= Testcase 3.5.3.7c; +--source include/show_msg.inc + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; + grant TRIGGER 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; + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant TRIGGER on *.* to test_yesprivs@localhost; + grant UPDATE on priv_db.t1 to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs_424c,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs_424c,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + + connection no_privs_424c; + show grants; + use priv_db; + +let $message= Trigger create disabled - should fail - Bug 8884; +--source include/show_msg.inc +# --error 1227 +# create trigger trg4c_1 before INSERT on t1 for each row +# set new.f1 = 'trig 3.5.3.7-1c'; + + connection default; + insert into t1 (f1) values ('insert 3.5.3.7-1c'); + select f1 from t1 order by f1; + --error 0, 1360 + drop trigger trg4c_1; + + connection yes_privs_424c; + show grants; + use priv_db; + create trigger trg4c_2 before INSERT on t1 for each row + set new.f1 = 'trig 3.5.3.7-2c'; + + connection default; + + insert into t1 (f1) values ('insert 3.5.3.7-2c'); + select f1 from t1 order by f1; + +# Cleanup + --disable_warnings + drop trigger trg4c_2; + disconnect no_privs_424c; + disconnect yes_privs_424c; + --enable_warnings + +# --- 3.5.3.7d - Privs set on a column level +--disable_query_log +let $message= Testcase 3.5.3.7d:; +--enable_query_log +--source include/show_msg.inc + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; + grant TRIGGER on *.* to test_noprivs@localhost; +# There is no ALL privs on the column level + grant SELECT (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost; + show grants for test_noprivs; + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant TRIGGER on *.* to test_yesprivs@localhost; + grant UPDATE (f1) on priv_db.t1 to test_yesprivs@localhost; + show grants for test_noprivs; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs_424d,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs_424d,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + + connection no_privs_424d; + show grants; + use priv_db; +let $message= Trigger create disabled - should fail - Bug 8884; +--source include/show_msg.inc +# --error 1227 +# create trigger trg4d_1 before INSERT on t1 for each row +# set new.f1 = 'trig 3.5.3.7-1d'; + + connection default; + insert into t1 (f1) values ('insert 3.5.3.7-1d'); + select f1 from t1 order by f1; + --error 0, 1360 + drop trigger trg4d_1; + + connection yes_privs_424d; + show grants; + use priv_db; + create trigger trg4d_2 before INSERT on t1 for each row + set new.f1 = 'trig 3.5.3.7-2d'; + + connection default; + + insert into t1 (f1) values ('insert 3.5.3.7-2d'); + select f1 from t1 order by f1; + +# Cleanup + --disable_warnings + drop trigger trg4d_2; + disconnect no_privs_424d; + disconnect yes_privs_424d; + --enable_warnings + +#Section 3.5.3.8 +# Test case: Ensure that use of the construct "SET <target> = NEW. <Column name>" fails +# at CREATE TRIGGER time, if the current user does not have the SELECT privilege +# on the column specified. + +# --- 3.5.3.8a - Privs set on a global level +let $message= Testcase 3.5.3.8a:; +--source include/show_msg.inc + + 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; + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant TRIGGER, SELECT on *.* to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs_425a,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs_425a,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + + connection no_privs_425a; + select current_user; + use priv_db; + show grants; + +let $message= Trigger create disabled - should fail - Bug 8887; +--source include/show_msg.inc +# --error 1227 +# create trigger trg5a_1 before INSERT on t1 for each row +# set @test_var = new.f1; + + connection default; + set @test_var = 'before trig 3.5.3.8-1a'; + select @test_var; + insert into t1 (f1) values ('insert 3.5.3.8-1a'); + select @test_var; + --error 0, 1360 + drop trigger trg5a_1; + + connection yes_privs_425a; + use priv_db; + select current_user; + show grants; + create trigger trg5a_2 before INSERT on t1 for each row + set @test_var= new.f1; + + connection default; + set @test_var= 'before trig 3.5.3.8-2a'; + select @test_var; + + insert into t1 (f1) values ('insert 3.5.3.8-2a'); + select @test_var; + +# Cleanup + --disable_warnings + drop trigger trg5a_2; + disconnect no_privs_425a; + disconnect yes_privs_425a; + --enable_warnings + +# --- 3.5.3.8b - Privs set on a database level +let $message= Testcase: 3.5.3.8b; +--source include/show_msg.inc + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; + grant TRIGGER 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; + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant TRIGGER on *.* to test_yesprivs@localhost; + grant SELECT on priv_db.* to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs_425b,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs_425b,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + + connection no_privs_425b; + show grants; + use priv_db; + +let $message= Trigger create disabled - should fail - Bug 8887; +--source include/show_msg.inc +# --error 1227 +# create trigger trg5b_1 before UPDATE on t1 for each row +# set @test_var= new.f1; + + connection default; + set @test_var= 'before trig 3.5.3.8-1b'; + insert into t1 (f1) values ('insert 3.5.3.8-1b'); + select @test_var; + update t1 set f1= 'update 3.5.3.8-1b' where f1 = 'insert 3.5.3.8-1b'; + select @test_var; + --error 0, 1360 + drop trigger trg5b_1; + + connection yes_privs_425b; + show grants; + use priv_db; + create trigger trg5b_2 before UPDATE on t1 for each row + set @test_var= new.f1; + + connection default; + set @test_var= 'before trig 3.5.3.8-2b'; + insert into t1 (f1) values ('insert 3.5.3.8-2b'); + select @test_var; + + update t1 set f1= 'update 3.5.3.8-2b' where f1 = 'insert 3.5.3.8-2b'; + select @test_var; +# Cleanup + --disable_warnings + drop trigger trg5b_2; + disconnect no_privs_425b; + disconnect yes_privs_425b; + --enable_warnings + +# --- 3.5.3.8c - Privs set on a table level +let $message= Testcase 3.5.3.8c:; +--source include/show_msg.inc + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; + grant TRIGGER 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; + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant TRIGGER on *.* to test_yesprivs@localhost; + grant SELECT on priv_db.t1 to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs_425c,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs_425c,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + + connection no_privs_425c; + show grants; + use priv_db; + +let $message= Trigger create disabled - should fail - Bug 8887; +--source include/show_msg.inc +# --error 1227 +# create trigger trg5c_1 before INSERT on t1 for each row +# set @test_var= new.f1; + + connection default; + set @test_var= 'before trig 3.5.3.8-1c'; + insert into t1 (f1) values ('insert 3.5.3.8-1c'); + select @test_var; + --error 0, 1360 + drop trigger trg5c_1; + + connection yes_privs_425c; + show grants; + use priv_db; + create trigger trg5c_2 before INSERT on t1 for each row + set @test_var= new.f1; + + connection default; + set @test_var='before trig 3.5.3.8-2c'; + + insert into t1 (f1) values ('insert 3.5.3.8-2c'); + select @test_var; +# Cleanup + --disable_warnings + drop trigger trg5c_2; + disconnect no_privs_425c; + disconnect yes_privs_425c; + --enable_warnings + +# --- 3.5.3.8d - Privs set on a column level +let $message=Testcase: 3.5.3.8d:; +--source include/show_msg.inc + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; + grant TRIGGER on *.* to test_noprivs@localhost; +# There is no ALL prov on the column level + grant UPDATE (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost; + show grants for test_noprivs@localhost; + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant TRIGGER on *.* to test_yesprivs@localhost; + grant SELECT (f1) on priv_db.t1 to test_yesprivs@localhost; + show grants for test_noprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs_425d,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs_425d,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + + connection no_privs_425d; + show grants; + use priv_db; +let $message= Trigger create disabled - should fail - Bug 8887; +--source include/show_msg.inc +# --error 1227 +# create trigger trg5d_1 before INSERT on t1 for each row +# set @test_var= new.f1; + + connection default; + set @test_var='before trig 3.5.3.8-1d'; + insert into t1 (f1) values ('insert 3.5.3.8-1d'); + select @test_var; + --error 0, 1360 + drop trigger trg5d_1; + + connection yes_privs_425d; + show grants; + use priv_db; + create trigger trg5d_2 before INSERT on t1 for each row + set @test_var= new.f1; + + connection default; + set @test_var='before trig 3.5.3.8-2d'; + + insert into t1 (f1) values ('insert 3.5.3.8-2d'); + select @test_var; + +# Cleanup 3.5.3.8 + --disable_warnings + drop trigger trg5d_2; + --enable_warnings + +# --- 3.5.3.x - additional tests following the fix to bug 5861 / WL 2818 +# to test for trigger definer privs in the case of trigger +# actions (insert/update/delete/select) performed on other +# tables. +let $message=Testcase: 3.5.3.x:; +--source include/show_msg.inc + + use priv_db; + --disable_warnings + drop table if exists t1; + drop table if exists t2; + --enable_warnings + + eval create table t1 (f1 int) engine= $engine_type; + eval create table t2 (f2 int) engine= $engine_type; + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant TRIGGER on *.* to test_yesprivs@localhost; + grant SELECT, UPDATE on priv_db.t1 to test_yesprivs@localhost; + grant SELECT on priv_db.t2 to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_353x,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + + connection yes_353x; + select current_user; + use priv_db; + + create trigger trg1 before insert on t1 for each row + insert into t2 values (new.f1); + + connection default; + use priv_db; + insert into t1 (f1) values (4); + revoke SELECT on priv_db.t2 from test_yesprivs@localhost; + grant INSERT on priv_db.t2 to test_yesprivs@localhost; + insert into t1 (f1) values (4); + select f1 from t1 order by f1; + select f2 from t2 order by f2; + + connection yes_353x; + use priv_db; + drop trigger trg1; + + create trigger trg2 before insert on t1 for each row + update t2 set f2=new.f1-1; + + connection default; + use priv_db; + insert into t1 (f1) values (2); + revoke INSERT on priv_db.t2 from test_yesprivs@localhost; + grant UPDATE on priv_db.t2 to test_yesprivs@localhost; + insert into t1 (f1) values (2); + select f1 from t1 order by f1; + select f2 from t2 order by f2; + + connection yes_353x; + use priv_db; + drop trigger trg2; + + create trigger trg3 before insert on t1 for each row + select f2 into @aaa from t2 where f2=new.f1; + + connection default; + use priv_db; + insert into t1 (f1) values (1); + revoke UPDATE on priv_db.t2 from test_yesprivs@localhost; + grant SELECT on priv_db.t2 to test_yesprivs@localhost; + insert into t1 (f1) values (1); + select f1 from t1 order by f1; + select f2 from t2 order by f2; + select @aaa; + + connection yes_353x; + use priv_db; + drop trigger trg3; + + create trigger trg4 before insert on t1 for each row + delete from t2; + + connection default; + use priv_db; + insert into t1 (f1) values (1); + revoke SELECT on priv_db.t2 from test_yesprivs@localhost; + grant DELETE on priv_db.t2 to test_yesprivs@localhost; + insert into t1 (f1) values (1); + select f1 from t1 order by f1; + select f2 from t2 order by f2; + + + +# Cleanup 3.5.3 + --disable_warnings + drop database if exists priv_db; + drop user test_yesprivs@localhost; + drop user test_noprivs@localhost; + drop user test_noprivs; + --enable_warnings + diff --git a/mysql-test/suite/funcs_1/triggers/triggers_03e_columns.inc b/mysql-test/suite/funcs_1/triggers/triggers_03e_columns.inc new file mode 100644 index 00000000000..c67291f605b --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/triggers_03e_columns.inc @@ -0,0 +1,250 @@ +#====================================================================== +# +# Trigger Tests +# test cases for TRIGGER privilege on db, table and column level +#====================================================================== + +--disable_abort_on_error + +######################################################### +################ Section 3.5.3 ########################## +# Check for column privileges of Triggers # +######################################################### + +# General setup to be used in all testcases +let $message= ####### Testcase for column privileges of triggers: #######; +--source include/show_msg.inc + + --disable_warnings + drop database if exists priv_db; + drop database if exists no_priv_db; + --enable_warnings + create database priv_db; + use priv_db; + eval create table t1 (f1 char(20)) engine= $engine_type; + eval create table t2 (f1 char(20)) engine= $engine_type; + + create User test_yesprivs@localhost; + set password for test_yesprivs@localhost = password('PWD'); + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant TRIGGER on priv_db.* to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + create User test_noprivs@localhost; + set password for test_noprivs@localhost = password('PWD'); + revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; + grant SELECT,UPDATE on priv_db.* to test_noprivs@localhost; + show grants for test_noprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + +# grant TRIGGER and UPDATE on column -> succeed + +let $message= update only on column:; +--source include/show_msg.inc + + connection default; + select current_user; + grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t1 + to test_yesprivs@localhost; + grant SELECT(f1),INSERT,UPDATE(f1) on priv_db.t2 + to test_yesprivs@localhost; + + connection yes_privs; + select current_user; + use priv_db; + insert into t1 (f1) values ('insert1-yes'); + insert into t2 (f1) values ('insert1-yes'); + create trigger trg1_1 before UPDATE on t1 for each row + set new.f1 = 'trig 1_1-yes'; + create trigger trg2_1 before UPDATE on t2 for each row + set new.f1 = 'trig 2_1-yes'; + + connection no_privs; + select current_user; + use priv_db; + select f1 from t1 order by f1; + update t1 set f1 = 'update1_no' + where f1 like '%insert%'; + select f1 from t1 order by f1; + select f1 from t2 order by f1; + update t2 set f1 = 'update1_no' + where f1 like '%insert%'; + select f1 from t2 order by f1; + + connection default; + select current_user; + revoke UPDATE on priv_db.* + from test_yesprivs@localhost; + revoke UPDATE(f1) on priv_db.t2 + from test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + connection yes_privs; + select current_user; + use priv_db; + insert into t1 (f1) values ('insert2-yes'); + insert into t2 (f1) values ('insert2-yes'); + + connection no_privs; + select current_user; + use priv_db; + update t1 set f1 = 'update2_no' + where f1 like '%insert%'; + --error 1143 + update t2 set f1 = 'update2_no' + where f1 like '%insert%'; + update t1 set f1 = 'update3_no' + where f1 like '%insert%'; + --error 1143 + update t2 set f1 = 'update3_no' + where f1 like '%insert%'; + select f1 from t1 order by f1; + select f1 from t2 order by f1; + +# check with three columns +let $message= check if access only on one of three columns; +--source include/show_msg.inc + + connection default; + select current_user; + alter table priv_db.t1 add f2 char(20), add f3 int; + revoke TRIGGER on priv_db.* from test_yesprivs@localhost; + grant TRIGGER,SELECT on priv_db.t1 to test_yesprivs@localhost; + grant UPDATE on priv_db.t2 to test_yesprivs@localhost; + + connection yes_privs; + select current_user; + use priv_db; + insert into t1 values ('insert2-yes','insert2-yes',1); + insert into t1 values ('insert3-yes','insert3-yes',2); + select * from t1 order by f1; + + connection no_privs; + select current_user; + use priv_db; + update t1 set f1 = 'update4-no', + f2 = 'update4-yes', + f3 = f3*10 + where f2 like '%yes'; + select * from t1 order by f1,f2,f3; + + connection yes_privs; + select current_user; + create trigger trg1_2 after UPDATE on t1 for each row + set @f2 = 'trig 1_2-yes'; + + connection no_privs; + select current_user; + update t1 set f1 = 'update5-yes', + f2 = 'update5-yes' + where f2 like '%yes'; + select * from t1 order by f1,f2,f3; + select @f2; + + update t1 set f1 = 'update6_no' + where f1 like '%insert%'; + --error 1142 + update t2 set f1 = 'update6_no' + where f1 like '%insert%'; + update t1 set f1 = 'update7_no' + where f1 like '%insert%'; + --error 1142 + update t2 set f1 = 'update7_no' + where f1 like '%insert%'; + select f1 from t1 order by f1; + select f1 from t2 order by f1; + +# check with three columns + + +# check if update is rejected without trigger privilege + +let $message= check if rejected without trigger privilege:; +--source include/show_msg.inc + + connection default; + select current_user; + revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost; + + connection no_privs; + select current_user; + --error 1142 + update t1 set f1 = 'update8-no', + f2 = 'update8-no' + where f2 like '%yes'; + select * from t1 order by f1,f2,f3; + select @f2; + +# check trigger, but not update privilege on column + +let $message= check trigger, but not update privilege on column:; +--source include/show_msg.inc + + connection default; + select current_user; + revoke UPDATE(f1) on priv_db.t1 from test_yesprivs@localhost; + grant TRIGGER,UPDATE(f2),UPDATE(f3) on priv_db.t1 + to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + connection yes_privs; + select current_user; + use priv_db; + drop trigger trg1_1; + create trigger trg1_3 before UPDATE on t1 for each row + set new.f1 = 'trig 1_3-yes'; + + connection no_privs; + select current_user; + use priv_db; + --error 1143 + update t1 set f1 = 'update9-no', + f2 = 'update9-no' + where f2 like '%yes'; + select * from t1 order by f1,f2,f3; + +# trigger is involved (table privilege) ->fail + --error 1143 + update t1 set f3= f3+1; + select f3 from t1 order by f3; + + connection default; + select current_user; + revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost; + grant UPDATE(f1),UPDATE(f2),UPDATE(f3) on priv_db.t1 + to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + +# trigger is involved (table privilege) ->fail + connection no_privs; + select current_user; + use priv_db; + --error 1142 + update t1 set f3= f3+1; + select f3 from t1 order by f3; + +let $message= ##### trigger privilege on column level? #######; +--source include/show_msg.inc + --error 1064 + grant TRIGGER(f1) on priv_db.t1 to test_yesprivs@localhost; + +# Cleanup table level + --disable_warnings + disconnect yes_privs; + disconnect no_privs; + + connection default; + select current_user; + + +# general Cleanup + drop database if exists priv_db; + drop user test_yesprivs@localhost; + drop user test_noprivs@localhost; + --enable_warnings + diff --git a/mysql-test/suite/funcs_1/triggers/triggers_03e_db_level.inc b/mysql-test/suite/funcs_1/triggers/triggers_03e_db_level.inc new file mode 100644 index 00000000000..c2679a55ef7 --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/triggers_03e_db_level.inc @@ -0,0 +1,221 @@ +#====================================================================== +# +# Trigger Tests +# test cases for TRIGGER privilege on db, table and column level +#====================================================================== + +--disable_abort_on_error + +############################################ +################ Section 3.5.3 ############# +# basic tests for the db level of Triggers # +############################################ + +# General setup to be used in all testcases +let $message= Testcase for db level:; +--source include/show_msg.inc + + --disable_warnings + drop database if exists priv_db; + drop database if exists no_priv_db; + --enable_warnings + create database priv_db; + create database no_priv_db; + use priv_db; + eval create table t1 (f1 char(20)) engine= $engine_type; + + create User test_yesprivs@localhost; + set password for test_yesprivs@localhost = password('PWD'); + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant select on priv_db.* to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + create User test_noprivs@localhost; + set password for test_noprivs@localhost = password('PWD'); + revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; + grant select,insert on priv_db.* to test_noprivs@localhost; + show grants for test_noprivs@localhost; + +# no trigger privilege->create trigger must fail: + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); +let $message= no trigger privilege on db level for create:; +--source include/show_msg.inc + use priv_db; + --error 1142 + create trigger trg1_1 before INSERT on t1 for each row + set new.f1 = 'trig 1_1-no'; + +# user with minimum privs on t1->no trigger executed; + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + use priv_db; + insert into t1 (f1) values ('insert-yes'); + select f1 from t1 order by f1; + + connection default; + select current_user; + grant TRIGGER on priv_db.* to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + +# user got trigger privilege->create successful: +let $message= trigger privilege on db level for create:; +--source include/show_msg.inc + connection yes_privs; + select current_user; + use priv_db; + create trigger trg1_2 before INSERT on t1 for each row + set new.f1 = 'trig 1_2-yes'; + +# user with minimum privs on t1->fail,as trigger definer no update priv:; + connection no_privs; + select current_user; + use priv_db; + insert into t1 (f1) values ('insert-yes'); + select f1 from t1 order by f1; + + connection default; + select current_user; + grant UPDATE on priv_db.* to test_yesprivs@localhost; +# succeed,as trigger definer has update privilege: +# new privilege take effect after 'use db': + use priv_db; + insert into t1 (f1) values ('insert-no'); + select f1 from t1 order by f1; + +# succeed: + connection no_privs; + select current_user; + use priv_db; + insert into t1 (f1) values ('insert-yes'); + select f1 from t1 order by f1; + + connection default; + select current_user; + revoke TRIGGER on priv_db.* from test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + +# drop must fail, as no trigger privilege: +let $message= no trigger privilege on db level for drop:; +--source include/show_msg.inc + connection yes_privs; + select current_user; + use priv_db; + --error 1142 + drop trigger trg1_2; + + connection no_privs; + select current_user; + use priv_db; +# no trigger privilege at activation time: +let $message= no trigger privilege at activation time:; +--source include/show_msg.inc + --error 1142 + insert into t1 (f1) values ('insert-yes'); + select f1 from t1 order by f1; + +let $message= trigger privilege at activation time:; +--source include/show_msg.inc + connection default; + select current_user; + grant TRIGGER on priv_db.* to test_yesprivs@localhost; + +# succeed, as trigger privilege at activation time: + connection no_privs; + select current_user; + use priv_db; + insert into t1 (f1) values ('insert-no'); + select f1 from t1 order by f1; +# drop must fail, as no 'use db' executed: +let $message= trigger privilege on db level for drop:; +--source include/show_msg.inc + connection yes_privs; + select current_user; + show grants for test_yesprivs@localhost; + --error 1142 + drop trigger trg1_2; + +# succeed +let $message= takes effect after use priv_db:; +--source include/show_msg.inc + use priv_db; + drop trigger trg1_2; + + connection default; + select current_user; + use priv_db; + insert into t1 (f1) values ('insert-yes'); + select f1 from t1 order by f1; + +let $message= switch to db without having trigger priv for it:; +--source include/show_msg.inc + use no_priv_db; + eval create table t1 (f1 char(20)) engine= $engine_type; +# Adding the minimal priv to be able to set to the db + grant SELECT,UPDATE on no_priv_db.* to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + +# trigger privilege is hold over changes between priv and no priv db: +let $message= use db with trigger privilege on db level and without...:; +--source include/show_msg.inc + connection yes_privs; + select current_user; + use no_priv_db; + --error 1142 + create trigger trg1_3 before INSERT on t1 for each row + set new.f1 = 'trig 1_3-no'; + use priv_db; + create trigger trg1_3 before INSERT on t1 for each row + set new.f1 = 'trig 1_3-yes'; + use no_priv_db; + --error 1142 + create trigger trg1_4 before UPDATE on t1 for each row + set new.f1 = 'trig 1_4-no'; + use priv_db; + create trigger trg1_4 before UPDATE on t1 for each row + set new.f1 = 'trig 1_4-yes'; + + connection no_privs; + select current_user; + use no_priv_db; + insert into t1 (f1) values ('insert-yes'); + select f1 from t1 order by f1; + use priv_db; + insert into t1 (f1) values ('insert-no'); + select f1 from t1 order by f1; + --disable_warnings + disconnect no_privs; + --enable warnings + + connection yes_privs; + select current_user; + use no_priv_db; + --error 1360 + drop trigger trg1_3; + use priv_db; + drop trigger trg1_3; + use no_priv_db; + --error 1360 + drop trigger trg1_4; + use priv_db; + drop trigger trg1_4; + + +# Cleanup db level + --disable_warnings + disconnect yes_privs; + + connection default; + select current_user; + drop table priv_db.t1; + drop table no_priv_db.t1; + --enable_warnings + +# general Cleanup + --disable_warnings + drop database if exists priv_db; + drop database if exists no_priv_db; + drop user test_yesprivs@localhost; + drop user test_noprivs@localhost; + --enable_warnings + diff --git a/mysql-test/suite/funcs_1/triggers/triggers_03e_db_table_mix.inc b/mysql-test/suite/funcs_1/triggers/triggers_03e_db_table_mix.inc new file mode 100644 index 00000000000..653af075478 --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/triggers_03e_db_table_mix.inc @@ -0,0 +1,205 @@ +#====================================================================== +# +# Trigger Tests +# test cases for TRIGGER privilege on db, table and column level +#====================================================================== + +--disable_abort_on_error + +######################################################### +################ Section 3.5.3 ########################## +# Check for mix of db and table level of Triggers # +######################################################### + +# General setup to be used in all testcases +let $message= ####### Testcase for mix of db and table level: #######; +--source include/show_msg.inc + + --disable_warnings + drop database if exists priv1_db; + drop database if exists priv2_db; + --enable_warnings + create database priv1_db; + create database priv2_db; + use priv1_db; + eval create table t1 (f1 char(20)) engine= $engine_type; + eval create table t2 (f1 char(20)) engine= $engine_type; + use priv2_db; + eval create table t1 (f1 char(20)) engine= $engine_type; + + create User test_yesprivs@localhost; + set password for test_yesprivs@localhost = password('PWD'); + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant ALL on priv1_db.* to test_yesprivs@localhost; + grant SELECT,UPDATE on priv2_db.* to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + create User test_noprivs@localhost; + set password for test_noprivs@localhost = password('PWD'); + revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; + grant SELECT,INSERT,UPDATE on priv1_db.* to test_noprivs@localhost; + grant SELECT,INSERT on priv2_db.* to test_noprivs@localhost; + show grants for test_noprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + use priv1_db; + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + use priv1_db; + +# trigger priv on db level->create trigger for all tables successful + +let $message= trigger privilege on one db1 db level, not on db2; +--source include/show_msg.inc + connection yes_privs; + select current_user; + use priv1_db; + create trigger trg1_1 before INSERT on t1 for each row + set new.f1 = 'trig 1_1-yes'; + create trigger trg2_1 before INSERT on t2 for each row + set new.f1 = 'trig 2_1-yes'; + use priv2_db; + --error 1142 + create trigger trg1_1 before INSERT on t1 for each row + set new.f1 = 'trig1_1-yes'; + + connection no_privs; + select current_user; + insert into t1 (f1) values ('insert1_no'); + select f1 from t1 order by f1; + insert into t2 (f1) values ('insert1_no'); + select f1 from t2 order by f1; + insert into priv2_db.t1 (f1) values ('insert21-yes'); + select f1 from priv2_db.t1 order by f1; + + use priv2_db; + insert into t1 (f1) values ('insert1_yes'); + select f1 from t1 order by f1; + insert into priv1_db.t1 (f1) values ('insert11-no'); + select f1 from priv1_db.t1 order by f1; + insert into priv1_db.t2 (f1) values ('insert22-no'); + select f1 from priv1_db.t2 order by f1; + +# revoke trigger priv on table level, that doesn't exists->fail + +let $message= revoke trigger privilege on table level (not existing); +--source include/show_msg.inc + connection default; + select current_user; + use priv1_db; + --error 1147 + revoke TRIGGER on priv1_db.t1 from test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + connection yes_privs; + select current_user; + drop trigger trg1_1; + drop trigger trg2_1; + use priv1_db; + drop trigger trg1_1; + drop trigger trg2_1; + +# revoke the db level->create/drop/use trigger fail + + connection default; + select current_user; + use priv1_db; + revoke TRIGGER on priv1_db.* from test_yesprivs@localhost; + +################ Section 3.5.3 ############ +# Check for the table level of Triggers # +########################################### +let $message= no trigger privilege on table level for create:; +--source include/show_msg.inc + + connection yes_privs; + select current_user; + use priv1_db; + --error 1142 + create trigger trg1_1 before INSERT on t1 for each row + set new.f1 = 'trig 1_1-no'; + + connection default; + select current_user; + show triggers; + grant TRIGGER on priv1_db.t1 to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + +let $message= trigger privilege on table level for create:; +--source include/show_msg.inc + connection yes_privs; + select current_user; + show triggers; + create trigger trg1_2 before INSERT on t1 for each row + set new.f1 = 'trig 1_2-yes'; + --error 1142 + create trigger trg2_1 before INSERT on t2 for each row + set new.f1 = 'trig 2_1-no'; + + connection no_privs; + select current_user; +# need 'use db' to get the newest privileges + use priv1_db; + insert into t1 (f1) values ('insert2-no'); + select f1 from t1 order by f1; + insert into t2 (f1) values ('insert2-yes'); + select f1 from t2 order by f1; + insert into priv2_db.t1 (f1) values ('insert22-yes'); + select f1 from priv2_db.t1 order by f1; + + connection default; + select current_user; + grant TRIGGER on priv1_db.* to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + +# though granted on db level->create trigger fails (no use db) + + connection yes_privs; + select current_user; + --error 1142 + create trigger trg2_1 before INSERT on t2 for each row + set new.f1 = 'trig 2_1-yes'; + +# grant trigger takes effect + + use priv1_db; + create trigger trg2_1 before INSERT on t2 for each row + set new.f1 = 'trig 2_1-yes'; + + connection no_privs; + select current_user; + use priv1_db; + insert into t1 (f1) values ('insert3-no'); + select f1 from t1 order by f1; + insert into t2 (f1) values ('insert3-no'); + select f1 from t2 order by f1; + use priv2_db; + insert into priv1_db.t1 (f1) values ('insert12-no'); + select f1 from priv1_db.t1 order by f1; + insert into priv1_db.t2 (f1) values ('insert23-no'); + select f1 from priv1_db.t2 order by f1; + + disconnect no_privs; + + connection yes_privs; + select current_user; + + drop trigger trg1_2; + drop trigger trg2_1; + +# Cleanup table level + --disable_warnings + disconnect yes_privs; + + connection default; + select current_user; + + +# general Cleanup + drop database if exists priv1_db; + drop database if exists priv2_db; + drop user test_yesprivs@localhost; + drop user test_noprivs@localhost; + --enable_warnings + diff --git a/mysql-test/suite/funcs_1/triggers/triggers_03e_definer.inc b/mysql-test/suite/funcs_1/triggers/triggers_03e_definer.inc new file mode 100644 index 00000000000..59ba321c2e2 --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/triggers_03e_definer.inc @@ -0,0 +1,117 @@ +#====================================================================== +# +# Trigger Tests +# test cases for TRIGGER privilege on db, table and column level +#====================================================================== + +--disable_abort_on_error + +########################################### +################ Section 3.5.3 ############ +# Check for the definer of Triggers # +########################################### + +# General setup to be used in all testcases +let $message= ######### Testcase for definer: ########; +--source include/show_msg.inc + + --disable_warnings + drop database if exists priv_db; + --enable_warnings + create database priv_db; + use priv_db; + eval create table t1 (f1 char(20)) engine= $engine_type; + + create User test_yesprivs@localhost; + set password for test_yesprivs@localhost = password('PWD'); + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + +# create trigger with not existing definer shall deliver a warning: + connection default; + select current_user; +# --warning 1449 + create definer=not_ex_user@localhost trigger trg1_0 + before INSERT on t1 for each row + set new.f1 = 'trig 1_0-yes'; + drop trigger trg1_0; +# create trigger with definer test_yesprivs@localhost->succeed: + create definer=test_yesprivs@localhost trigger trg1_0 + before INSERT on t1 for each row + set new.f1 = 'trig 1_0-yes'; + grant select, insert, update + on priv_db.t1 to test_yesprivs@localhost; + + connection yes_privs; + select current_user; + use priv_db; +# user hasn't trigger privilege->fail: + --error 1142 + insert into t1 (f1) values ('insert-no'); + select f1 from t1 order by f1; + --error 1142 + drop trigger trg1_0; + + connection default; select current_user; grant select, insert, update ,trigger + on priv_db.t1 to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + connection yes_privs; + select current_user; +# user now has trigger privilege->succeed: + insert into t1 (f1) values ('insert-no'); + select f1 from t1 order by f1; + drop trigger trg1_0; +# user has not super privilege->fail: + --error 1227 + create definer=not_ex_user@localhost trigger trg1_0 + before INSERT on t1 for each row + set new.f1 = 'trig 1_0-yes'; +# shall always succeed: + create definer=current_user trigger trg1_1 + before INSERT on t1 for each row + set new.f1 = 'trig 1_1-yes'; + insert into t1 (f1) values ('insert-no'); + select f1 from t1 order by f1; +# shall always succeed: + create definer=test_yesprivs@localhost trigger trg1_2 + before UPDATE on t1 for each row + set new.f1 = 'trig 1_2-yes'; + update t1 set f1 = 'update-yes' where f1 like '%trig%'; + select f1 from t1 order by f1; + + connection default; + select current_user; + grant trigger on priv_db.* to test_yesprivs@localhost + with grant option; + +# user has not super privilege->fail: + connection yes_privs; + select current_user; + show grants; + --error 1227 + create definer=not_ex_user@localhost trigger trg1_3 + after UPDATE on t1 for each row + set @var1 = 'trig 1_3-yes'; + + connection default; + select current_user; + +# Cleanup prepare + --disable_warnings + disconnect yes_privs; + + connection default; + select current_user; + --enable_warnings + + +# general Cleanup + --disable_warnings + drop database if exists priv_db; + drop user test_yesprivs@localhost; + --enable_warnings + diff --git a/mysql-test/suite/funcs_1/triggers/triggers_03e_global_db_mix.inc b/mysql-test/suite/funcs_1/triggers/triggers_03e_global_db_mix.inc new file mode 100644 index 00000000000..a1e1f0e494c --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/triggers_03e_global_db_mix.inc @@ -0,0 +1,219 @@ +#====================================================================== +# +# Trigger Tests +# test cases for TRIGGER privilege on db, table and column level +#====================================================================== + +--disable_abort_on_error + +######################################################### +################ Section 3.5.3 ########################## +# Check for mix of user and db level of Triggers # +######################################################### + +# General setup to be used in all testcases +let $message= #### Testcase for mix of user(global) and db level: ####; +--source include/show_msg.inc + + --disable_warnings + drop database if exists priv_db; + drop database if exists no_priv_db; + --enable_warnings + create database priv_db; + create database no_priv_db; + use priv_db; + eval create table t1 (f1 char(20)) engine= $engine_type; + use no_priv_db; + eval create table t1 (f1 char(20)) engine= $engine_type; + + create User test_yesprivs@localhost; + set password for test_yesprivs@localhost = password('PWD'); + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant ALL on *.* to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + create User test_noprivs@localhost; + set password for test_noprivs@localhost = password('PWD'); + revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; + grant SELECT,INSERT on *.* to test_noprivs@localhost; + show grants for test_noprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + + connection yes_privs; + select current_user; +let $message= trigger privilege on user level for create:; +--source include/show_msg.inc + use priv_db; + + create trigger trg1_1 before INSERT on t1 for each row + set new.f1 = 'trig 1_1-yes'; + insert into t1 (f1) values ('insert-no'); + select f1 from t1 order by f1; + + use no_priv_db; + create trigger priv_db.trg1_5 before UPDATE on priv_db.t1 + for each row + set new.f1 = 'trig 1_5-yes'; + insert into priv_db.t1 (f1) values ('insert-no'); + select f1 from priv_db.t1 order by f1; + drop trigger priv_db.trg1_5; + + connection no_privs; + select current_user; + use priv_db; + insert into t1 (f1) values ('insert-no'); + select f1 from t1 order by f1; + + connection default; + select current_user; + use priv_db; + insert into t1 (f1) values ('insert-no'); + select f1 from t1 order by f1; + + revoke TRIGGER on *.* from test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + +# change of privilege only active after reconnecting the session + + --disable_warnings + disconnect yes_privs; + --enable_warnings + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + select current_user; + use priv_db; + show triggers; + select * from information_schema.triggers; + --error 1142 + drop trigger trg1_1; + + connection default; + select current_user; + show grants; + drop trigger trg1_1; + use priv_db; + +################ Section 3.5.3 ############ +# Check for the db level of Triggers # +########################################### +let $message= no trigger privilege on db level for create:; +--source include/show_msg.inc + + connection yes_privs; + select current_user; + --error 1142 + create trigger trg1_1 before INSERT on t1 for each row + set new.f1 = 'trig 1_1-no'; + + connection no_privs; + select current_user; + use priv_db; + insert into t1 (f1) values ('insert-yes'); + select f1 from t1 order by f1; + + connection default; + select current_user; + grant TRIGGER on priv_db.* to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + +let $message= trigger privilege on db level for create:; +--source include/show_msg.inc + connection yes_privs; + select current_user; + +# active after 'use db' + + use priv_db; + create trigger trg1_2 before INSERT on t1 for each row + set new.f1 = 'trig 1_2-yes'; + --error 1142 + create trigger no_priv_db.trg1_9 before insert on no_priv_db.t1 + for each row + set new.f1 = 'trig 1_9-yes'; + use no_priv_db; + --error 1142 + create trigger trg1_2 before INSERT on t1 for each row + set new.f1 = 'trig 1_2-no'; + create trigger priv_db.trg1_9 before UPDATE on priv_db.t1 + for each row + set new.f1 = 'trig 1_9-yes'; + + connection no_privs; + select current_user; + use priv_db; + insert into t1 (f1) values ('insert-yes'); + select f1 from t1 order by f1; + use no_priv_db; + insert into t1 (f1) values ('insert-yes'); + select f1 from t1 order by f1; + --error 1142 + drop trigger priv_db.trg1_9; + + connection default; + select current_user; + drop trigger priv_db.trg1_9; + revoke TRIGGER on priv_db.* from test_yesprivs@localhost; + use priv_db; + --error 1142 + insert into t1 (f1) values ('insert-yes'); + select f1 from t1 order by f1; + grant TRIGGER on *.* to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + connection yes_privs; + select current_user; + use no_priv_db; + --error 1142 + create trigger trg1_2 before INSERT on t1 for each row + set new.f1 = 'trig 1_2-no'; + + connection no_privs; + select current_user; + use priv_db; + insert into t1 (f1) values ('insert-no'); + select f1 from t1 order by f1; + use no_priv_db; + insert into t1 (f1) values ('insert-yes'); + select f1 from t1 order by f1; + + --disable_warnings + disconnect yes_privs; + --enable_warnings + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + select current_user; + use no_priv_db; + create trigger trg1_2 before INSERT on t1 for each row + set new.f1 = 'trig 1_2-yes'; + --disable_warnings + disconnect yes_privs; + --enable_warnings + + connection no_privs; + select current_user; + use priv_db; + insert into t1 (f1) values ('insert-no'); + select f1 from t1 order by f1; + use no_priv_db; + insert into t1 (f1) values ('insert-no'); + select f1 from t1 order by f1; + --disable_warnings + disconnect no_privs; + +# Cleanup table level + connection default; + select current_user; + +# general Cleanup + drop database if exists priv_db; + drop database if exists no_priv_db; + drop database if exists h1; + drop user test_yesprivs@localhost; + drop user test_noprivs@localhost; + --enable_warnings + diff --git a/mysql-test/suite/funcs_1/triggers/triggers_03e_prepare.inc b/mysql-test/suite/funcs_1/triggers/triggers_03e_prepare.inc new file mode 100644 index 00000000000..dbb58194bb0 --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/triggers_03e_prepare.inc @@ -0,0 +1,179 @@ +#====================================================================== +# +# Trigger Tests +# test cases for TRIGGER privilege on db, table and column level +# These tests ensure that at activation time (execute statement) +# the user must have trigger privilege. +#====================================================================== + +--disable_abort_on_error + +########################################################### +################ Section 3.5.3 ############################ +# Check for the trigger privilege in case of prepare/exec # +########################################################### + +# General setup to be used in all testcases +let $message= #### Testcase for trigger privilege on execution time ########; +--source include/show_msg.inc + + --disable_warnings + drop database if exists priv_db; + --enable_warnings + create database priv_db; + use priv_db; + eval create table t1 (f1 char(20)) engine= $engine_type; + + create User test_yesprivs@localhost; + set password for test_yesprivs@localhost = password('PWD'); + create User test_useprivs@localhost; + set password for test_useprivs@localhost = password('PWD'); + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + revoke ALL PRIVILEGES, GRANT OPTION FROM test_useprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + + connection default; + select current_user; + show triggers; + grant select, insert, update ,trigger + on priv_db.t1 to test_yesprivs@localhost + with grant option; + grant select + on priv_db.t1 to test_useprivs@localhost; + show grants for test_yesprivs@localhost; + + connection yes_privs; + select current_user; + use priv_db; + create trigger trg1_1 before INSERT on t1 for each row + set new.f1 = 'trig 1_1-yes'; + grant insert on t1 to test_useprivs@localhost; + prepare ins1 from 'insert into t1 (f1) values (''insert1-no'')'; + execute ins1; + select f1 from t1 order by f1; + prepare ins1 from 'insert into t1 (f1) values (''insert2-no'')'; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (use_privs,localhost,test_useprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + select current_user; + use priv_db; + prepare ins1 from 'insert into t1 (f1) values (''insert3-no'')'; + execute ins1; + select f1 from t1 order by f1; + + connection default; + select current_user; + revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + connection yes_privs; + select current_user; + --error 1142 + execute ins1; + select f1 from t1 order by f1; + prepare ins1 from 'insert into t1 (f1) values (''insert4-no'')'; + + connection use_privs; + select current_user; + prepare ins1 from 'insert into t1 (f1) values (''insert5-no'')'; + --error 1142 + execute ins1; + select f1 from t1 order by f1; + + connection default; + select current_user; + grant TRIGGER on priv_db.t1 to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + connection yes_privs; + select current_user; + execute ins1; + select f1 from t1 order by f1; + prepare ins1 from 'insert into t1 (f1) values (''insert6-no'')'; + + connection use_privs; + select current_user; + execute ins1; + select f1 from t1 order by f1; + prepare ins1 from 'insert into t1 (f1) values (''insert7-no'')'; + + connection default; + select current_user; + revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + connection yes_privs; + select current_user; + --error 1142 + execute ins1; + select f1 from t1 order by f1; + + connection use_privs; + select current_user; + --error 1142 + execute ins1; + select f1 from t1 order by f1; + + connection default; + select current_user; + grant TRIGGER on priv_db.t1 to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + connection yes_privs; + select current_user; + execute ins1; + select f1 from t1 order by f1; + + connection use_privs; + select current_user; + execute ins1; + select f1 from t1 order by f1; + + connection default; + select current_user; + revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + connection yes_privs; + select current_user; + execute ins1; + select f1 from t1 order by f1; + deallocate prepare ins1; + + connection use_privs; + select current_user; + execute ins1; + select f1 from t1 order by f1; + deallocate prepare ins1; + + connection default; + select current_user; + grant TRIGGER on priv_db.t1 to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + connection yes_privs; + select current_user; + drop trigger trg1_1; + + connection default; + select current_user; + +# Cleanup prepare + --disable_warnings + disconnect yes_privs; + + connection default; + select current_user; + --enable_warnings + + +# general Cleanup + --disable_warnings + drop database if exists priv_db; + drop user test_yesprivs@localhost; + drop user test_useprivs@localhost; + --enable_warnings + diff --git a/mysql-test/suite/funcs_1/triggers/triggers_03e_table_level.inc b/mysql-test/suite/funcs_1/triggers/triggers_03e_table_level.inc new file mode 100644 index 00000000000..456e51a2ae0 --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/triggers_03e_table_level.inc @@ -0,0 +1,219 @@ +#====================================================================== +# +# Trigger Tests +# test cases for TRIGGER privilege on db, table and column level +#====================================================================== + +--disable_abort_on_error + +########################################### +################ Section 3.5.3 ############ +# Check for the db level of Triggers # +########################################### + +# General setup to be used in all testcases +let $message= ######### Testcase for table level: ########; +--source include/show_msg.inc + + --disable_warnings + drop database if exists priv_db; + --enable_warnings + create database priv_db; + use priv_db; + eval create table t1 (f1 char(20)) engine= $engine_type; + + create User test_yesprivs@localhost; + set password for test_yesprivs@localhost = password('PWD'); + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + + create User test_noprivs@localhost; + set password for test_noprivs@localhost = password('PWD'); + revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; + + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + +################ Section 3.5.3 ############ +# Check for the table level of Triggers # +########################################### + +# user has no trigger privilege->create trigger fail + +let $message= no trigger privilege on table level for create:; +--source include/show_msg.inc + connection default; + select current_user; + show triggers; + grant select, insert, update on priv_db.t1 to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + grant select, update, insert on priv_db.t1 to test_noprivs@localhost; + show grants for test_noprivs@localhost; + + connection yes_privs; + select current_user; + use priv_db; + show tables; + --error 1142 + create trigger trg1_1 before INSERT on t1 for each row + set new.f1 = 'trig 1_1-no'; + +# no trigger execution, as trigger does'nt exist + + connection no_privs; + select current_user; + use priv_db; + insert into t1 (f1) values ('insert1-yes'); + select f1 from t1 order by f1; + + connection default; + select current_user; + show triggers; + show tables; + insert into t1 (f1) values ('insert2-yes'); + select f1 from t1 order by f1; + grant TRIGGER on priv_db.t1 to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + +# user got trigger privilege->create trigger successful + +let $message= trigger privilege on table level for create:; +--source include/show_msg.inc + connection yes_privs; + select current_user; + show triggers; + create trigger trg1_2 before INSERT on t1 for each row + set new.f1 = 'trig 1_2-yes'; + +# insert now executes the trigger + + connection no_privs; + select current_user; + insert into t1 (f1) values ('insert3-no'); + select f1 from t1 order by f1; + + connection default; + select current_user; + insert into t1 (f1) values ('insert4-no'); + select f1 from t1 order by f1; + revoke TRIGGER on priv_db.t1 from test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + +# revoke triggerprivilege->drop trigger fail + +let $message= no trigger privilege on table level for drop:; +--source include/show_msg.inc + connection yes_privs; + select current_user; + + --error 1142 + drop trigger trg1_2; + +# no trigger priv at activation time->insert fails + +let $message= no trigger privilege at activation time:; +--source include/show_msg.inc + connection no_privs; + select current_user; + --error 1142 + insert into t1 (f1) values ('insert5-no'); + select f1 from t1 order by f1; + + connection default; + select current_user; + grant TRIGGER on priv_db.t1 to test_yesprivs@localhost; + +# trigger privilege at activation time->insert with trigger successful + +let $message= trigger privilege at activation time:; +--source include/show_msg.inc + connection no_privs; + select current_user; + insert into t1 (f1) values ('insert6-no'); + select f1 from t1 order by f1; + +# trigger privilege->drop trigger successful +let $message= trigger privilege on table level for drop:; +--source include/show_msg.inc + connection yes_privs; + select current_user; + show grants for test_yesprivs@localhost; + drop trigger trg1_2; + +# inserts without trigger + + connection no_privs; + select current_user; + insert into t1 (f1) values ('insert7-yes'); + select f1 from t1 order by f1; + + connection default; + select current_user; + insert into t1 (f1) values ('insert8-yes'); + select f1 from t1 order by f1; + +# trigger privilege must be keep when mixinf tables with and without +# trigger privilege + +let $message= switch to table without having trigger priv for it:; +--source include/show_msg.inc + eval create table t2 (f1 char(20)) engine= $engine_type; +# Adding the minimal priv to be able to set to the db + grant SELECT, INSERT, UPDATE on priv_db.t2 to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + grant SELECT, INSERT, UPDATE on priv_db.t2 to test_noprivs@localhost; + show grants for test_noprivs@localhost; + +let $message= use table with trigger privilege and without...:; +--source include/show_msg.inc + connection yes_privs; + select current_user; + --error 1142 + create trigger trg2_1 before INSERT on t2 for each row + set new.f1 = 'trig 2_1-no'; + create trigger trg1_3 before INSERT on t1 for each row + set new.f1 = 'trig 1_3-yes'; + --error 1142 + create trigger trg2_2 before UPDATE on t2 for each row + set new.f1 = 'trig 2_2-no'; + create trigger trg1_4 before UPDATE on t1 for each row + set new.f1 = 'trig 1_4-yes'; + show triggers; + connection no_privs; + select current_user; + insert into t2 (f1) values ('insert9-yes'); + select f1 from t2 order by f1; + insert into t1 (f1) values ('insert10-no'); + select f1 from t1 order by f1; + disconnect no_privs; + + connection yes_privs; + select current_user; + --error 1360 + drop trigger trg2_1; + drop trigger trg1_3; + --error 1360 + drop trigger trg2_2; + drop trigger trg1_4; + + +# Cleanup table level + --disable_warnings + disconnect yes_privs; + + connection default; + select current_user; + --enable_warnings + + +# general Cleanup + --disable_warnings + drop database if exists priv_db; + drop user test_yesprivs@localhost; + drop user test_noprivs@localhost; + --enable_warnings + diff --git a/mysql-test/suite/funcs_1/triggers/triggers_03e_transaction.inc b/mysql-test/suite/funcs_1/triggers/triggers_03e_transaction.inc new file mode 100644 index 00000000000..a3f3a753774 --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/triggers_03e_transaction.inc @@ -0,0 +1,82 @@ +#====================================================================== +# +# Trigger Tests +# test cases for TRIGGER privilege on db, table and column level +#====================================================================== + +--disable_abort_on_error + +########################################### +################ Section 3.5.3 ############ +# Check for Triggers in transactions # +########################################### + +# General setup to be used in all testcases +let $message= ######### Testcase for transactions: ########; +--source include/show_msg.inc + + --disable_warnings + drop database if exists priv_db; + --enable_warnings + create database priv_db; + use priv_db; + eval create table t1 (f1 char(20)) engine= $engine_type; + + create User test_yesprivs@localhost; + set password for test_yesprivs@localhost = password('PWD'); + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + + connection default; + select current_user; + grant select, insert, update ,trigger + on priv_db.t1 to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + connection yes_privs; + select current_user; + use priv_db; + set autocommit=0; + create definer=current_user trigger trg1_1 + before INSERT on t1 for each row + set new.f1 = 'trig 1_1-yes'; + rollback work; + insert into t1 (f1) values ('insert-no'); + select f1 from t1 order by f1; + + create definer=test_yesprivs@localhost trigger trg1_2 + before UPDATE on t1 for each row + set new.f1 = 'trig 1_2-yes'; + commit work; + update t1 set f1 = 'update-yes' where f1 like '%trig%'; + select f1 from t1 order by f1; + commit work; + drop trigger trg1_1; + rollback work; + --error 1360 + drop trigger trg1_1; + drop trigger trg1_2; + commit work; + set autocommit=1; + + connection default; + select current_user; + +# Cleanup prepare + --disable_warnings + disconnect yes_privs; + + connection default; + select current_user; + --enable_warnings + + +# general Cleanup + --disable_warnings + drop database if exists priv_db; + drop user test_yesprivs@localhost; + --enable_warnings + diff --git a/mysql-test/suite/funcs_1/triggers/triggers_0407.inc b/mysql-test/suite/funcs_1/triggers/triggers_0407.inc new file mode 100644 index 00000000000..ccfeb1aec99 --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/triggers_0407.inc @@ -0,0 +1,611 @@ +#====================================================================== +# +# Trigger Tests +# (test case numbering refer to requirement document TP v1.1) +#====================================================================== + +--disable_abort_on_error + +# General setup for Trigger tests +let $message= Testcase: 3.5:; +--source include/show_msg.inc + +--disable_abort_on_error + + 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; + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (con1_general,localhost,test_general,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (con1_super,localhost,test_super,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + +#################################### +############ Section 3.5.4 ######### +# Drop Trigger Checkes: # +#################################### +let $message= Testcase 3.5.4:; +--source include/show_msg.inc + + connection default; + use test; + +#Section 3.5.4.1 +# Testcase: Ensure that the DROP TRIGGER statement cleanly drops its target trigger. +let $message= Testcase 3.5.4.1:; +--source include/show_msg.inc + + connection con1_super; + create database db_drop; + Use db_drop; + eval create table t1 (f1 char(30)) engine=$engine_type; + 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'; + connection con1_general; + Use db_drop; + Insert into t1 values ('Insert error 3.5.4.1'); + Select * from t1 order by f1; + connection con1_super; + drop trigger trg1; + select trigger_schema, trigger_name, event_object_table + from information_schema.triggers order by trigger_name; + connection con1_general; + Insert into t1 values ('Insert no trigger 3.5.4.1'); + Select * from t1 order by f1; + +#Cleanup + --disable_warnings + connection con1_super; + --disable_warnings + --error 0,1360 + drop trigger trg1; + drop database if exists db_drop; + revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; + --enable_warnings + +#Section 3.5.4.2 +# Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate error +# message, if the trigger name does not exist. +let $message= Testcase 3.5.4.2:; +--source include/show_msg.inc + + connection con1_super; + create database db_drop2; + Use db_drop2; + --disable_warnings + drop table if exists t1_432 ; + --enable_warnings + eval create table t1_432 (f1 char (30)) engine=$engine_type; + --error 1360 + Drop trigger tr_does_not_exit; +#cleanup + --disable_warnings + drop table if exists t1_432 ; + drop database if exists db_drop2; + --enable_warnings + +#Section 3.5.4.3 +# Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate +# error message, if <trigger name> is not a qualified name. +let $message= Testcase 3.5.4.3:; +--source include/show_msg.inc + + connection con1_super; + create database db_drop3; + Use db_drop3; + --disable_warnings + drop table if exists t1_433 ; + drop table if exists t1_433a ; + --enable_warnings + eval create table t1_433 (f1 char (30)) engine=$engine_type; + eval create table t1_433a (f1a char (5)) engine=$engine_type; + + CREATE TRIGGER trg3 BEFORE INSERT on t1_433 for each row + set new.f1 = 'Trigger 3.5.4.3'; + +# Using table + --error 1064 + Drop trigger t1.433.trg3; + +# Using database.table + --error 1064 + Drop trigger db_drop3.t1.433.trg3; + +# wrong database + --error 1360 + Drop trigger mysql.trg3; + +# database does not exist + --error 1360 + Drop trigger tbx.trg3; + +#cleanup + Drop trigger db_drop3.trg3; + drop table if exists t1_433; + drop table if exists t1_433a; + drop database if exists db_drop3; + +#Section 3.5.4.4 +# Test case: Ensure that when a database is dropped, all triggers created within +# that database are also cleanly dropped. +let $message= Testcase 3.5.4.4:; +--source include/show_msg.inc + + connection con1_super; + create database db_drop4; + Use db_drop4; + eval create table t1 (f1 char(30)) engine=$engine_type; + 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'; + connection con1_general; + Use db_drop4; + Insert into t1 values ('Insert 3.5.4.4'); + Select * from t1; + connection con1_super; + Drop database db_drop4; + Show databases; + select trigger_schema, trigger_name, event_object_table + from information_schema.triggers + where information_schema.triggers.trigger_name='trg4'; + create database db_drop4; + Use db_drop4; + eval create table t1 (f1 char(30)) engine=$engine_type; + grant INSERT, SELECT on db_drop4.t1 to test_general; + connection con1_general; + Insert into t1 values ('2nd Insert 3.5.4.4'); + Select * from t1; + +#Cleanup + connection con1_super; + --disable_warnings + --error 1360 + drop trigger trg4; + drop database if exists db_drop4; + --enable_warnings + revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; + +#Section 3.5.4.5 +# Test case: Ensure that when a table is dropped, all triggers for which it is the +# subject table are also cleanly dropped. +let $message= Testcase 3.5.4.5:; +--source include/show_msg.inc + + connection con1_super; + create database db_drop5; + Use db_drop5; + eval create table t1 (f1 char(50)) engine=$engine_type; + 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'; + connection con1_general; + Use db_drop5; + Insert into t1 values ('Insert 3.5.4.5'); + Select * from t1; + connection con1_super; + Drop table t1; + Show tables; + select trigger_schema, trigger_name, event_object_table + from information_schema.triggers + where information_schema.triggers.trigger_name='trg5'; + eval create table t1 (f1 char(50)) engine=$engine_type; + grant INSERT, SELECT on t1 to test_general; + connection con1_general; + Insert into t1 values ('2nd Insert 3.5.4.5'); + Select * from t1; + +#Cleanup + connection con1_super; + --disable_warnings + --error 1360 + drop trigger trg5; + drop database if exists db_drop5; + --enable_warnings + revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; + + +################################## +######### Section 3.5.5 ########## +# Checks on the Subject Table # +################################## + +let $message= Testcase 3.5.5:; +--source include/show_msg.inc + + connection default; + use test; + +#Section 3.5.5.1 +# Test case: Ensure that, if CREATE TRIGGER is executed with a non-existent +# subject table, the statement fails with an appropriate error message. +let $message= Testcase 3.5.5.1:; +--source include/show_msg.inc + + --error 1146 + Create trigger trg1 before INSERT on t100 for each row set new.f2=1000; + + +#Section 3.5.5.2 +# Test case: Ensure that, if CREATE TRIGGER is executed with a temporary table +# as the subject table, the statement fails with an appropriate error message. +let $message= Testcase 3.5.5.2:; +--source include/show_msg.inc + + Create temporary table t1_temp (f1 bigint signed, f2 bigint unsigned); + + --error 1361 + Create trigger trg2 before INSERT + on t1_temp for each row set new.f2=9999; + +#Cleanup + --disable_warnings + drop table t1_temp; + --enable_warnings + + +#Section 3.5.5.3 +# Test case: Ensure that, if CREATE TRIGGER is executed with a view as the subject +# table, the statement fails with an appropriate error message. +let $message= Testcase 3.5.5.3:; +--source include/show_msg.inc + + Create view vw3 as select f118 from tb3; + +# OBN Not sure why the server is returning error 1347 + --error 1347 + Create trigger trg3 before INSERT + on vw3 for each row set new.f118='s'; + +#Cleanup + --disable_warnings + drop view vw3; + --enable_warnings + + +#Section 3.5.5.4 +# Test case: Ensure that, if CREATE TRIGGER is executed with a table that resides +# in a different database than in which the trigger will reside, the +# statement fails with an appropriate error message; that is, ensure that +# the trigger and its subject table must reside in the same database. +let $message= Testcase 3.5.5.4:; +--source include/show_msg.inc + + connection con1_super; + create database dbtest_one; + create database dbtest_two; + use dbtest_two; + eval create table t2 (f1 char(15)) engine=$engine_type; + use dbtest_one; + --error 1435 + create trigger trg4 before INSERT + on dbtest_two.t2 for each row set new.f1='trig 3.5.5.4'; + grant INSERT, SELECT on dbtest_two.t2 to test_general; + grant SELECT on dbtest_one.* to test_general; + connection con1_general; + use dbtest_two; + Insert into t2 values ('1st Insert 3.5.5.4'); + Select * from t2; + use dbtest_one; + Insert into dbtest_two.t2 values ('2nd Insert 3.5.5.4'); + Select * from dbtest_two.t2 order by f1; + +#Cleanup + connection con1_super; + --disable_warnings + revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; + DROP DATABASE if exists dbtest_one; + drop database if EXISTS dbtest_two; + --enable_warnings + +##################################### +########### Section 3.5.6 ########### +# Check on the Trigger Action Time # +##################################### + +let $message= Testcase 3.5.6:; +--source include/show_msg.inc + + connection default; + use test; + +#Section 3.5.6.1 +# Test case: Ensure that a trigger definition can specify a trigger action time of BEFORE. +# See section 3.5.1.1 +let $message= Testcase 3.5.6.1 (see Testcase 3.5.1.1); +--source include/show_msg.inc + +#Section 3.5.6.2 +# Test case: Ensure that a trigger definition can specify a trigger action time of AFTER. +# See section 3.5.1.1 +let $message= Testcase 3.5.6.2 (see Testcase 3.5.1.1); +--source include/show_msg.inc + +#Section 3.5.6.3 +# Test case: Ensure that a trigger definition that specifies a trigger action +# time that is not either BEFORE or AFTER fails, with an appropriate +# error message, at CREATE TRIGGER time. +let $message= Testcase 3.5.6.3:; +--source include/show_msg.inc + + --error 1064 + Create trigger trg3_1 DURING UPDATE on tb3 for each row set new.f132=25; + --error 1064 + Create trigger trg3_2 TIME INSERT on tb3 for each row set new.f132=15; + +#Cleanup +# OBN - Although none of the above should have been created we should do a cleanup +# since if they have been created, not dropping them will affect following +# tests. + --disable_warnings + --error 0, 1360 + drop trigger tb3.trg3_1; + --error 0, 1360 + drop trigger tb3.trg3_2; + --enable_warnings + +#Section 3.5.6.4 +# Test case: Ensure that a trigger defined with a trigger action time of BEFORE +# always executes its triggered action immediately before the trigger event. +# See section 3.5.1.1 +let $message= Testcase 3.5.6.4 (see Testcase 3.5.1.1); +--source include/show_msg.inc + +#Section 3.5.6.5 +# Test case: Ensure that a trigger defined with a trigger action time of AFTER +# always executes its triggered action immediately after the trigger event. +let $message= Testcase 3.5.6.5 (see Testcase 3.5.1.1); +--source include/show_msg.inc + +############################# +####### Section 3.5.7 ####### +# Check on Trigger Event # +############################# + +#Section 3.5.7.1 +#Test case: Ensure that a trigger definition can specify a trigger event of INSERT. +let $message= Testcase 3.5.7.1 (see Testcase 3.5.1.1); +--source include/show_msg.inc + +#Section 3.5.7.2 +# Test case: Ensure that a trigger definition can specify a trigger event of UPDATE. +let $message= Testcase 3.5.7.2 (see Testcase 3.5.1.1); +--source include/show_msg.inc + +#Section 3.5.7.3 +# Test case: Ensure that a trigger definition can specify a trigger event of DELETE. +let $message= Testcase 3.5.7.3 (see Testcase 3.5.1.1); +--source include/show_msg.inc + +#Section 3.5.7.4 +# Test case: Ensure that a trigger definition that specifies a trigger event that +# is not either INSERT, UPDATE or DELETE fails, with an appropriate error +# message, at CREATE TRIGGER time. +let $message= Testcase 3.5.7.4:; +--source include/show_msg.inc + + --error 1064 + Create trigger trg4_1 BEFORE SELECT on tb3 for each row set new.f132=5; + --error 1064 + Create trigger trg4_2 AFTER VALUE on tb3 for each row set new.f132=1; + +#Cleanup +# OBN - Although none of the above should have been created we should do a cleanup +# since if they have been created, not dropping them will affect following +# tests. + --disable_warnings + --error 0, 1360 + drop trigger tb3.trg4_1; + --error 0, 1360 + drop trigger tb3.trg4_2; + --enable_warnings + +#Section 3.5.7.5 / 3.5.7.6 +# Test case: Ensure that it is not possible to create multiple BEFORE INSERT triggers +# on the same table, even if the triggers have different names / different +# triggered actions. +let $message= Testcase 3.5.7.5 / 3.5.7.6:; +--source include/show_msg.inc + + Create trigger trg5_1 BEFORE INSERT + on tb3 for each row set new.f122='Trigger1 3.5.7.5/6'; + + --error ER_NOT_SUPPORTED_YET + Create trigger trg5_2 BEFORE INSERT + on tb3 for each row set new.f122='Trigger2 3.5.7.5'; + + 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'; + 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'; + +#Cleanup + --disable_warnings + drop trigger trg5_1; + --error 0, 1360 + drop trigger trg5_2; + delete from tb3 where f121='Test 3.5.7.5/6'; + --enable_warnings + + +#Section 3.5.7.7 / 3.5.7.8 +# Test case: Ensure that it is not possible to create multiple AFTER INSERT triggers +# on the same table, even if the triggers have different names / different +# triggered actions. +let $message= Testcase 3.5.7.7 / 3.5.7.8:; +--source include/show_msg.inc + + 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'; + + --error ER_NOT_SUPPORTED_YET + Create trigger trg6_2 AFTER INSERT + on tb3 for each row set @test_var='Trigger2 3.5.7.7'; + + select @test_var; + 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'; + select @test_var; + 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'; + select @test_var; + +#Cleanup + --disable_warnings + drop trigger trg6_1; + --error 0, 1360 + drop trigger trg6_2; + delete from tb3 where f121='Test 3.5.7.7/8'; + --enable_warnings + + +#Section 3.5.7.9 / 3.5.7.10 +# Test case: Ensure that it is not possible to create multiple BEFORE UPDATE triggers +# on the same table, even if the triggers have different names / different +# triggered actions. +let $message= Testcase 3.5.7.9/10:; +--source include/show_msg.inc + + Create trigger trg7_1 BEFORE UPDATE + on tb3 for each row set new.f122='Trigger1 3.5.7.9/10'; + + --error ER_NOT_SUPPORTED_YET + Create trigger trg7_2 BEFORE UPDATE + on tb3 for each row set new.f122='Trigger2 3.5.7.9'; + + 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'; + 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'; + +#Cleanup + --disable_warnings + drop trigger trg7_1; + --error 0, 1360 + drop trigger trg7_2; + delete from tb3 where f121='Test 3.5.7.9/10'; + +#Section 3.5.7.11 / 3.5.7.12 +# Test case: Ensure that it is not possible to create multiple AFTER UPDATE triggers +# on the same table, even if the triggers have different names / different +# triggered actions. +let $message= Testcase 3.5.7.11/12:; +--source include/show_msg.inc + + 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'; + + --error ER_NOT_SUPPORTED_YET + Create trigger trg8_2 AFTER UPDATE + on tb3 for each row set @test_var='Trigger2 3.5.7.11'; + + + select @test_var; + Insert into tb3 (f121,f122) values ('Test 3.5.7.11/12','Insert 3.5.7.11/12'); + select @test_var; + Select f121,f122 from tb3 where f121='Test 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'; + select @test_var; + delete from tb3 where f121='Test 3.5.7.11/12'; + +#Cleanup + --disable_warnings + drop trigger trg8_1; + --error 0, 1360 + drop trigger trg8_2; + delete from tb3 where f121='Test 3.5.7.11/12'; + +#Section 3.5.7.13 / 3.5.7.14 +# Test case: Ensure that it is not possible to create multiple BEFORE DELETE triggers +# on the same table, even if the triggers have different names / different +# triggered actions. +let $message= Testcase 3.5.7.13/14:; +--source include/show_msg.inc + + set @test_var=1; + Create trigger trg9_1 BEFORE DELETE + on tb3 for each row set @test_var=@test_var+1; + + --error ER_NOT_SUPPORTED_YET + Create trigger trg9_2 BEFORE DELETE + on tb3 for each row set @test_var=@test_var+10; + + select @test_var; + 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'; + select @test_var; + delete from tb3 where f121='Test 3.5.7.13/14'; + Select f121,f122 from tb3 where f121='Test 3.5.7.13/14'; + select @test_var; + delete from tb3 where f121='Test 3.5.7.13/14'; + select @test_var; + +#Cleanup + --disable_warnings + drop trigger trg9_1; + --error 0, 1360 + drop trigger trg9_2; + delete from tb3 where f121='Test 3.5.7.13/14'; + +#Section 3.5.7.15 / 3.5.7.16 +# Test case: Ensure that it is not possible to create multiple AFTER DELETE triggers +# on the same table, even if the triggers have different names / different +# triggered actions. +let $message= Testcase 3.5.7.15/16:; +--source include/show_msg.inc + + set @test_var=1; + Create trigger trg_3_406010_1 AFTER DELETE + on tb3 for each row set @test_var=@test_var+5; + + --error ER_NOT_SUPPORTED_YET + Create trigger trg_3_406010_2 AFTER DELETE + on tb3 for each row set @test_var=@test_var+50; + + --error 1359 + Create trigger trg_3_406010_1 AFTER INSERT + on tb3 for each row set @test_var=@test_var+1; + + select @test_var; + 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'; + select @test_var; + delete from tb3 where f121='Test 3.5.7.15/16'; + Select f121,f122 from tb3 where f121='Test 3.5.7.15/16'; + select @test_var; + delete from tb3 where f121='Test 3.5.7.15/16'; + select @test_var; + +#Cleanup + --disable_warnings + drop trigger trg_3_406010_1; + --error 0, 1360 + drop trigger trg_3_406010_2; + delete from tb3 where f121='Test 3.5.7.15/16'; + --enable_warnings + + +#Section 3.5.7.17 +# Test case: Ensure that it is possible to have a BEFORE INSERT, an AFTER INSERT, +# a BEFORE UPDATE, an AFTER UPDATE, a BEFORE DELETE, and an AFTER DELETE +# trigger on the same table; that is, ensure that every persistent base +# table may be the subject table for exactly six triggers +let $message= Testcase 3.5.7.17 (see Testcase 3.5.1.1); +--source include/show_msg.inc + + +# Cleanup section 3.5 + connection default; + drop user test_general@localhost; + drop user test_general; + drop user test_super@localhost; + diff --git a/mysql-test/suite/funcs_1/triggers/triggers_08.inc b/mysql-test/suite/funcs_1/triggers/triggers_08.inc new file mode 100644 index 00000000000..4a7ea486248 --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/triggers_08.inc @@ -0,0 +1,538 @@ +#====================================================================== +# +# Trigger Tests +# (test case numbering refer to requirement document TP v1.1) +#====================================================================== + +# General setup for Trigger tests +let $message= Testcase: 3.5:; +--source include/show_msg.inc + +--disable_abort_on_error + + 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; + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (con2_general,localhost,test_general,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (con2_super,localhost,test_super,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + +################################# +####### Section 3.5.8 ########### +# Checks on Triggered Actions # +################################# + +#Section 3.5.8.1 +# Testcase: Ensure that the triggered action of every trigger always executes +# correctly and the results in all expected changes made to the database +let $message= Testcase 3.5.8.1: (implied in previous tests); +--source include/show_msg.inc + +# OBN - FIXME - Missing 3.5.8.1 need to add + +#Section 3.5.8.2 +# Testcase: Ensure that the triggered actions of every trigger never results +# in an unexpected change made to the database. +let $message= Testcase 3.5.8.2: (implied in previous tests); +--source include/show_msg.inc + + +#Section 3.5.8.3 / 3.5.8.4 +#Test case: Ensure that the triggered action can any valid SQL statement / set +# of valid SQL statements, provided the statements are written within +# a BEGIN/END compound statement construct +# OBN - At this point the tests focuses on the the INSERT/UPDATE/DELETE SQL statements +# as there are the most likely to be used in triggers +let $message= Testcase 3.5.8.3/4:; +--source include/show_msg.inc + +# creating test tables to perform the trigger SQL on + connection con2_super; + 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; + eval 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=$engine_type; + eval 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=$engine_type; + eval 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=$engine_type; + 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); + +let $message= 3.5.8.4 - multiple SQL; +--source include/show_msg.inc +# Trigger definition - multiple SQL + use test; + delimiter //; + 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// + delimiter ;// + +# Test trigger execution - multiple SQL + connection con2_general; + 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'; +# error in ndb + select * from db_test.t1_i order by i120; + select * from db_test.t1_u order by u120; + select * from db_test.t1_d order by d120; + select @test_var; + + +let $message= 3.5.8.4 - single SQL - insert; +--source include/show_msg.inc +# Trigger definition - single SQL Insert + connection con2_super; + delimiter //; + Create trigger trg2 BEFORE UPDATE on tb3 for each row + BEGIN + insert into db_test.t1_i + values (new.f120, new.f136, new.f144, new.f163); + END// + delimiter ;// + +# Trigger exeution - single SQL Insert + connection con2_general; + Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%'; + select * from db_test.t1_i order by i120; + 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%'; + select * from db_test.t1_i order by i120; + + +let $message= 3.5.8.4 - single SQL - update; +--source include/show_msg.inc +# Trigger definition - single SQL update + connection con2_super; + 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; + +# Trigger exeution - single SQL - update; + connection con2_general; + 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%'; + select * from db_test.t1_u order by u120; + + +let $message= 3.5.8.3/4 - single SQL - delete; +--source include/show_msg.inc +# Trigger definition - single SQL delete + connection con2_super; + drop trigger trg3; + Create trigger trg4 AFTER UPDATE on tb3 for each row + delete from db_test.t1_d where d136= new.f136; + +# Trigger exeution - single SQL delete + connection con2_general; +#lock tables tb3 write, db_test.t1_i write, db_test.t1_u write, db_test.t1_d write; + update tb3 set f120='D', f136=444, + f122='Test 3.5.8.4-Single Delete' + where f122='Test 3.5.8.4-Single Update'; +#unlock tables; + Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%'; + select * from db_test.t1_d order by d120; + + +let $message= 3.5.8.3/4 - single SQL - select; +--source include/show_msg.inc +# Trigger definition - single SQL select + connection con2_super; + 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; + +# Trigger exeution - single SQL select + connection con2_general; + 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%'; + select @test_var; + +#Cleanup + connection default; + --disable_warnings + 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'; + --enable_warnings + + +#Section 3.5.8.5 (IF) +# Test case: Ensure that the stored procedure-specific flow control statement like IF +# works correctly when it is a part of the triggered action portion of a +# trigger definition. +let $message= Testcase 3.5.8.5 (IF):; +--source include/show_msg.inc + + delimiter //; + 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// + delimiter ;// + + 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' order by f136; + 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' order by f136; + 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' order by f136; + 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' order by f136; + + delimiter //; + --error 1064 + create trigger trg3 before update on tb3 for each row + BEGIN + ELSEIF new.f120='2' then + END IF; + END// + --error 0, 1360 + drop trigger trg3// + + --error 1064 + 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// + delimiter ;// + --error 0, 1360 + drop trigger trg4; + +#Cleanup + --disable_warnings + drop trigger trg2; + delete from tb3 where f121='Test 3.5.8.5-if'; + --enable_warnings + + +#Section 3.5.8.5 (CASE) +# Test case: Ensure that the stored procedure-specific flow control statement +# like CASE works correctly when it is a part of the triggered action +# portion of a trigger definition. +let $message= Testcase 3.5.8.5-case:; +--source include/show_msg.inc + + delimiter //; + 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// + delimiter ;// + + 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' order by f120; + 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' order by f120; + 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' order by f120; + Insert into tb3 (f120, f122, f136) + values ('d', 'Test 3.5.8.5-case', 152); + select f120, f122, f136, f144, @test_var + from tb3 where f122 = 'Test 3.5.8.5-case' order by f120; + Insert into tb3 (f120, f122, f136, f144) + values ('e', 'Test 3.5.8.5-case', 200, 8); + select f120, f122, f136, f144, @test_var + from tb3 where f122 = 'Test 3.5.8.5-case' order by f120; + --error 0, 1339 + 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' order by f120; + + delimiter //; + --error 1064 + create trigger trg3a before update on tb3 for each row + BEGIN + CASE + when new.f136<100 then set new.f120='p'; + END// + delimiter ;// + + --error 0, 1360 + drop trigger trg3a; + +#Cleanup + --disable_warnings + drop trigger trg3; + delete from tb3 where f121='Test 3.5.8.5-case'; + --enable_warnings + +#Section 3.5.8.5 (LOOP) +# Test case: Ensure that the stored procedure-specific flow control +# statement like LOOP / LEAVE work correctly when they are +# part of the triggered action portion of a trigger definition. +let $message= Testcase 3.5.8.5-loop/leave:; +--source include/show_msg.inc + + delimiter //; + 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// + delimiter ;// + Insert into tb3 (f122, f136, f144) + values ('Test 3.5.8.5-loop', 2, 8); + select @counter, @flag; + Insert into tb3 (f122, f136, f144) + values ('Test 3.5.8.5-loop', 11, 8); + select @counter, @flag; + + + delimiter //; + + --error 1064 + Create trigger trg4_2 after update on tb3 for each row + BEGIN + Label1: loop + set @counter=@counter+1; + END; + END// + delimiter ;// + --error 0, 1360 + drop trigger trg4_2; + +#Cleanup + --disable_warnings + drop trigger trg4; + delete from tb3 where f122='Test 3.5.8.5-loop'; + --enable_warnings + +#Section 3.5.8.5 (REPEAT ITERATE) +#Testcase: Ensure that the stored procedure-specific flow control statements +# like REPEAT work correctly when they are part of the triggered action +# portion of a trigger definition. +let $message= Testcase 3.5.8.5-repeat:; +--source include/show_msg.inc + + delimiter //; + 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// + delimiter ;// + + set @counter1= 0, @counter2= 0; + Insert into tb3 (f122, f136) + values ('Test 3.5.8.5-repeat', 13); + select @counter1, @counter2; + + + delimiter //; + --error 1064 + Create trigger trg6_2 after update on tb3 for each row + BEGIN + REPEAT + SET @counter2 = @counter2 + 1; + END// + delimiter ;// + +#Cleanup + --disable_warnings + drop trigger trg6; + delete from tb3 where f122='Test 3.5.8.5-repeat'; + --enable_warnings + + +#Section 3.5.8.5 (WHILE) +# Test case: Ensure that the stored procedure-specific flow control +# statements WHILE, work correctly when they are part of +# the triggered action portion of a trigger definition. +let $message= Testcase 3.5.8.5-while:; +--source include/show_msg.inc + + delimiter //; + 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// + delimiter ;// + + set @counter1= 0, @counter2= 0; + Insert into tb3 (f122, f136) + values ('Test 3.5.8.5-while', 7); + select @counter1, @counter2; + delimiter //; + --error 1064 + Create trigger trg7_2 after update on tb3 for each row + BEGIN + WHILE @counter1 < new.f136 + SET @counter1 = @counter1 + 1; + END// + delimiter ;// + +#Cleanup + --disable_warnings + delete from tb3 where f122='Test 3.5.8.5-while'; + drop trigger trg7; + --enable_warnings + +#Section 3.5.8.6 +# Test case: Ensure that a trigger definition that includes a CALL to a stored +# procedure fails, at CREATE TRIGGER time, with an appropriate error +# message +# OBN - requirement void since allowed +# Fails due to Bug 9909 the bug allows the trigger to be created +# and fails in execution time +let $message= Testcase 3.5.8.6: (requirement void); +--source include/show_msg.inc + + +#Section 3.5.8.7 +# Test case: Ensure that a trigger definition that includes a +# transaction-delimiting statement (e.g. COMMIT, +# ROLLBACK, START TRANSACTION) fails, at CREATE TRIGGER +# time, with an appropriate error message. +# OBN - Fails due to Bug ____ +let $message= Testcase 3.5.8.7: (Disabled as a result of bug _____); +--source include/show_msg.inc + +# --error 1314 +# Create trigger trg9_1 before update on tb3 for each row +# BEGIN +# Start transaction; +# Set new.f120='U'; +# Commit; +# END; + +# --error 1314 +# Create trigger trg9_2 before delete on tb3 for each row +# BEGIN +# Start transaction; +# Set @var2=old.f120; +# Rollback; +# END; + + +# Cleanup section 3.5 + connection default; + drop user test_general@localhost; + drop user test_general; + drop user test_super@localhost; + + diff --git a/mysql-test/suite/funcs_1/triggers/triggers_09.inc b/mysql-test/suite/funcs_1/triggers/triggers_09.inc new file mode 100644 index 00000000000..912c5f50b21 --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/triggers_09.inc @@ -0,0 +1,318 @@ +#====================================================================== +# +# Trigger Tests +# (test case numbering refer to requirement document TP v1.1) +#====================================================================== + + +################################# +####### Section 3.5.9 ########### +# Checks on old and new rows # +################################# + +#Section 3.5.9.1 +#Test case: Ensure that every trigger executes its triggered action on each row +# that meets the conditions stated in the trigger definition. +#Section 3.5.9.2 +#Testcase: Ensure that a trigger never executes its triggered action on any row +# that doesn't meet the conditions stated in the trigger definition. +let $message= Testcase 3.5.9.1/2:; +--source include/show_msg.inc + + Create trigger trg1 BEFORE UPDATE on tb3 for each row + set new.f142 = 94087, @counter=@counter+1; +--disable_query_log + select count(*) as TotalRows from tb3; + select count(*) as Affected from tb3 where f130<100; + select count(*) as NotAffected from tb3 where f130>=100; + select count(*) as NewValuew from tb3 where f142=94087; +--enable_query_log + set @counter=0; + Update tb3 Set f142='1' where f130<100; + select count(*) as ExpectedChanged, @counter as TrigCounter + from tb3 where f142=94087; + select count(*) as ExpectedNotChange from tb3 + where f130<100 and f142<>94087; + select count(*) as NonExpectedChanged from tb3 + where f130>=130 and f142=94087; + +#Cleanup + --disable_warnings + drop trigger trg1; + --enable_warnings + + +#Section 3.5.9.3 +#Test case: Ensure that a reference to OLD.<column name> always correctly refers +# to the values of the specified column of the subject table before a +# data row is updated or deleted. +let $message= Testcase 3.5.9.3:; +--source include/show_msg.inc + + 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; + + +--disable_query_log + set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0, + @tr_var_b4_136=0, @tr_var_b4_163=0; + set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0, + @tr_var_af_136=0, @tr_var_af_163=0; + select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, + @tr_var_b4_136, @tr_var_b4_163; + select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, + @tr_var_af_136, @tr_var_af_163; +--enable_query_log + + 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' order by f136; + select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, + @tr_var_b4_136, @tr_var_b4_163; + select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, + @tr_var_af_136, @tr_var_af_163; + +--disable_query_log + set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0, + @tr_var_b4_136=0, @tr_var_b4_163=0; + set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0, + @tr_var_af_136=0, @tr_var_af_163=0; + select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, + @tr_var_b4_136, @tr_var_b4_163; + select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, + @tr_var_af_136, @tr_var_af_163; +--enable_query_log + + 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' order by f136; + select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, + @tr_var_b4_136, @tr_var_b4_163; + select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, + @tr_var_af_136, @tr_var_af_163; +#Cleanup + --disable_warnings + drop trigger trg2_a; + drop trigger trg2_b; + drop trigger trg2_c; + drop trigger trg2_d; + --enable_warnings + +#Section 3.5.9.4 +#Test case: Ensure that a reference to NEW.<column name> always correctly refers +# to the values of the specified column of the subject table after an +# existing data row has been updated or a new data row has been inserted. +let $message= Testcase 3.5.9.4:; +--source include/show_msg.inc + + 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; + +--disable_query_log + set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0, + @tr_var_b4_136=0, @tr_var_b4_151=0, @tr_var_b4_163=0; + set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0, + @tr_var_af_136=0, @tr_var_af_151=0, @tr_var_af_163=0; + 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; + 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; +--enable_query_log + + 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%' order by f163; + 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; + 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; + +--disable_query_log + set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0, + @tr_var_b4_136=0, @tr_var_b4_151=0, @tr_var_b4_163=0; + set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0, + @tr_var_af_136=0, @tr_var_af_151=0, @tr_var_af_163=0; + 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; + 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; +--enable_query_log + + Update tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL + where f122='Test 3.5.9.4'; + + select f118, f121, f122, f136, f151, f163 from tb3 + where f122 like 'Test 3.5.9.4-trig' order by f163; + 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; + 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; +#Cleanup + --disable_warnings + 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'; + --enable_warnings + + +#Section 3.5.9.5 +# Test case: Ensure that the definition of an INSERT trigger can include a +# reference to NEW. <Column name>. +let $message= Testcase 3.5.9.5: (implied in previous tests); +--source include/show_msg.inc + +#Section 3.5.9.6 +# Test case: Ensure that the definition of an INSERT trigger cannot include +# a reference to OLD. <Column name>. +let $message= Testcase 3.5.9.6:; +--source include/show_msg.inc + + --error 1363 + create trigger trg4a before insert on tb3 for each row + set @temp1= old.f120; + --error 1362 + create trigger trg4b after insert on tb3 for each row + set old.f120= 'test'; + +#Cleanup + --disable_warnings + --error 0, 1360 + drop trigger trg4a; + --error 0, 1360 + drop trigger trg4b; + --enable_warnings + + +#Section 3.5.9.7 +# Test case: Ensure that the definition of an UPDATE trigger can include a +# reference to NEW. <Column name>. +let $message= Testcase 3.5.9.7: (implied in previous tests); +--source include/show_msg.inc + +#Section 3.5.9.8 +# Test case: Ensure that the definition of an UPDATE trigger cannot include a +# reference to OLD. <Column name>. +let $message= Testcase 3.5.9.8: (implied in previous tests); +--source include/show_msg.inc + +#Section 3.5.9.9 +# Test case: Ensure that the definition of a DELETE trigger cannot include a +# reference to NEW.<column name>. +let $message= Testcase 3.5.9.9:; +--source include/show_msg.inc + + --error 1363 + create trigger trg5a before DELETE on tb3 for each row + set @temp1=new.f122; + --error 1363 + create trigger trg5b after DELETE on tb3 for each row + set new.f122='test'; +let $message= The above returns the wrong error, should be error 1362 (Bug 11648) +--source include/show_msg.inc + +#Cleanup + --disable_warnings + --error 0, 1360 + drop trigger trg5a; + --error 0, 1360 + drop trigger trg5b; + --enable_warnings + + +#Section 3.5.9.10 +# Test case: Ensure that the definition of a DELETE trigger can include a reference +# to OLD.<column name>. +let $message= Testcase 3.5.9.10: (implied in previous tests); +--source include/show_msg.inc + + +#Section 3.5.9.11 +# Testcase: Ensure that trigger definition that includes a referance to +# NEW.<colunm name> fails with an appropriate error message, +# at CREATE TRIGGER time, if the trigger event in not INSERT or UPDATE +let $message= Testcase 3.5.9.11: covered by 3.5.9.9; +--source include/show_msg.inc + + +#Section 3.5.9.12 +# Testcase: Ensure that trigger definition that includes a referance to +# OLD.<column name> fails with an appropriate error message, at +# CREATE TRIGGER time, if the trigger event is not DELETE or UPDATE +let $message= Testcase 3.5.9.12: covered by 3.5.9.6; +--source include/show_msg.inc + + +#Section 3.5.9.13 +# Test case: Ensure that all references to OLD. <Column name> are read-only, +# that is, that they cannot be used to modify a data row. +let $message= Testcase 3.5.9.13:; +--source include/show_msg.inc + + --error 1362 + create trigger trg6a before UPDATE on tb3 for each row + set old.f118='C', new.f118='U'; + --error 1362 + create trigger trg6b after INSERT on tb3 for each row + set old.f136=163, new.f118='U'; + --error 1362 + create trigger trg6c after UPDATE on tb3 for each row + set old.f136=NULL; + +#Cleanup + --disable_warnings + --error 0, 1360 + drop trigger trg6a; + --error 0, 1360 + drop trigger trg6b; + --error 0, 1360 + drop trigger trg6c; + --enable_warnings + + +#Section 3.5.9.14 +# Test case: Ensure that all references to NEW. <Column name> may be used both to +# read a data row and to modify a data row +let $message= Testcase 3.5.9.14: (implied in previous tests); +--source include/show_msg.inc + + diff --git a/mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc b/mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc new file mode 100644 index 00000000000..1b7505260f7 --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/triggers_1011ext.inc @@ -0,0 +1,401 @@ +#====================================================================== +# +# Trigger Tests +# (test case numbering refer to requirement document TP v1.1) +#====================================================================== + +--disable_abort_on_error + +############################################## +################ Section 3.5.10 ################# +# Check on Trigger Activation +############################################## +#Section 3.5.10.1 +# Test case: Ensure that every trigger that should be activated by +# every possible type of implicit insertion into its subject +# table (INSERT into a view based on the subject table) is +# indeed activated correctly +#Section 3.5.10.2 +# Test case: Ensure that every trigger that should be activated by every +# possible type of implicit insertion into its subject table +# (UPDATE into a view based on the subject table) is indeed +# activated correctly +#Section 3.5.10.3 +# Test case: Ensure that every trigger that should be activated by every +# possible type of implicit insertion into its subject table +# (DELETE from a view based on the subject table) is indeed +# activated correctly +let $message= Testcase 3.5.10.1/2/3:; +--source include/show_msg.inc + + 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; + +#Section 3.5.10.1 + 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%' order by f151; + select f121, f122, f151, f163 from vw11; + select f121, f122, f151, f163 + from tb3 where f122 like 'Not in View'; + +#Section 3.5.10.2 + Update vw11 set f163=1; + select f121, f122, f151, f163 from tb3 + where f122 like 'Test 3.5.10.1/2/3%' order by f151; + select f121, f122, f151, f163 from vw11; + +#Section 3.5.10.3 + set @test_var=0; + Select @test_var as 'before delete'; + delete from vw11 where f151=1; + select f121, f122, f151, f163 from tb3 + where f122 like 'Test 3.5.10.1/2/3%' order by f151; + select f121, f122, f151, f163 from vw11; + Select @test_var as 'after delete'; + +#Cleanup + --disable_warnings + 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%'; + --enable_warnings + + +#Section 3.5.10.4 +# Test case: Ensure that every trigger that should be activated by every +# possible type of implicit insertion into its subject table +# (LOAD into the subject table) is indeed activated correctly +let $message= Testcase 3.5.10.4:; +--source include/show_msg.inc + + eval create table tb_load (f1 int, f2 char(25),f3 int) engine=$engine_type; + 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'; + --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR + eval load data infile '$MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table tb_load; + + select @counter as 'Rows Loaded After'; + Select * from tb_load order by f1 limit 10; + +#Cleanup + --disable_warnings + drop trigger trg4; + drop table tb_load; + --enable_warnings + + +#Section 3.5.10.5 +# Testcase: Ensure that every trigger that should be activated by every possible +# type of implicit update of its subject table (e.g.a FOREIGN KEY SET +# DEFAULT action or an UPDATE of a view based on the subject table) is +# indeed activated correctly +let $message= Testcase 3.5.10.5: (implemented in trig_frkey.test); +--source include/show_msg.inc + + +#Section 3.5.10.6 +# Testcase: Ensure that every trigger that should be activated by every possible +# type of implicit deletion from its subject table (e.g.a FOREIGN KEY +# CASCADE action or a DELETE from a view based on the subject table) is +# indeed activated correctly +let $message= Testcase 3.5.10.6: (implemented in trig_frkey.test); +--source include/show_msg.inc + +#Section 3.5.10.extra +# Testcase: Ensure that every trigger that should be activated by every possible +# type of implicit deletion from its subject table (e.g. an action performed +# on the subject table from a stored procedure is indeed activated correctly +let $message= Testcase 3.5.10.extra:; +--source include/show_msg.inc + + eval create table t1_sp (var136 tinyint, var151 decimal) engine=$engine_type; + + create trigger trg before insert on t1_sp + for each row set @counter=@counter+1; + # declare continue handler for sqlstate '01000' set done = 1; + + delimiter //; + 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// + delimiter ;// + + set @counter=0; + select @counter; + --error 1329 + call trig_sp(); + select @counter; + select count(*) from tb3; + select count(*) from t1_sp; + +#Cleanup + --disable_warnings + drop procedure trig_sp; + drop trigger trg; + drop table t1_sp; + --enable_warnings + +################################## +########## Section 3.5.11 ######## +# Check on Trigger Performance # +################################## +#Section 3.5.11.1 +# Testcase: Ensure that a set of complicated, interlocking triggers that are activated +# by multiple trigger events on no fewer than 50 different tables with at least +# 500,000 rows each, all work correctly, return the correct results, and have +# the correct effects on the database. It is expected that the Services Provider +# will use its own skills and experience in database testing to devise tables and +# triggers that fulfill this requirement. +let $message= Testcase 3.5.11.1 (implemented in trig_perf.test); +--source include/show_msg.inc + + +########################################## +# Other Scenasrios (not in requirements) # +########################################## +# Testcase: y.y.y.2: +# Checking for triggers starting triggers (no direct requirement) +let $message= Testcase y.y.y.2: Check for triggers starting triggers; +--source include/show_msg.inc + + use test; + --disable_warnings + 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; + --enable_warnings + + eval create table t1 (f1 integer) engine=$engine_type; + eval create table t2_1 (f1 integer) engine=$engine_type; + eval create table t2_2 (f1 integer) engine=$engine_type; + eval create table t2_3 (f1 integer) engine=$engine_type; + eval create table t2_4 (f1 integer) engine=$engine_type; + eval create table t3 (f1 integer) engine=$engine_type; + + insert into t1 values (1); + delimiter //; + 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// + delimiter ;// + + 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); + +#lock tables t1 write, t2_1 write, t2_2 write, t2_3 write, t2_4 write, t3 write; + insert into t1 values (1); +#unlock tables; + select * from t3 order by f1; + +#Cleanup + --disable_warnings + 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; + --enable_warnings + +# Testcase: y.y.y.3: +# Checking for circular trigger definitions +let $message= Testcase y.y.y.3: Circular trigger reference; +--source include/show_msg.inc + use test; + --disable_warnings + drop table if exists t1; + drop table if exists t2; + drop table if exists t3; + drop table if exists t4; + --enable_warnings + eval create table t1 (f1 integer) engine = $engine_type; + eval create table t2 (f2 integer) engine = $engine_type; + eval create table t3 (f3 integer) engine = $engine_type; + eval create table t4 (f4 integer) engine = $engine_type; + + 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); + + # OBN See bug 11896 + --error 1442 + insert into t1 values (1); + select * from t1 order by f1; + select * from t2 order by f2; + select * from t3 order by f3; + select * from t4 order by f4; + +#Cleanup + --disable_warnings + drop trigger tr1; + drop trigger tr2; + drop trigger tr3; + drop trigger tr4; + drop table t1; + drop table t2; + drop table t3; + drop table t4; + --enable_warnings + + +#Section y.y.y.4 +# Testcase: create recursive trigger/storedprocedures conditions +let $message= Testcase y.y.y.4: Recursive trigger/SP references (disabled bug 11889); +--source include/show_msg.inc + +set @sql_mode='traditional'; + eval create table t1_sp ( + count integer, + var136 tinyint, + var151 decimal) engine=$engine_type; + + delimiter //; + 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// + delimiter ;// + + create trigger trg before insert on t1_sp + for each row call trig_sp(); + + set @counter=0; + select @counter; + --error 1456 + call trig_sp(); + select @counter; + select count(*) from tb3; + select count(*) from t1_sp; + + # check recursion will not work here: + set @@max_sp_recursion_depth= 10; + set @counter=0; + select @counter; + --error 1442 + call trig_sp(); + select @counter; + select count(*) from tb3; + select count(*) from t1_sp; + +#Cleanup + --disable_warnings + drop procedure trig_sp; + drop trigger trg; + drop table t1_sp; + --enable_warnings + + +# Testcase: y.y.y.5: +# Checking rollback of nested trigger definitions +let $message= Testcase y.y.y.5: Roleback of nested trigger references; +--source include/show_msg.inc + + set @@sql_mode='traditional'; + use test; + --disable_warnings + drop table if exists t1; + drop table if exists t2; + drop table if exists t3; + drop table if exists t4; + --enable_warnings + eval create table t1 (f1 integer) engine = $engine_type; + eval create table t2 (f2 integer) engine = $engine_type; + eval create table t3 (f3 integer) engine = $engine_type; + eval create table t4 (f4 tinyint) engine = $engine_type; + show create table t1; + 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); + +#lock tables t1 write, t2 write, t3 write, t4 write; + + set autocommit=0; + start transaction; + --error 1264 + insert into t1 values (1); + commit; + select * from t1 order by f1; + select * from t2 order by f2; + select * from t3 order by f3; +#unlock tables; +#Cleanup + --disable_warnings + drop trigger tr1; + drop trigger tr2; + drop trigger tr3; + drop table t1; + drop table t2; + drop table t3; + drop table t4; + --enable_warnings + diff --git a/mysql-test/suite/funcs_1/triggers/triggers_master.test b/mysql-test/suite/funcs_1/triggers/triggers_master.test new file mode 100644 index 00000000000..0f6d4ff555f --- /dev/null +++ b/mysql-test/suite/funcs_1/triggers/triggers_master.test @@ -0,0 +1,2818 @@ +#====================================================================== +# +# Trigger Tests +# (test case numbering refer to requirement document TP v1.1) +#====================================================================== + +# OBM - ToDo +############ +# 1. Information Schema Trigger Table +# 2. Performace +############################################### + +# General setup for Trigger tests +let $message= Testcase: 3.5:; +--source include/show_msg.inc + +--disable_abort_on_error + + 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; + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (con_general,localhost,test_general,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (con_super,localhost,test_super,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + + +##################################################### +################# Section 3.5.1 ##################### +# Syntax checks for CREATE TRIGGER and DROP TRIGGER # +##################################################### + +#Section 3.5.1.1 +# Testcase: Ensure that all clauses that should be supported are supported. +let $message= Testcase: 3.5.1.1:; +--source include/show_msg.inc +# OBN - This test case tests basic trigger definition and execution +# of INSERT/UPDATE/DELETE actions and BEFORE/AFTER timings. +# As such it covers the equirements in sections 3.5.6.1, 3.5.6.2, +# 3.5.6.4, 3.5.6.5, 3.5.7.1, 3.5.7.2, 3.5.7.3, 3.5.7.17 below. +# - Note currently as a result of limitations with locking tables in +# triggers, a specifc lockingof the tables is done. +# Once fixed, the locking and alias referances should be removed + +use test; +# Trigger Definition + 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'; +# Trigger Execution Insert (before and after) + set @test_before = 1; + set @test_after = 5; + select @test_before, @test_after; + 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'; + select @test_before, @test_after; + +# Trigger Execution Update (before and after) + set @test_before = 18; + set @test_after = 8; + select @test_before, @test_after; + 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'; + select @test_before, @test_after; + +# Trigger Execution Delete (before and after) + 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'; + select @test_before, @test_after; + 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'; + select @test_before, @test_after; + +#Cleanup + --disable_warnings + --error 0, 1360 + drop trigger trg1_1; + --error 0, 1360 + drop trigger trg1_2; + --error 0, 1360 + drop trigger trg1_3; + --error 0, 1360 + drop trigger trg1_4; + --error 0, 1360 + drop trigger trg1_5; + --error 0, 1360 + drop trigger trg1_6; + --enable_warnings + delete from tb3 where f121='Test 3.5.1.1'; + --enable_warnings + +#Section 3.5.1.2 +# Testcase: Ensure that all clauses that should not be supported are disallowed +# with an appropriate error message. +let $message= Testcase: 3.5.1.2:; +--source include/show_msg.inc + + --error 1064 + Create trigger trg_1 after insert + on tb3 for each statement set @x= 1; + +#Cleanup + --disable_warnings + --error 0, 1360 + drop trigger trg_1; + --enable_warnings + + +#Section 3.5.1.3 +# Testcase: Ensure that all supported clauses are supported only in the correct order. +let $message= Testcase 3.5.1.3:; +--source include/show_msg.inc + --error 1064 + CREATE TRIGGER trg3_1 on tb3 BEFORE INSERT for each row set new.f120 = 't'; + + --error 1064 + CREATE trg3_2 TRIGGER AFTER INSERT on tb3 for each row set new.f120 = 's'; + + --error 1064 + CREATE TRIGGER trg3_3 Before DELETE on tb3 set @ret1 = 'test' for each row; + + --error 1064 + CREATE TRIGGER trg3_4 DELETE AFTER on tb3 set @ret1 = 'test' for each row; + + --error 1064 + CREATE for each row TRIGGER trg3_5 AFTER UPDATE on tb3 set @ret1 = 'test'; + +#Cleanup +# OBN - Although none of the above should have been created we should do a cleanup +# since if they have been created, not dropping them will affect following +# tests. + --disable_warnings + --error 0, 1360 + drop trigger trg3_1; + --error 0, 1360 + drop trigger trg3_2; + --error 0, 1360 + drop trigger trg3_3; + --error 0, 1360 + drop trigger trg3_4; + --error 0, 1360 + drop trigger trg3_5; + --enable_warnings + + +#Section 3.5.1.4 +# Testcase: Ensure that an appropriate error message is returned if a clause +# is out-of-order in an SQL statement. +# OBN - FIXME - Missing 3.5.1.4 need to add + +#Section 3.5.1.5 +# Testcase: Ensure that all clauses that are defined to be mandatory are indeed +# required to be mandatory by the MySQL server and tools +let $message= Testcase: 3.5.1.5:; +--source include/show_msg.inc + + --error 1064 + CREATE TRIGGER trg4_1 AFTER on tb3 for each row set new.f120 = 'e'; + + --error 1064 + CREATE TRIGGER trg4_2 INSERT on tb3 for each set row new.f120 = 'f'; + + --error 1064 + CREATE TRIGGER trg4_3 BEFORE INSERT tb3 for each row set new.f120 = 'g'; + + --error 1064 + CREATE TRIGGER trg4_4 AFTER UPDATE on tb3 for each set new.f120 = 'g'; + + --error 1064 + CREATE trg4_5 AFTER DELETE on tb3 for each set new.f120 = 'g'; + + --error 1064 + CREATE TRIGGER trg4_6 BEFORE DELETE for each row set new.f120 = 'g'; + +#Cleanup +# OBN - Although none of the above should have been created we should do a cleanup +# since if they have been created, not dropping them will affect following +# tests. + --disable_warnings + --error 0, 1360 + drop trigger trg4_1; + --error 0, 1360 + drop trigger trg4_2; + --error 0, 1360 + drop trigger trg4_3; + --error 0, 1360 + drop trigger trg4_4; + --error 0, 1360 + drop trigger trg4_5; + --error 0, 1360 + drop trigger trg4_6; + --enable_warnings + +#Section 3.5.1.6 +# Testcase: Ensure that any clauses that are defined to be optional are indeed +# trated as optional by MySQL server and tools +let $message= Testcase 3.5.1.6: - Need to fix; +--source include/show_msg.inc +# OBN - FIXME - Missing 3.5.1.6 need to add + +#Section 3.5.1.7 +# Testcase: Ensure that all valid, fully-qualified, and non-qualified, +# trigger names are accepted, at creation time. +let $message= Testcase 3.5.1.7: - need to fix; +--source include/show_msg.inc + + drop table if exists t1; + eval create table t1 (f1 int, f2 char(25),f3 int) engine=$engine_type; + 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; + update t1 set f2='update 3.5.1.7'; + select * from t1; + select trigger_name from information_schema.triggers; + +#Cleanup + --disable_warnings + --error 0, 1360 + drop trigger trg5_1; + # The above trigger should be dropped since the name was trimmed. + drop trigger trg_abcdefghijklmnopqrstuvwxyz1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ; + drop table t1; + +#Section 3.5.1.8 +# Testcase: Ensure that any invalid trigger name is never accepted, and that an +# appropriate error message is returned when the name is rejected. +let $message= Testcase 3.5.1.8:; +--source include/show_msg.inc + + --error 1064 + CREATE TRIGGER trg12* before insert on tb3 for each row set new.f120 = 't'; + + --error 1064 + CREATE TRIGGER trigger before insert on tb3 for each row set new.f120 = 't'; + + --error 1064 + CREATE TRIGGER 100 before insert on tb3 for each row set new.f120 = 't'; + + --error 1064 + CREATE TRIGGER @@view before insert on tb3 for each row set new.f120 = 't'; + + --error 1064 + CREATE TRIGGER @name before insert on tb3 for each row set new.f120 = 't'; + + --error 1435 + CREATE TRIGGER tb3.trg6_1 BEFORE INSERT on test.tb3 + for each row set new.f120 ='X'; + + --disable_warnings + drop database if exists trig_db; + --enable_warnings + create database trig_db; + use trig_db; + eval create table t1 (f1 integer) engine = $engine_type; + + # Can't create a trigger in a different database + use test; + --error 1435 + CREATE TRIGGER trig_db.trg6_2 AFTER INSERT on tb3 + for each row set @ret_trg6_2 = 5; + + # Can't create a trigger refrencing a table in a different db + use trig_db; + --error 1435 + CREATE TRIGGER trg6_3 AFTER INSERT on test.tb3 + for each row set @ret_trg6_3 = 18; + + use test; + +#Cleanup + --disable_warnings + drop database trig_db; +# OBN - Although none of the above should have been created we should do a cleanup +# since if they have been created, not dropping them will affect following +# tests. + --error 0, 1360 + drop trigger trg6_1; + --error 0, 1360 + drop trigger trg6_3; + --enable_warnings + +#Section 3.5.1.9 +#Testcase: Ensure that a reference to a non-existent trigger is rejected with +# an appropriate error message. +let $message= Testcase 3.5.1.9:(cannot be inplemented at this point); +--source include/show_msg.inc + + +#Section 3.5.1.10 +#Testcase: Ensure that it is not possible to create two triggers with the same name on +# the same table +let $message= Testcase 3.5.1.10:; +--source include/show_msg.inc + + CREATE TRIGGER trg7_1 BEFORE UPDATE on tb3 for each row set new.f120 ='X'; + + --error 1359 + CREATE TRIGGER trg7_1 AFTER INSERT on tb3 for each row set @x ='Y'; + +#Cleanup + --disable_warnings + --error 0, 1360 + drop trigger trg7_1; + --enable_warnings + + +#Section 3.5.1.? +# Testcase: Ensure that it is not possible to create two or more triggers with +# the same name, provided each is associated with a different table. +let $message= Testcase 3.5.1.?:; +--source include/show_msg.inc + + --disable_warnings + drop table if exists t1; + drop table if exists t2; + --enable_warnings + eval create table t1 (f1 char(50), f2 integer) engine = $engine_type; + eval create table t2 (f1 char(50), f2 integer) engine = $engine_type; + + create trigger trig before insert on t1 + for each row set new.f1 ='trig t1'; + + --error 1359 + create trigger trig before update on t2 + for each row set new.f1 ='trig t2'; + + insert into t1 value ('insert to t1',1); + select * from t1; + update t1 set f1='update to t1'; + select * from t1; + insert into t2 value ('insert to t2',2); + update t2 set f1='update to t1'; + select * from t2; + +#Cleanup + --disable_warnings + drop table t1; + drop table t2; + --error 0, 1360 + drop trigger trig; + --enable_warnings + + +#Section 3.5.1.11 +# Testcase: Ensure that it is possible to create two or more triggers with +# the same name, provided each resides in a different database +let $message= Testcase 3.5.1.11:; +--source include/show_msg.inc + + --disable_warnings + drop database if exists trig_db1; + drop database if exists trig_db2; + drop database if exists trig_db3; + --enable_warnings + create database trig_db1; + create database trig_db2; + create database trig_db3; + use trig_db1; + eval create table t1 (f1 char(50), f2 integer) engine = $engine_type; + create trigger trig before insert on t1 + for each row set new.f1 ='trig1', @test_var1='trig1'; + use trig_db2; + eval create table t2 (f1 char(50), f2 integer) engine = $engine_type; + create trigger trig before insert on t2 + for each row set new.f1 ='trig2', @test_var2='trig2'; + use trig_db3; + eval create table t1 (f1 char(50), f2 integer) engine = $engine_type; + 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; + select * from t1; + select * from trig_db2.t2; + select * from trig_db3.t1; + select * from t1; + use test; + +#Cleanup + --disable_warnings + drop database trig_db1; + drop database trig_db2; + drop database trig_db3; + --enable_warnings + +########################################### +################ Section 3.5.2 ############ +# Check for the global nature of Triggers # +########################################### + +#Section 3.5.2.1 +# Test case: Ensure that if a trigger created without a qualifying database +# name belongs to the database in use at creation time. +#Section 3.5.2.2 +# Test case: Ensure that if a trigger created with a qualifying database name +# belongs to the database specified. +#Section 3.5.2.3 +# Test case: Ensure that if a trigger created with a qualifying database name +# does not belong to the database in use at creation time unless +# the qualifying database name identifies the database that is +# also in use at creation time. +let $message= Testcase 3.5.2.1/2/3:; +--source include/show_msg.inc + + + --disable_warnings + drop database if exists trig_db1; + drop database if exists trig_db2; + --enable_warnings + create database trig_db1; + create database trig_db2; + use trig_db1; + eval create table t1 (f1 char(50), f2 integer) engine = $engine_type; + eval create table trig_db2.t1 (f1 char(50), f2 integer) engine = $engine_type; + 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; + + 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; + +#Cleanup + --disable_warnings + drop database trig_db1; + drop database trig_db2; + +########################################### +################ Section 3.5.3 ############ +# Check for the global nature of Triggers # +########################################### + +# General setup to be used in all testcases of 3.5.3 +let $message= Testcase 3.5.3:; +--source include/show_msg.inc + + --disable_warnings + drop database if exists priv_db; + --enable_warnings + 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'); + +#Section 3.5.3.1 / 3.5.3.2 +# Test case: Ensure SUPER privilege is required to create a trigger +#Section 3.5.3.3 / 3.5.3.4 +# Test case: Ensure that root always has the SUPER privilege. +# OMR - No need to test this since SUPER priv is an existing one and not related +# or added for triggers (TP 2005-06-06) +#Section 3.5.3.5 / 3.5.3.6 +# Test case: Ensure that the SUPER privilege is required to drop a trigger. +let $message= Testcase 3.5.3.2/6:; +--source include/show_msg.inc + + 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; + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant SUPER on *.* to test_yesprivs@localhost; +# Adding the minimal priv to be able to set to the db + grant SELECT on priv_db.t1 to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + +let $message= Testcase 3.5.3.2:; +--source include/show_msg.inc + + connection no_privs; + select current_user; + use priv_db; + + --error 1227 + create trigger trg1_1 before INSERT on t1 for each row + set new.f1 = 'trig 3.5.3.2_1-no'; + + connection default; + use priv_db; + insert into t1 (f1) values ('insert 3.5.3.2-no'); + select f1 from t1; + + connection yes_privs; + select current_user; + use priv_db; + + create trigger trg1_2 before INSERT on t1 for each row + set new.f1 = 'trig 3.5.3.2_2-yes'; + + connection default; + use priv_db; + insert into t1 (f1) values ('insert 3.5.3.2-yes'); + select f1 from t1; + +let $message= Testcase 3.5.3.6:; +--source include/show_msg.inc + + connection no_privs; + use priv_db; + + --error 1227 + drop trigger trg1_2; + + connection default; + use priv_db; + insert into t1 (f1) values ('insert 3.5.3.6-yes'); + select f1 from t1; + + connection yes_privs; + use priv_db; + + drop trigger trg1_2; + + connection default; + use priv_db; + insert into t1 (f1) values ('insert 3.5.3.6-no'); + select f1 from t1; + +# Cleanup + --disable_warnings + connection default; + --error 0, 1360 + drop trigger trg1_2; + disconnect no_privs; + disconnect yes_privs; + --enable_warnings + + +#Section 3.5.3.7 +# Test case: Ensure that use of the construct "SET NEW. <column name> = <value>" +# fails at CREATE TRIGGER time, if the current user does not have the +# UPDATE privilege on the column specified +# Note: As a result of bug 8884 the triggers are actually created. +# Disabled because of bug 8884 + +# --- 3.5.3.7a - Privs set on a global level +let $message=Testcase 3.5.3.7a:; +--source include/show_msg.inc + + 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; + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant SUPER, UPDATE on *.* to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs_424a,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs_424a,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + + connection no_privs_424a; + select current_user; + use priv_db; + show grants; + select f1 from t1; + +let $message= Trigger create disabled - should fail - Bug 8884; +--source include/show_msg.inc +# --error 1227 +# create trigger trg4a_1 before INSERT on t1 for each row +# set new.f1 = 'trig 3.5.3.7-1a'; + + connection default; + insert into t1 (f1) values ('insert 3.5.3.7-1a'); + select f1 from t1; + --error 0, 1360 + drop trigger trg4a_1; + + connection yes_privs_424a; + use priv_db; + select current_user; + show grants; + create trigger trg4a_2 before INSERT on t1 for each row + set new.f1 = 'trig 3.5.3.7-2a'; + + connection default; + insert into t1 (f1) values ('insert 3.5.3.7-2b'); + select f1 from t1; + +# Cleanup + --disable_warnings + drop trigger trg4a_2; + disconnect no_privs_424a; + disconnect yes_privs_424a; + --enable_warnings + +# --- 3.5.3.7b - Privs set on a database level +let $message= Testcase 3.5.3.7b:; +--source include/show_msg.inc + + 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; + + 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; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs_424b,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs_424b,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + + connection no_privs_424b; + show grants; + use priv_db; + +let $message= Trigger create disabled - should fail - Bug 8884; +--source include/show_msg.inc +# --error 1227 +# create trigger trg4b_1 before UPDATE on t1 for each row +# set new.f1 = 'trig 3.5.3.7-1b'; + + connection default; + insert into t1 (f1) values ('insert 3.5.3.7-1b'); + select f1 from t1; + update t1 set f1 = 'update 3.5.3.7-1b' where f1 = 'insert 3.5.3.7-1b'; + select f1 from t1; + --error 0, 1360 + drop trigger trg4b_1; + + connection yes_privs_424b; + show grants; + use priv_db; + create trigger trg4b_2 before UPDATE on t1 for each row + set new.f1 = 'trig 3.5.3.7-2b'; + + connection default; + insert into t1 (f1) values ('insert 3.5.3.7-2b'); + select f1 from t1; + update t1 set f1 = 'update 3.5.3.7-2b' where f1 = 'insert 3.5.3.7-2b'; + select f1 from t1; +# Cleanup + --disable_warnings + drop trigger trg4b_2; + disconnect no_privs_424b; + disconnect yes_privs_424b; + --enable_warnings + +# --- 3.5.3.7c - Privs set on a table level +let $message= Testcase 3.5.3.7c; +--source include/show_msg.inc + + 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; + + 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; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs_424c,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs_424c,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + + connection no_privs_424c; + show grants; + use priv_db; + +let $message= Trigger create disabled - should fail - Bug 8884; +--source include/show_msg.inc +# --error 1227 +# create trigger trg4c_1 before INSERT on t1 for each row +# set new.f1 = 'trig 3.5.3.7-1c'; + + connection default; + insert into t1 (f1) values ('insert 3.5.3.7-1c'); + select f1 from t1; + --error 0, 1360 + drop trigger trg4c_1; + + connection yes_privs_424c; + show grants; + use priv_db; + create trigger trg4c_2 before INSERT on t1 for each row + set new.f1 = 'trig 3.5.3.7-2c'; + + connection default; + insert into t1 (f1) values ('insert 3.5.3.7-2c'); + select f1 from t1; + +# Cleanup + --disable_warnings + drop trigger trg4c_2; + disconnect no_privs_424c; + disconnect yes_privs_424c; + --enable_warnings + +# --- 3.5.3.7d - Privs set on a column level +--disable_query_log +let $message= Testcase 3.5.3.7d:; +--enable_query_log +--source include/show_msg.inc + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; + grant SUPER on *.* to test_noprivs@localhost; +# There is no ALL privs on the column level + grant SELECT (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost; + show grants for 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; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs_424d,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs_424d,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + + connection no_privs_424d; + show grants; + use priv_db; +let $message= Trigger create disabled - should fail - Bug 8884; +--source include/show_msg.inc +# --error 1227 +# create trigger trg4d_1 before INSERT on t1 for each row +# set new.f1 = 'trig 3.5.3.7-1d'; + + connection default; + insert into t1 (f1) values ('insert 3.5.3.7-1d'); + select f1 from t1; + --error 0, 1360 + drop trigger trg4d_1; + + connection yes_privs_424d; + show grants; + use priv_db; + create trigger trg4d_2 before INSERT on t1 for each row + set new.f1 = 'trig 3.5.3.7-2d'; + + connection default; + insert into t1 (f1) values ('insert 3.5.3.7-2d'); + select f1 from t1; + +# Cleanup + --disable_warnings + drop trigger trg4d_2; + disconnect no_privs_424d; + disconnect yes_privs_424d; + --enable_warnings + +#Section 3.5.3.8 +# Test case: Ensure that use of the construct "SET <target> = NEW. <Column name>" fails +# at CREATE TRIGGER time, if the current user does not have the SELECT privilege +# on the column specified. + +# --- 3.5.3.8a - Privs set on a global level +let $message= Testcase 3.5.3.8a:; +--source include/show_msg.inc + + 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; + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost; + grant SUPER, SELECT on *.* to test_yesprivs@localhost; + show grants for test_yesprivs@localhost; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs_425a,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs_425a,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + + connection no_privs_425a; + select current_user; + use priv_db; + show grants; + +let $message= Trigger create disabled - should fail - Bug 8887; +--source include/show_msg.inc +# --error 1227 +# create trigger trg5a_1 before INSERT on t1 for each row +# set @test_var = new.f1; + + connection default; + set @test_var = 'before trig 3.5.3.8-1a'; + select @test_var; + insert into t1 (f1) values ('insert 3.5.3.8-1a'); + select @test_var; + --error 0, 1360 + drop trigger trg5a_1; + + connection yes_privs_425a; + use priv_db; + select current_user; + show grants; + create trigger trg5a_2 before INSERT on t1 for each row + set @test_var= new.f1; + + connection default; + set @test_var= 'before trig 3.5.3.8-2a'; + select @test_var; + insert into t1 (f1) values ('insert 3.5.3.8-2a'); + select @test_var; + +# Cleanup + --disable_warnings + drop trigger trg5a_2; + disconnect no_privs_425a; + disconnect yes_privs_425a; + --enable_warnings + +# --- 3.5.3.8b - Privs set on a database level +let $message= Testcase: 3.5.3.8b; +--source include/show_msg.inc + + 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; + + 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; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs_425b,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs_425b,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + + connection no_privs_425b; + show grants; + use priv_db; + +let $message= Trigger create disabled - should fail - Bug 8887; +--source include/show_msg.inc +# --error 1227 +# create trigger trg5b_1 before UPDATE on t1 for each row +# set @test_var= new.f1; + + connection default; + set @test_var= 'before trig 3.5.3.8-1b'; + insert into t1 (f1) values ('insert 3.5.3.8-1b'); + select @test_var; + update t1 set f1= 'update 3.5.3.8-1b' where f1 = 'insert 3.5.3.8-1b'; + select @test_var; + --error 0, 1360 + drop trigger trg5b_1; + + connection yes_privs_425b; + show grants; + use priv_db; + create trigger trg5b_2 before UPDATE on t1 for each row + set @test_var= new.f1; + + connection default; + set @test_var= 'before trig 3.5.3.8-2b'; + insert into t1 (f1) values ('insert 3.5.3.8-2b'); + select @test_var; + update t1 set f1= 'update 3.5.3.8-2b' where f1 = 'insert 3.5.3.8-2b'; + select @test_var; +# Cleanup + --disable_warnings + drop trigger trg5b_2; + disconnect no_privs_425b; + disconnect yes_privs_425b; + --enable_warnings + +# --- 3.5.3.8c - Privs set on a table level +let $message= Testcase 3.5.3.8c:; +--source include/show_msg.inc + + 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; + + 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; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs_425c,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs_425c,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + + connection no_privs_425c; + show grants; + use priv_db; + +let $message= Trigger create disabled - should fail - Bug 8887; +--source include/show_msg.inc +# --error 1227 +# create trigger trg5c_1 before INSERT on t1 for each row +# set @test_var= new.f1; + + connection default; + set @test_var= 'before trig 3.5.3.8-1c'; + insert into t1 (f1) values ('insert 3.5.3.8-1c'); + select @test_var; + --error 0, 1360 + drop trigger trg5c_1; + + connection yes_privs_425c; + show grants; + use priv_db; + create trigger trg5c_2 before INSERT on t1 for each row + set @test_var= new.f1; + + connection default; + set @test_var='before trig 3.5.3.8-2c'; + insert into t1 (f1) values ('insert 3.5.3.8-2c'); + select @test_var; +# Cleanup + --disable_warnings + drop trigger trg5c_2; + disconnect no_privs_425c; + disconnect yes_privs_425c; + --enable_warnings + +# --- 3.5.3.8d - Privs set on a column level +let $message=Testcase: 3.5.3.8d:; +--source include/show_msg.inc + + revoke ALL PRIVILEGES, GRANT OPTION FROM test_noprivs@localhost; + grant SUPER on *.* to test_noprivs@localhost; +# There is no ALL prov on the column level + grant UPDATE (f1), INSERT (f1) on priv_db.t1 to test_noprivs@localhost; + show grants for 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; + + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (no_privs_425d,localhost,test_noprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + --replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK + connect (yes_privs_425d,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK); + connection default; + + connection no_privs_425d; + show grants; + use priv_db; +let $message= Trigger create disabled - should fail - Bug 8887; +--source include/show_msg.inc +# --error 1227 +# create trigger trg5d_1 before INSERT on t1 for each row +# set @test_var= new.f1; + + connection default; + set @test_var='before trig 3.5.3.8-1d'; + insert into t1 (f1) values ('insert 3.5.3.8-1d'); + select @test_var; + --error 0, 1360 + drop trigger trg5d_1; + + connection yes_privs_425d; + show grants; + use priv_db; + create trigger trg5d_2 before INSERT on t1 for each row + set @test_var= new.f1; + + connection default; + set @test_var='before trig 3.5.3.8-2d'; + insert into t1 (f1) values ('insert 3.5.3.8-2d'); + select @test_var; + +# Cleanup 3.5.3.8 + --disable_warnings + drop trigger trg5d_2; + --enable_warnings + +# Cleanup 3.5.3 + --disable_warnings + drop database if exists priv_db; + drop user test_yesprivs@localhost; + drop user test_noprivs@localhost; + drop user test_noprivs; + --enable_warnings + +#################################### +############ Section 3.5.4 ######### +# Drop Trigger Checkes: # +#################################### +let $message= Testcase 3.5.4:; +--source include/show_msg.inc + + connection default; + use test; + +#Section 3.5.4.1 +# Testcase: Ensure that the DROP TRIGGER statement cleanly drops its target trigger. +let $message= Testcase 3.5.4.1:; +--source include/show_msg.inc + + connection con_super; + create database db_drop; + Use db_drop; + eval create table t1 (f1 char(30)) engine=$engine_type; + 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'; + connection con_general; + Use db_drop; + Insert into t1 values ('Insert error 3.5.4.1'); + Select * from t1; + connection con_super; + drop trigger trg1; + select trigger_schema, trigger_name, event_object_table + from information_schema.triggers; + connection con_general; + Insert into t1 values ('Insert no trigger 3.5.4.1'); + Select * from t1; + +#Cleanup + --disable_warnings + connection con_super; + --disable_warnings + --error 0,1360 + drop trigger trg1; + drop database if exists db_drop; + revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; + --enable_warnings + +#Section 3.5.4.2 +# Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate error +# message, if the trigger name does not exist. +let $message= Testcase 3.5.4.2:; +--source include/show_msg.inc + + connection con_super; + create database db_drop2; + Use db_drop2; + --disable_warnings + drop table if exists t1_432 ; + --enable_warnings + eval create table t1_432 (f1 char (30)) engine=$engine_type; + --error 1360 + Drop trigger tr_does_not_exit; +#cleanup + --disable_warnings + drop table if exists t1_432 ; + drop database if exists db_drop2; + --enable_warnings + +#Section 3.5.4.3 +# Test case: Ensure that DROP TRIGGER <trigger name> fails, with an appropriate +# error message, if <trigger name> is not a qualified name. +let $message= Testcase 3.5.4.3:; +--source include/show_msg.inc + + connection con_super; + create database db_drop3; + Use db_drop3; + --disable_warnings + drop table if exists t1_433 ; + drop table if exists t1_433a ; + --enable_warnings + eval create table t1_433 (f1 char (30)) engine=$engine_type; + eval create table t1_433a (f1a char (5)) engine=$engine_type; + + CREATE TRIGGER trg3 BEFORE INSERT on t1_433 for each row + set new.f1 = 'Trigger 3.5.4.3'; + +# Using table + --error 1064 + Drop trigger t1.433.trg3; + +# Using database.table + --error 1064 + Drop trigger db_drop3.t1.433.trg3; + +# wrong database + --error 1360 + Drop trigger mysql.trg3; + +# database does not exist + --error 1360 + Drop trigger tbx.trg3; + +#cleanup + Drop trigger db_drop3.trg3; + drop table if exists t1_433; + drop table if exists t1_433a; + drop database if exists db_drop3; + +#Section 3.5.4.4 +# Test case: Ensure that when a database is dropped, all triggers created within +# that database are also cleanly dropped. +let $message= Testcase 3.5.4.4:; +--source include/show_msg.inc + + connection con_super; + create database db_drop4; + Use db_drop4; + eval create table t1 (f1 char(30)) engine=$engine_type; + 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'; + connection con_general; + Use db_drop4; + Insert into t1 values ('Insert 3.5.4.4'); + Select * from t1; + connection con_super; + Drop database db_drop4; + Show databases; + select trigger_schema, trigger_name, event_object_table + from information_schema.triggers + where information_schema.triggers.trigger_name='trg4'; + create database db_drop4; + Use db_drop4; + eval create table t1 (f1 char(30)) engine=$engine_type; + grant INSERT, SELECT on db_drop4.t1 to test_general; + connection con_general; + Insert into t1 values ('2nd Insert 3.5.4.4'); + Select * from t1; + +#Cleanup + connection con_super; + --disable_warnings + --error 1360 + drop trigger trg4; + drop database if exists db_drop4; + --enable_warnings + revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; + +#Section 3.5.4.5 +# Test case: Ensure that when a table is dropped, all triggers for which it is the +# subject table are also cleanly dropped. +let $message= Testcase 3.5.4.5:; +--source include/show_msg.inc + + connection con_super; + create database db_drop5; + Use db_drop5; + eval create table t1 (f1 char(50)) engine=$engine_type; + 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'; + connection con_general; + Use db_drop5; + Insert into t1 values ('Insert 3.5.4.5'); + Select * from t1; + connection con_super; + Drop table t1; + Show tables; + select trigger_schema, trigger_name, event_object_table + from information_schema.triggers + where information_schema.triggers.trigger_name='trg5'; + eval create table t1 (f1 char(50)) engine=$engine_type; + grant INSERT, SELECT on t1 to test_general; + connection con_general; + Insert into t1 values ('2nd Insert 3.5.4.5'); + Select * from t1; + +#Cleanup + connection con_super; + --disable_warnings + --error 1360 + drop trigger trg5; + drop database if exists db_drop5; + --enable_warnings + revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; + + +################################## +######### Section 3.5.5 ########## +# Checks on the Subject Table # +################################## + +let $message= Testcase 3.5.5:; +--source include/show_msg.inc + + connection default; + use test; + +#Section 3.5.5.1 +# Test case: Ensure that, if CREATE TRIGGER is executed with a non-existent +# subject table, the statement fails with an appropriate error message. +let $message= Testcase 3.5.5.1:; +--source include/show_msg.inc + + --error 1146 + Create trigger trg1 before INSERT on t100 for each row set new.f2=1000; + + +#Section 3.5.5.2 +# Test case: Ensure that, if CREATE TRIGGER is executed with a temporary table +# as the subject table, the statement fails with an appropriate error message. +let $message= Testcase 3.5.5.2:; +--source include/show_msg.inc + + Create temporary table t1_temp (f1 bigint signed, f2 bigint unsigned); + + --error 1361 + Create trigger trg2 before INSERT + on t1_temp for each row set new.f2=9999; + +#Cleanup + --disable_warnings + drop table t1_temp; + --enable_warnings + + +#Section 3.5.5.3 +# Test case: Ensure that, if CREATE TRIGGER is executed with a view as the subject +# table, the statement fails with an appropriate error message. +let $message= Testcase 3.5.5.3:; +--source include/show_msg.inc + + Create view vw3 as select f118 from tb3; + +# OBN Not sure why the server is returning error 1347 + --error 1347 + Create trigger trg3 before INSERT + on vw3 for each row set new.f118='s'; + +#Cleanup + --disable_warnings + drop view vw3; + --enable_warnings + + +#Section 3.5.5.4 +# Test case: Ensure that, if CREATE TRIGGER is executed with a table that resides +# in a different database than in which the trigger will reside, the +# statement fails with an appropriate error message; that is, ensure that +# the trigger and its subject table must reside in the same database. +let $message= Testcase 3.5.5.4:; +--source include/show_msg.inc + + connection con_super; + create database dbtest_one; + create database dbtest_two; + use dbtest_two; + create table t2 (f1 char(15)); + use dbtest_one; + --error 1435 + create trigger trg4 before INSERT + on dbtest_two.t2 for each row set new.f1='trig 3.5.5.4'; + grant INSERT, SELECT on dbtest_two.t2 to test_general; + grant SELECT on dbtest_one.* to test_general; + connection con_general; + use dbtest_two; + Insert into t2 values ('1st Insert 3.5.5.4'); + Select * from t2; + use dbtest_one; + Insert into dbtest_two.t2 values ('2nd Insert 3.5.5.4'); + Select * from dbtest_two.t2; + +#Cleanup + connection con_super; + --disable_warnings + revoke ALL PRIVILEGES, GRANT OPTION FROM 'test_general'@'localhost'; + DROP DATABASE if exists dbtest_one; + drop database if EXISTS dbtest_two; + --enable_warnings + +##################################### +########### Section 3.5.6 ########### +# Check on the Trigger Action Time # +##################################### + +let $message= Testcase 3.5.6:; +--source include/show_msg.inc + + connection default; + use test; + +#Section 3.5.6.1 +# Test case: Ensure that a trigger definition can specify a trigger action time of BEFORE. +# See section 3.5.1.1 +let $message= Testcase 3.5.6.1 (see Testcase 3.5.1.1); +--source include/show_msg.inc + +#Section 3.5.6.2 +# Test case: Ensure that a trigger definition can specify a trigger action time of AFTER. +# See section 3.5.1.1 +let $message= Testcase 3.5.6.2 (see Testcase 3.5.1.1); +--source include/show_msg.inc + +#Section 3.5.6.3 +# Test case: Ensure that a trigger definition that specifies a trigger action +# time that is not either BEFORE or AFTER fails, with an appropriate +# error message, at CREATE TRIGGER time. +let $message= Testcase 3.5.6.3:; +--source include/show_msg.inc + + --error 1064 + Create trigger trg3_1 DURING UPDATE on tb3 for each row set new.f132=25; + --error 1064 + Create trigger trg3_2 TIME INSERT on tb3 for each row set new.f132=15; + +#Cleanup +# OBN - Although none of the above should have been created we should do a cleanup +# since if they have been created, not dropping them will affect following +# tests. + --disable_warnings + --error 0, 1360 + drop trigger tb3.trg3_1; + --error 0, 1360 + drop trigger tb3.trg3_2; + --enable_warnings + +#Section 3.5.6.4 +# Test case: Ensure that a trigger defined with a trigger action time of BEFORE +# always executes its triggered action immediately before the trigger event. +# See section 3.5.1.1 +let $message= Testcase 3.5.6.4 (see Testcase 3.5.1.1); +--source include/show_msg.inc + +#Section 3.5.6.5 +# Test case: Ensure that a trigger defined with a trigger action time of AFTER +# always executes its triggered action immediately after the trigger event. +let $message= Testcase 3.5.6.5 (see Testcase 3.5.1.1); +--source include/show_msg.inc + +############################# +####### Section 3.5.7 ####### +# Check on Trigger Event # +############################# + +#Section 3.5.7.1 +#Test case: Ensure that a trigger definition can specify a trigger event of INSERT. +let $message= Testcase 3.5.7.1 (see Testcase 3.5.1.1); +--source include/show_msg.inc + +#Section 3.5.7.2 +# Test case: Ensure that a trigger definition can specify a trigger event of UPDATE. +let $message= Testcase 3.5.7.2 (see Testcase 3.5.1.1); +--source include/show_msg.inc + +#Section 3.5.7.3 +# Test case: Ensure that a trigger definition can specify a trigger event of DELETE. +let $message= Testcase 3.5.7.3 (see Testcase 3.5.1.1); +--source include/show_msg.inc + +#Section 3.5.7.4 +# Test case: Ensure that a trigger definition that specifies a trigger event that +# is not either INSERT, UPDATE or DELETE fails, with an appropriate error +# message, at CREATE TRIGGER time. +let $message= Testcase 3.5.7.4:; +--source include/show_msg.inc + + --error 1064 + Create trigger trg4_1 BEFORE SELECT on tb3 for each row set new.f132=5; + --error 1064 + Create trigger trg4_2 AFTER VALUE on tb3 for each row set new.f132=1; + +#Cleanup +# OBN - Although none of the above should have been created we should do a cleanup +# since if they have been created, not dropping them will affect following +# tests. + --disable_warnings + --error 0, 1360 + drop trigger tb3.trg4_1; + --error 0, 1360 + drop trigger tb3.trg4_2; + --enable_warnings + +#Section 3.5.7.5 / 3.5.7.6 +# Test case: Ensure that it is not possible to create multiple BEFORE INSERT triggers +# on the same table, even if the triggers have different names / different +# triggered actions. +let $message= Testcase 3.5.7.5 / 3.5.7.6:; +--source include/show_msg.inc + + Create trigger trg5_1 BEFORE INSERT + on tb3 for each row set new.f122='Trigger1 3.5.7.5/6'; + + --error 1359 + Create trigger trg5_2 BEFORE INSERT + on tb3 for each row set new.f122='Trigger2 3.5.7.5'; + + 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'; + 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'; + +#Cleanup + --disable_warnings + drop trigger trg5_1; + --error 0, 1360 + drop trigger trg5_2; + delete from tb3 where f121='Test 3.5.7.5/6'; + --enable_warnings + + +#Section 3.5.7.7 / 3.5.7.8 +# Test case: Ensure that it is not possible to create multiple AFTER INSERT triggers +# on the same table, even if the triggers have different names / different +# triggered actions. +let $message= Testcase 3.5.7.7 / 3.5.7.8:; +--source include/show_msg.inc + + 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'; + + --error 1359 + Create trigger trg6_2 AFTER INSERT + on tb3 for each row set @test_var='Trigger2 3.5.7.7'; + + select @test_var; + 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'; + select @test_var; + 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'; + select @test_var; + +#Cleanup + --disable_warnings + drop trigger trg6_1; + --error 0, 1360 + drop trigger trg6_2; + delete from tb3 where f121='Test 3.5.7.7/8'; + --enable_warnings + + +#Section 3.5.7.9 / 3.5.7.10 +# Test case: Ensure that it is not possible to create multiple BEFORE UPDATE triggers +# on the same table, even if the triggers have different names / different +# triggered actions. +let $message= Testcase 3.5.7.9/10:; +--source include/show_msg.inc + + Create trigger trg7_1 BEFORE UPDATE + on tb3 for each row set new.f122='Trigger1 3.5.7.9/10'; + + --error 1359 + Create trigger trg7_2 BEFORE UPDATE + on tb3 for each row set new.f122='Trigger2 3.5.7.9'; + + 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'; + 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'; + +#Cleanup + --disable_warnings + drop trigger trg7_1; + --error 0, 1360 + drop trigger trg7_2; + delete from tb3 where f121='Test 3.5.7.9/10'; + +#Section 3.5.7.11 / 3.5.7.12 +# Test case: Ensure that it is not possible to create multiple AFTER UPDATE triggers +# on the same table, even if the triggers have different names / different +# triggered actions. +let $message= Testcase 3.5.7.11/12:; +--source include/show_msg.inc + + 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'; + + --error 1359 + Create trigger trg8_2 AFTER UPDATE + on tb3 for each row set @test_var='Trigger2 3.5.7.11'; + + + select @test_var; + Insert into tb3 (f121,f122) values ('Test 3.5.7.11/12','Insert 3.5.7.11/12'); + select @test_var; + Select f121,f122 from tb3 where f121='Test 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'; + select @test_var; + delete from tb3 where f121='Test 3.5.7.11/12'; + +#Cleanup + --disable_warnings + drop trigger trg8_1; + --error 0, 1360 + drop trigger trg8_2; + delete from tb3 where f121='Test 3.5.7.11/12'; + +#Section 3.5.7.13 / 3.5.7.14 +# Test case: Ensure that it is not possible to create multiple BEFORE DELETE triggers +# on the same table, even if the triggers have different names / different +# triggered actions. +let $message= Testcase 3.5.7.13/14:; +--source include/show_msg.inc + + set @test_var=1; + Create trigger trg9_1 BEFORE DELETE + on tb3 for each row set @test_var=@test_var+1; + + --error 1359 + Create trigger trg9_2 BEFORE DELETE + on tb3 for each row set @test_var=@test_var+10; + + select @test_var; + 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'; + select @test_var; + delete from tb3 where f121='Test 3.5.7.13/14'; + Select f121,f122 from tb3 where f121='Test 3.5.7.13/14'; + select @test_var; + delete from tb3 where f121='Test 3.5.7.13/14'; + select @test_var; + +#Cleanup + --disable_warnings + drop trigger trg9_1; + --error 0, 1360 + drop trigger trg9_2; + delete from tb3 where f121='Test 3.5.7.13/14'; + +#Section 3.5.7.15 / 3.5.7.16 +# Test case: Ensure that it is not possible to create multiple AFTER DELETE triggers +# on the same table, even if the triggers have different names / different +# triggered actions. +let $message= Testcase 3.5.7.15/16:; +--source include/show_msg.inc + + set @test_var=1; + Create trigger trg_3_406010_1 AFTER DELETE + on tb3 for each row set @test_var=@test_var+5; + + --error 1359 + Create trigger trg_3_406010_2 AFTER DELETE + on tb3 for each row set @test_var=@test_var+50; + + --error 1359 + Create trigger trg_3_406010_1 AFTER INSERT + on tb3 for each row set @test_var=@test_var+1; + + select @test_var; + 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'; + select @test_var; + delete from tb3 where f121='Test 3.5.7.15/16'; + Select f121,f122 from tb3 where f121='Test 3.5.7.15/16'; + select @test_var; + delete from tb3 where f121='Test 3.5.7.15/16'; + select @test_var; + +#Cleanup + --disable_warnings + drop trigger trg_3_406010_1; + --error 0, 1360 + drop trigger trg_3_406010_2; + delete from tb3 where f121='Test 3.5.7.15/16'; + --enable_warnings + + +#Section 3.5.7.17 +# Test case: Ensure that it is possible to have a BEFORE INSERT, an AFTER INSERT, +# a BEFORE UPDATE, an AFTER UPDATE, a BEFORE DELETE, and an AFTER DELETE +# trigger on the same table; that is, ensure that every persistent base +# table may be the subject table for exactly six triggers +let $message= Testcase 3.5.7.17 (see Testcase 3.5.1.1); +--source include/show_msg.inc + + +################################# +####### Section 3.5.8 ########### +# Checks on Triggered Actions # +################################# + +#Section 3.5.8.1 +# Testcase: Ensure that the triggered action of every trigger always executes +# correctly and the results in all expected changes made to the database +let $message= Testcase 3.5.8.1: (implied in previous tests); +--source include/show_msg.inc +# OBN - FIXME - Missing 3.5.8.1 need to add + +#Section 3.5.8.2 +# Testcase: Ensure that the triggered actions of every trigger never results +# in an unexpected change made to the database. +let $message= Testcase 3.5.8.2: (implied in previous tests); +--source include/show_msg.inc + + +#Section 3.5.8.3 / 3.5.8.4 +#Test case: Ensure that the triggered action can any valid SQL statement / set +# of valid SQL statements, provided the statements are written within +# a BEGIN/END compound statement construct +# OBN - At this point the tests focuses on the the INSERT/UPDATE/DELETE SQL statements +# as there are the most likely to be used in triggers +let $message= Testcase 3.5.8.3/4:; +--source include/show_msg.inc + +# creating test tables to perform the trigger SQL on + connection con_super; + 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; + eval 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=$engine_type; + eval 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=$engine_type; + eval 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=$engine_type; + 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); + +let $message= 3.5.8.4 - multiple SQL; +--source include/show_msg.inc +# Trigger definition - multiple SQL + use test; + delimiter //; + 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// + delimiter ;// + +# Test trigger execution - multiple SQL + connection con_general; + 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'; + select * from db_test.t1_i; + select * from db_test.t1_u; + select * from db_test.t1_d; + select @test_var; + + +let $message= 3.5.8.4 - single SQL - insert; +--source include/show_msg.inc +# Trigger definition - single SQL Insert + connection con_super; + 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); + +# Trigger exeution - single SQL Insert + connection con_general; + 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%'; + select * from db_test.t1_i; + + +let $message= 3.5.8.4 - single SQL - update; +--source include/show_msg.inc +# Trigger definition - single SQL update + connection con_super; + 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; + +# Trigger exeution - single SQL - update; + connection con_general; + 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%'; + select * from db_test.t1_u; + + +let $message= 3.5.8.3/4 - single SQL - delete; +--source include/show_msg.inc +# Trigger definition - single SQL delete + connection con_super; + drop trigger trg3; + Create trigger trg4 AFTER UPDATE on tb3 for each row + delete from db_test.t1_d where d136= new.f136; + +# Trigger exeution - single SQL delete + connection con_general; +#lock tables tb3 write, db_test.t1_i write, db_test.t1_u write, db_test.t1_d write; + update tb3 set f120='D', f136=444, + f122='Test 3.5.8.4-Single Delete' + where f122='Test 3.5.8.4-Single Update'; +#unlock tables; + Select f120, f122, f136, f144, f163 from tb3 where f122 like 'Test 3.5.8.4%'; + select * from db_test.t1_d; + + +let $message= 3.5.8.3/4 - single SQL - select; +--source include/show_msg.inc +# Trigger definition - single SQL select + connection con_super; + 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; + +# Trigger exeution - single SQL select + connection con_general; + 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%'; + select @test_var; + +#Cleanup + connection default; + --disable_warnings + 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'; + --enable_warnings + + +#Section 3.5.8.5 (IF) +# Test case: Ensure that the stored procedure-specific flow control statement like IF +# works correctly when it is a part of the triggered action portion of a +# trigger definition. +let $message= Testcase 3.5.8.5 (IF):; +--source include/show_msg.inc + + delimiter //; + 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// + delimiter ;// + + 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'; + 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'; + 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'; + 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'; + + delimiter //; + --error 1064 + create trigger trg3 before update on tb3 for each row + BEGIN + ELSEIF new.f120='2' then + END IF; + END// + --error 0, 1360 + drop trigger trg3// + + --error 1064 + 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// + delimiter ;// + --error 0, 1360 + drop trigger trg4; + +#Cleanup + --disable_warnings + drop trigger trg2; + delete from tb3 where f121='Test 3.5.8.5-if'; + --enable_warnings + + +#Section 3.5.8.5 (CASE) +# Test case: Ensure that the stored procedure-specific flow control statement +# like CASE works correctly when it is a part of the triggered action +# portion of a trigger definition. +let $message= Testcase 3.5.8.5-case:; +--source include/show_msg.inc + + delimiter //; + 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// + delimiter ;// + + 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'; + 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'; + 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'; + Insert into tb3 (f120, f122, f136) + values ('d', 'Test 3.5.8.5-case', 152); + select f120, f122, f136, f144, @test_var + from tb3 where f122 = 'Test 3.5.8.5-case'; + Insert into tb3 (f120, f122, f136, f144) + values ('e', 'Test 3.5.8.5-case', 200, 8); + select f120, f122, f136, f144, @test_var + from tb3 where f122 = 'Test 3.5.8.5-case'; + --error 0, 1339 + 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'; + + delimiter //; + --error 1064 + create trigger trg3a before update on tb3 for each row + BEGIN + CASE + when new.f136<100 then set new.f120='p'; + END// + delimiter ;// + + --error 0, 1360 + drop trigger trg3a; + +#Cleanup + --disable_warnings + drop trigger trg3; + delete from tb3 where f121='Test 3.5.8.5-case'; + --enable_warnings + +#Section 3.5.8.5 (LOOP) +# Test case: Ensure that the stored procedure-specific flow control +# statement like LOOP / LEAVE work correctly when they are +# part of the triggered action portion of a trigger definition. +let $message= Testcase 3.5.8.5-loop/leave:; +--source include/show_msg.inc + + delimiter //; + 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// + delimiter ;// + Insert into tb3 (f122, f136, f144) + values ('Test 3.5.8.5-loop', 2, 8); + select @counter, @flag; + Insert into tb3 (f122, f136, f144) + values ('Test 3.5.8.5-loop', 11, 8); + select @counter, @flag; + + + delimiter //; + + --error 1064 + Create trigger trg4_2 after update on tb3 for each row + BEGIN + Label1: loop + set @counter=@counter+1; + END; + END// + delimiter ;// + --error 0, 1360 + drop trigger trg4_2; + +#Cleanup + --disable_warnings + drop trigger trg4; + delete from tb3 where f122='Test 3.5.8.5-loop'; + --enable_warnings + +#Section 3.5.8.5 (REPEAT ITERATE) +#Testcase: Ensure that the stored procedure-specific flow control statements +# like REPEAT work correctly when they are part of the triggered action +# portion of a trigger definition. +let $message= Testcase 3.5.8.5-repeat:; +--source include/show_msg.inc + + delimiter //; + 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// + delimiter ;// + + set @counter1= 0, @counter2= 0; + Insert into tb3 (f122, f136) + values ('Test 3.5.8.5-repeat', 13); + select @counter1, @counter2; + + + delimiter //; + --error 1064 + Create trigger trg6_2 after update on tb3 for each row + BEGIN + REPEAT + SET @counter2 = @counter2 + 1; + END// + delimiter ;// + +#Cleanup + --disable_warnings + drop trigger trg6; + delete from tb3 where f122='Test 3.5.8.5-repeat'; + --enable_warnings + + +#Section 3.5.8.5 (WHILE) +# Test case: Ensure that the stored procedure-specific flow control +# statements WHILE, work correctly when they are part of +# the triggered action portion of a trigger definition. +let $message= Testcase 3.5.8.5-while:; +--source include/show_msg.inc + + delimiter //; + 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// + delimiter ;// + + set @counter1= 0, @counter2= 0; + Insert into tb3 (f122, f136) + values ('Test 3.5.8.5-while', 7); + select @counter1, @counter2; + delimiter //; + --error 1064 + Create trigger trg7_2 after update on tb3 for each row + BEGIN + WHILE @counter1 < new.f136 + SET @counter1 = @counter1 + 1; + END// + delimiter ;// + +#Cleanup + --disable_warnings + delete from tb3 where f122='Test 3.5.8.5-while'; + drop trigger trg7; + --enable_warnings + +#Section 3.5.8.6 +# Test case: Ensure that a trigger definition that includes a CALL to a stored +# procedure fails, at CREATE TRIGGER time, with an appropriate error +# message +# OBN - requirement void since allowed +# Fails due to Bug 9909 the bug allows the trigger to be created +# and fails in execution time +let $message= Testcase 3.5.8.6: (requirement void); +--source include/show_msg.inc + + +#Section 3.5.8.7 +# Test case: Ensure that a trigger definition that includes a +# transaction-delimiting statement (e.g. COMMIT, +# ROLLBACK, START TRANSACTION) fails, at CREATE TRIGGER +# time, with an appropriate error message. +# OBN - Fails due to Bug ____ +let $message= Testcase 3.5.8.7: (Disabled as a result of bug _____); +--source include/show_msg.inc + +# --error 1314 +# Create trigger trg9_1 before update on tb3 for each row +# BEGIN +# Start transaction; +# Set new.f120='U'; +# Commit; +# END; + +# --error 1314 +# Create trigger trg9_2 before delete on tb3 for each row +# BEGIN +# Start transaction; +# Set @var2=old.f120; +# Rollback; +# END; + +################################# +####### Section 3.5.9 ########### +# Checks on old and new rows # +################################# + +#Section 3.5.9.1 +#Test case: Ensure that every trigger executes its triggered action on each row +# that meets the conditions stated in the trigger definition. +#Section 3.5.9.2 +#Testcase: Ensure that a trigger never executes its triggered action on any row +# that doesn't meet the conditions stated in the trigger definition. +let $message= Testcase 3.5.9.1/2:; +--source include/show_msg.inc + + Create trigger trg1 BEFORE UPDATE on tb3 for each row + set new.f142 = 94087, @counter=@counter+1; +--disable_query_log + select count(*) as TotalRows from tb3; + select count(*) as Affected from tb3 where f130<100; + select count(*) as NotAffected from tb3 where f130>=100; + select count(*) as NewValuew from tb3 where f142=94087; +--enable_query_log + set @counter=0; + Update tb3 Set f142='1' where f130<100; + select count(*) as ExpectedChanged, @counter as TrigCounter + from tb3 where f142=94087; + select count(*) as ExpectedNotChange from tb3 + where f130<100 and f142<>94087; + select count(*) as NonExpectedChanged from tb3 + where f130>=130 and f142=94087; + +#Cleanup + --disable_warnings + drop trigger trg1; + --enable_warnings + + +#Section 3.5.9.3 +#Test case: Ensure that a reference to OLD.<column name> always correctly refers +# to the values of the specified column of the subject table before a +# data row is updated or deleted. +let $message= Testcase 3.5.9.3:; +--source include/show_msg.inc + + 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; + + +--disable_query_log + set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0, + @tr_var_b4_136=0, @tr_var_b4_163=0; + set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0, + @tr_var_af_136=0, @tr_var_af_163=0; + select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, + @tr_var_b4_136, @tr_var_b4_163; + select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, + @tr_var_af_136, @tr_var_af_163; +--enable_query_log + + 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'; + select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, + @tr_var_b4_136, @tr_var_b4_163; + select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, + @tr_var_af_136, @tr_var_af_163; + +--disable_query_log + set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0, + @tr_var_b4_136=0, @tr_var_b4_163=0; + set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0, + @tr_var_af_136=0, @tr_var_af_163=0; + select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, + @tr_var_b4_136, @tr_var_b4_163; + select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, + @tr_var_af_136, @tr_var_af_163; +--enable_query_log + + 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'; + select @tr_var_b4_118, @tr_var_b4_121, @tr_var_b4_122, + @tr_var_b4_136, @tr_var_b4_163; + select @tr_var_af_118, @tr_var_af_121, @tr_var_af_122, + @tr_var_af_136, @tr_var_af_163; +#Cleanup + --disable_warnings + drop trigger trg2_a; + drop trigger trg2_b; + drop trigger trg2_c; + drop trigger trg2_d; + --enable_warnings + +#Section 3.5.9.4 +#Test case: Ensure that a reference to NEW.<column name> always correctly refers +# to the values of the specified column of the subject table after an +# existing data row has been updated or a new data row has been inserted. +let $message= Testcase 3.5.9.4:; +--source include/show_msg.inc + + 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; + +--disable_query_log + set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0, + @tr_var_b4_136=0, @tr_var_b4_151=0, @tr_var_b4_163=0; + set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0, + @tr_var_af_136=0, @tr_var_af_151=0, @tr_var_af_163=0; + 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; + 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; +--enable_query_log + + 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%'; + 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; + 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; + +--disable_query_log + set @tr_var_b4_118=0, @tr_var_b4_121=0, @tr_var_b4_122=0, + @tr_var_b4_136=0, @tr_var_b4_151=0, @tr_var_b4_163=0; + set @tr_var_af_118=0, @tr_var_af_121=0, @tr_var_af_122=0, + @tr_var_af_136=0, @tr_var_af_151=0, @tr_var_af_163=0; + 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; + 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; +--enable_query_log + + Update tb3 Set f122='Test 3.5.9.4-trig', f136=NULL, f151=DEFAULT, f163=NULL + where f122='Test 3.5.9.4'; + + select f118, f121, f122, f136, f151, f163 from tb3 + where f122 like 'Test 3.5.9.4-trig'; + 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; + 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; +#Cleanup + --disable_warnings + 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'; + --enable_warnings + + +#Section 3.5.9.5 +# Test case: Ensure that the definition of an INSERT trigger can include a +# reference to NEW. <Column name>. +let $message= Testcase 3.5.9.5: (implied in previous tests); +--source include/show_msg.inc + +#Section 3.5.9.6 +# Test case: Ensure that the definition of an INSERT trigger cannot include +# a reference to OLD. <Column name>. +let $message= Testcase 3.5.9.6:; +--source include/show_msg.inc + + --error 1363 + create trigger trg4a before insert on tb3 for each row + set @temp1= old.f120; + --error 1362 + create trigger trg4b after insert on tb3 for each row + set old.f120= 'test'; + +#Cleanup + --disable_warnings + --error 0, 1360 + drop trigger trg4a; + --error 0, 1360 + drop trigger trg4b; + --enable_warnings + + +#Section 3.5.9.7 +# Test case: Ensure that the definition of an UPDATE trigger can include a +# reference to NEW. <Column name>. +let $message= Testcase 3.5.9.7: (implied in previous tests); +--source include/show_msg.inc + +#Section 3.5.9.8 +# Test case: Ensure that the definition of an UPDATE trigger cannot include a +# reference to OLD. <Column name>. +let $message= Testcase 3.5.9.8: (implied in previous tests); +--source include/show_msg.inc + +#Section 3.5.9.9 +# Test case: Ensure that the definition of a DELETE trigger cannot include a +# reference to NEW.<column name>. +let $message= Testcase 3.5.9.9:; +--source include/show_msg.inc + + --error 1363 + create trigger trg5a before DELETE on tb3 for each row + set @temp1=new.f122; + --error 1363 + create trigger trg5b after DELETE on tb3 for each row + set new.f122='test'; +let $message= The above returns the wrong error, should be error 1362 (Bug 11648) +--source include/show_msg.inc + +#Cleanup + --disable_warnings + --error 0, 1360 + drop trigger trg5a; + --error 0, 1360 + drop trigger trg5b; + --enable_warnings + + +#Section 3.5.9.10 +# Test case: Ensure that the definition of a DELETE trigger can include a reference +# to OLD.<column name>. +let $message= Testcase 3.5.9.10: (implied in previous tests); +--source include/show_msg.inc + + +#Section 3.5.9.11 +# Testcase: Ensure that trigger definition that includes a referance to +# NEW.<colunm name> fails with an appropriate error message, +# at CREATE TRIGGER time, if the trigger event in not INSERT or UPDATE +let $message= Testcase 3.5.9.11: covered by 3.5.9.9; +--source include/show_msg.inc + + +#Section 3.5.9.12 +# Testcase: Ensure that trigger definition that includes a referance to +# OLD.<column name> fails with an appropriate error message, at +# CREATE TRIGGER time, if the trigger event is not DELETE or UPDATE +let $message= Testcase 3.5.9.12: covered by 3.5.9.6; +--source include/show_msg.inc + + +#Section 3.5.9.13 +# Test case: Ensure that all references to OLD. <Column name> are read-only, +# that is, that they cannot be used to modify a data row. +let $message= Testcase 3.5.9.13:; +--source include/show_msg.inc + + --error 1362 + create trigger trg6a before UPDATE on tb3 for each row + set old.f118='C', new.f118='U'; + --error 1362 + create trigger trg6b after INSERT on tb3 for each row + set old.f136=163, new.f118='U'; + --error 1362 + create trigger trg6c after UPDATE on tb3 for each row + set old.f136=NULL; + +#Cleanup + --disable_warnings + --error 0, 1360 + drop trigger trg6a; + --error 0, 1360 + drop trigger trg6b; + --error 0, 1360 + drop trigger trg6c; + --enable_warnings + + +#Section 3.5.9.14 +# Test case: Ensure that all references to NEW. <Column name> may be used both to +# read a data row and to modify a data row +let $message= Testcase 3.5.9.14: (implied in previous tests); +--source include/show_msg.inc + + +############################################## +################ Section 3.5.10 ################# +# Check on Trigger Activation +############################################## +#Section 3.5.10.1 +# Test case: Ensure that every trigger that should be activated by +# every possible type of implicit insertion into its subject +# table (INSERT into a view based on the subject table) is +# indeed activated correctly +#Section 3.5.10.2 +# Test case: Ensure that every trigger that should be activated by every +# possible type of implicit insertion into its subject table +# (UPDATE into a view based on the subject table) is indeed +# activated correctly +#Section 3.5.10.3 +# Test case: Ensure that every trigger that should be activated by every +# possible type of implicit insertion into its subject table +# (DELETE from a view based on the subject table) is indeed +# activated correctly +let $message= Testcase 3.5.10.1/2/3:; +--source include/show_msg.inc + + 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; + +#Section 3.5.10.1 + 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%'; + select f121, f122, f151, f163 from vw11; + select f121, f122, f151, f163 + from tb3 where f122 like 'Not in View'; + +#Section 3.5.10.2 + Update vw11 set f163=1; + select f121, f122, f151, f163 from tb3 + where f122 like 'Test 3.5.10.1/2/3%'; + select f121, f122, f151, f163 from vw11; + +#Section 3.5.10.3 + set @test_var=0; + Select @test_var as 'before delete'; + delete from vw11 where f151=1; + select f121, f122, f151, f163 from tb3 + where f122 like 'Test 3.5.10.1/2/3%'; + select f121, f122, f151, f163 from vw11; + Select @test_var as 'after delete'; + +#Cleanup + --disable_warnings + 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%'; + --enable_warnings + + +#Section 3.5.10.4 +# Test case: Ensure that every trigger that should be activated by every +# possible type of implicit insertion into its subject table +# (LOAD into the subject table) is indeed activated correctly +let $message= Testcase 3.5.10.4:; +--source include/show_msg.inc + + eval create table tb_load (f1 int, f2 char(25),f3 int) engine=$engine_type; + 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'; + --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR + eval load data infile '$MYSQL_TEST_DIR/suite/funcs_1/data/t9.txt' into table tb_load; + + select @counter as 'Rows Loaded After'; + Select * from tb_load limit 10; + +#Cleanup + --disable_warnings + drop trigger trg4; + drop table tb_load; + --enable_warnings + + +#Section 3.5.10.5 +# Testcase: Ensure that every trigger that should be activated by every possible +# type of implicit update of its subject table (e.g.a FOREIGN KEY SET +# DEFAULT action or an UPDATE of a view based on the subject table) is +# indeed activated correctly +let $message= Testcase 3.5.10.5: (implemented in trig_frkey.test); +--source include/show_msg.inc + + +#Section 3.5.10.6 +# Testcase: Ensure that every trigger that should be activated by every possible +# type of implicit deletion from its subject table (e.g.a FOREIGN KEY +# CASCADE action or a DELETE from a view based on the subject table) is +# indeed activated correctly +let $message= Testcase 3.5.10.6: (implemented in trig_frkey.test); +--source include/show_msg.inc + +#Section 3.5.10.extra +# Testcase: Ensure that every trigger that should be activated by every possible +# type of implicit deletion from its subject table (e.g. an action performed +# on the subject table from a stored procedure is indeed activated correctly +let $message= Testcase 3.5.10.extra:; +--source include/show_msg.inc + + eval create table t1_sp (var136 tinyint, var151 decimal) engine=$engine_type; + + create trigger trg before insert on t1_sp + for each row set @counter=@counter+1; + # declare continue handler for sqlstate '01000' set done = 1; + + delimiter //; + 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// + delimiter ;// + + set @counter=0; + select @counter; + --error 1329 + call trig_sp(); + select @counter; + select count(*) from tb3; + select count(*) from t1_sp; + +#Cleanup + --disable_warnings + drop procedure trig_sp; + drop trigger trg; + drop table t1_sp; + --enable_warnings + +################################## +########## Section 3.5.11 ######## +# Check on Trigger Performance # +################################## +#Section 3.5.11.1 +# Testcase: Ensure that a set of complicated, interlocking triggers that are activated +# by multiple trigger events on no fewer than 50 different tables with at least +# 500,000 rows each, all work correctly, return the correct results, and have +# the correct effects on the database. It is expected that the Services Provider +# will use its own skills and experience in database testing to devise tables and +# triggers that fulfill this requirement. +let $message= Testcase 3.5.11.1 (implemented in trig_perf.test); +--source include/show_msg.inc + +# Cleanup section 3.5 + connection default; + drop user test_general@localhost; + drop user test_general; + drop user test_super@localhost; + +########################################## +# Other Scenasrios (not in requirements) # +########################################## +# Testcase: y.y.y.2: +# Checking for triggers starting triggers (no direct requirement) +let $message= Testcase y.y.y.2: Check for triggers starting triggers; +--source include/show_msg.inc + + use test; + --disable_warnings + 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; + --enable_warnings + + 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); + delimiter //; + 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// + delimiter ;// + + 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); + +#lock tables t1 write, t2_1 write, t2_2 write, t2_3 write, t2_4 write, t3 write; + insert into t1 values (1); +#unlock tables; + select * from t3; + +#Cleanup + --disable_warnings + 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; + --enable_warnings + +# Testcase: y.y.y.3: +# Checking for circular trigger definitions +let $message= Testcase y.y.y.3: Circular trigger reference; +--source include/show_msg.inc + use test; + --disable_warnings + drop table if exists t1; + drop table if exists t2; + drop table if exists t3; + drop table if exists t4; + --enable_warnings + eval create table t1 (f1 integer) engine = $engine_type; + eval create table t2 (f2 integer) engine = $engine_type; + eval create table t3 (f3 integer) engine = $engine_type; + eval create table t4 (f4 integer) engine = $engine_type; + + 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); + + # OBN See bug 11896 + --error 1442 + insert into t1 values (1); + select * from t1; + select * from t2; + select * from t3; + select * from t4; + +#Cleanup + --disable_warnings + drop trigger tr1; + drop trigger tr2; + drop trigger tr3; + drop trigger tr4; + drop table t1; + drop table t2; + drop table t3; + drop table t4; + --enable_warnings + + +#Section y.y.y.4 +# Testcase: create recursive trigger/storedprocedures conditions +let $message= Testcase y.y.y.4: Recursive trigger/SP references (disabled bug 11889); +--source include/show_msg.inc + +set @sql_mode='traditional'; + eval create table t1_sp ( + count integer, + var136 tinyint, + var151 decimal) engine=$engine_type; + + delimiter //; + 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// + delimiter ;// + + create trigger trg before insert on t1_sp + for each row call trig_sp(); + + set @counter=0; + select @counter; + --error 1424 + call trig_sp(); + select @counter; + select count(*) from tb3; + select count(*) from t1_sp; + +#Cleanup + --disable_warnings + drop procedure trig_sp; + drop trigger trg; + drop table t1_sp; + --enable_warnings + + +# Testcase: y.y.y.5: +# Checking rollback of nested trigger definitions +let $message= Testcase y.y.y.5: Roleback of nested trigger references; +--source include/show_msg.inc + + set @@sql_mode='traditional'; + use test; + --disable_warnings + drop table if exists t1; + drop table if exists t2; + drop table if exists t3; + drop table if exists t4; + --enable_warnings + eval create table t1 (f1 integer) engine = $engine_type; + eval create table t2 (f2 integer) engine = $engine_type; + eval create table t3 (f3 integer) engine = $engine_type; + eval create table t4 (f4 tinyint) engine = $engine_type; + show create table t1; + 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); + +#lock tables t1 write, t2 write, t3 write, t4 write; + + set autocommit=0; + start transaction; + --error 1264 + insert into t1 values (1); + commit; + select * from t1; + select * from t2; + select * from t3; +#unlock tables; +#Cleanup + --disable_warnings + drop trigger tr1; + drop trigger tr2; + drop trigger tr3; + drop table t1; + drop table t2; + drop table t3; + drop table t4; + --enable_warnings + |