Debian Codegraph @

mysql-test/suite/funcs_1/triggers/triggers_03e_transaction.inc

raw
1 #======================================================================
2 #
3 # Trigger Tests
4 # test cases for TRIGGER privilege on db, table and column level
5 #======================================================================
6
7 --disable_abort_on_error
8
9 ###########################################
10 ################ Section 3.5.3 ############
11 # Check for Triggers in transactions #
12 ###########################################
13
14 # General setup to be used in all testcases
15 let $message= ######### Testcase for transactions: ########;
16 --source include/show_msg.inc
17
18 --disable_warnings
19 drop database if exists priv_db;
20 --enable_warnings
21 create database priv_db;
22 use priv_db;
23 eval create table t1 (f1 char(20)) engine= $engine_type;
24
25 create User test_yesprivs@localhost;
26 set password for test_yesprivs@localhost = password('PWD');
27
28 revoke ALL PRIVILEGES, GRANT OPTION FROM test_yesprivs@localhost;
29
30
31 connection default;
32 select current_user;
33 grant select, insert, update ,trigger
34 on priv_db.t1 to test_yesprivs@localhost;
35 show grants for test_yesprivs@localhost;
36
37 connect (yes_privs,localhost,test_yesprivs,PWD,priv_db,$MASTER_MYPORT,$MASTER_MYSOCK);
38 connection yes_privs;
39 select current_user;
40 set autocommit=0;
41 create definer=current_user trigger trg1_1
42 before INSERT on t1 for each row
43 set new.f1 = 'trig 1_1-yes';
44 rollback work;
45 insert into t1 (f1) values ('insert-no');
46 select f1 from t1 order by f1;
47
48 create definer=test_yesprivs@localhost trigger trg1_2
49 before UPDATE on t1 for each row
50 set new.f1 = 'trig 1_2-yes';
51 commit work;
52 update t1 set f1 = 'update-yes' where f1 like '%trig%';
53 select f1 from t1 order by f1;
54 commit work;
55 drop trigger trg1_1;
56 rollback work;
57 --error ER_TRG_DOES_NOT_EXIST
58 drop trigger trg1_1;
59 drop trigger trg1_2;
60 commit work;
61 set autocommit=1;
62
63 connection default;
64 select current_user;
65
66 # Cleanup prepare
67 --disable_warnings
68 disconnect yes_privs;
69
70 connection default;
71 select current_user;
72 --enable_warnings
73
74
75 # general Cleanup
76 --disable_warnings
77 drop database if exists priv_db;
78 drop user test_yesprivs@localhost;
79 --enable_warnings
80