1
################################################################################
2
# inc/partition_trigg1.inc #
5
# Auxiliary script, only useful when sourced by inc/partition_check.inc. #
6
# One trigger uses new values (--> event UPDATE, INSERT only) #
7
# One trigger uses old values (--> event UPDATE, DELETE only) #
9
# 1. Create a trigger #
10
# 2. Execute a statement, which activates the trigger #
11
# 3. Check the results of the trigger activity #
12
# 4. Revert the modifications #
14
#------------------------------------------------------------------------------#
15
# Original Author: mleich #
16
# Original Date: 2006-03-05 #
20
################################################################################
22
# Test for operations, which have new values (INSERT+UPDATE, but not DELETE)
27
eval SELECT INSTR('$statement','DELETE') = 0 INTO @aux;
28
let $run1= `SELECT @aux`;
32
# Insert three records which are only needed for UPDATE TRIGGER test
33
eval INSERT INTO $tab_has_trigg(f_int1,f_int2,f_char1,f_char2,f_charbig)
34
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
35
'just inserted' FROM t0_template
36
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
38
eval CREATE TRIGGER trg_1 $event ON $tab_has_trigg FOR EACH ROW
40
UPDATE $tab_in_trigg SET f_int1 = -f_int1, f_int2 = -f_int2,
41
f_charbig = 'updated by trigger'
42
WHERE f_int1 = new.f_int1;
48
# Check of preceding statement via Select
53
eval SELECT '# check trigger-$num success: ' AS "", COUNT(*) = 3 AS ""
55
WHERE f_int1 = f_int2 AND CAST(f_char1 AS SIGNED INT) = -f_int1;
61
eval UPDATE $tab_in_trigg SET f_int1 = CAST(f_char1 AS SIGNED INT),
62
f_int2 = CAST(f_char1 AS SIGNED INT),
63
f_charbig = 'just inserted'
64
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
65
eval DELETE FROM $tab_has_trigg
66
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
70
# Test for operations, which have old values (UPDATE+DELETE, but not INSERT)
75
eval SELECT INSTR('$statement','INSERT') = 0 INTO @aux;
76
let $run1= `SELECT @aux`;
80
# Insert three records which are only needed for UPDATE/DELETE TRIGGER test
81
eval INSERT INTO $tab_has_trigg(f_int1,f_int2,f_char1,f_char2,f_charbig)
82
SELECT -f_int1,-f_int1,CAST(-f_int1 AS CHAR),CAST(-f_int1 AS CHAR),
83
'just inserted' FROM t0_template
84
WHERE f_int1 BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;
87
eval CREATE TRIGGER trg_1 $event ON $tab_has_trigg FOR EACH ROW
89
UPDATE $tab_in_trigg SET f_int1 = -f_int1, f_int2 = -f_int2,
90
f_charbig = 'updated by trigger'
91
WHERE f_int1 = - old.f_int1;
97
# Check of preceding statement via Select
102
eval SELECT '# check trigger-$num success: ' AS "", COUNT(*) = 3 AS ""
104
WHERE f_int1 = f_int2 AND CAST(f_char1 AS SIGNED INT) = -f_int1;
108
eval UPDATE $tab_in_trigg SET f_int1 = CAST(f_char1 AS SIGNED INT),
109
f_int2 = CAST(f_char1 AS SIGNED INT),
110
f_charbig = 'just inserted'
111
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT);
112
eval DELETE FROM $tab_has_trigg
113
WHERE ABS(f_int1) BETWEEN @max_row_div2 - 1 AND @max_row_div2 + 1;