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
15let $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	connect (yes_privs,localhost,test_yesprivs,PWD,test,$MASTER_MYPORT,$MASTER_MYSOCK);
31
32	connection default;
33        select current_user;
34        grant  select, insert, update ,trigger
35	        on priv_db.t1 to test_yesprivs@localhost;
36	show grants for test_yesprivs@localhost;
37
38	connection yes_privs;
39        select current_user;
40	use priv_db;
41	set autocommit=0;
42	create definer=current_user trigger trg1_1
43		before INSERT on t1 for each row
44		set new.f1 = 'trig 1_1-yes';
45	rollback work;
46	insert into t1 (f1) values ('insert-no');
47	select f1 from t1 order by f1;
48
49        create definer=test_yesprivs@localhost trigger trg1_2
50                before UPDATE on t1 for each row
51                set new.f1 = 'trig 1_2-yes';
52	commit work;
53        update t1 set f1 = 'update-yes' where f1 like '%trig%';
54        select f1 from t1 order by f1;
55	commit work;
56	drop trigger trg1_1;
57	rollback work;
58	--error ER_TRG_DOES_NOT_EXIST
59	drop trigger trg1_1;
60	drop trigger trg1_2;
61	commit work;
62	set autocommit=1;
63
64        connection default;
65        select current_user;
66
67# Cleanup prepare
68	--disable_warnings
69        disconnect yes_privs;
70
71	connection default;
72        select current_user;
73	--enable_warnings
74
75
76# general Cleanup
77	--disable_warnings
78	drop database if exists priv_db;
79	drop user test_yesprivs@localhost;
80	--enable_warnings
81
82