← Back to branch summary

~stewart/drizzle/embedded-innodb-create-select-transaction-arrgh

« back to all changes in this revision

Viewing changes to mysql-test/suite/parts/inc/partition_trigg3.inc

  • Committer: brian
  • Date: 2008-06-25 05:29:13 UTC
  • Revision ID: brian@localhost.localdomain-20080625052913-6upwo0jsrl4lnapl
clean slate

Show diffs side-by-side

added added

removed removed

Lines of Context:
mysql-test/suite/parts/inc/partition_trigg3.inc
 
1
################################################################################
 
2
# inc/partition_trigg3.inc                                                     #
 
3
#                                                                              #
 
4
# Purpose:                                                                     #
 
5
#   Auxiliary script, only useful when sourced by inc/partition_check.inc.     #
 
6
#   The trigger uses new values (--> event UPDATE, INSERT only)                #
 
7
#                                                                              #
 
8
# 1. Create a trigger                                                          #
 
9
# 2. Execute a statement, which activates the trigger                          #
 
10
# 3. Check the results of the trigger activity                                 #
 
11
# 4. Revert the modifications                                                  #
 
12
#                                                                              #
 
13
#------------------------------------------------------------------------------#
 
14
# Original Author: mleich                                                      #
 
15
# Original Date: 2006-03-05                                                    #
 
16
# Change Author:                                                               #
 
17
# Change Date:                                                                 #
 
18
# Change:                                                                      #
 
19
################################################################################
 
20
 
 
21
delimiter |;
 
22
# Original version of the trigger
 
23
# eval CREATE TRIGGER trg_3 $event ON t1 FOR EACH ROW
 
24
# BEGIN
 
25
#    SET @counter = 1;
 
26
#    SET @my_max1 = 0, @my_max2 = 0;
 
27
#    SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
 
28
#    SET new.f_int1 = @my_max1 + @counter,
 
29
#        new.f_int2 = @my_min2 - @counter,
 
30
#        new.f_charbig = '####updated per insert trigger####';
 
31
#    SET @counter = @counter + 1;
 
32
# END|
 
33
#
 
34
# Bug/currently undocumented limitation:
 
35
#      17704: Triggers: MAX, Insert select with several rows, strange error
 
36
# "A trigger can not access (not even read data) the table it's defined for."
 
37
#
 
38
eval CREATE TRIGGER trg_3 $event ON t1 FOR EACH ROW
 
39
BEGIN
 
40
   SET new.f_int1 = @my_max1 + @counter,
 
41
       new.f_int2 = @my_min2 - @counter,
 
42
       new.f_charbig = '####updated per insert trigger####';
 
43
   SET @counter = @counter + 1;
 
44
END|
 
45
delimiter ;|
 
46
# Additional statements because of Bug(limitation)#17704
 
47
SET @counter = 1;
 
48
# Bug#18730 Partitions: NDB, crash on SELECT MIN(<unique column>)
 
49
SELECT MAX(f_int1), MIN(f_int2) INTO @my_max1,@my_min2 FROM t1;
 
50
# Additional statements end
 
51
eval $statement;
 
52
DROP TRIGGER trg_3;
 
53
#      Check of preceding statement via Select
 
54
if ($no_debug)
 
55
{
 
56
  --disable_query_log
 
57
}
 
58
# We insert records around max_row_div2 !
 
59
eval SELECT '# check trigger-$num success: ' AS "", COUNT(*) = 3 AS "" FROM t1
 
60
WHERE f_int1 = CAST(f_char1 AS SIGNED INT) + @max_row_div2 + 2
 
61
  AND f_int2 = - CAST(f_char1 AS SIGNED INT) + @max_row_div2 - 1
 
62
  AND f_charbig = '####updated per insert trigger####';
 
63
--enable_query_log
 
64
# Revert the changes
 
65
eval DELETE FROM t1
 
66
WHERE f_int1 <> CAST(f_char1 AS SIGNED INT)
 
67
  AND f_int2 <> CAST(f_char1 AS SIGNED INT)
 
68
  AND f_charbig = '####updated per insert trigger####';
 
69
inc $num;