1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
|
#!/usr/bin/perl
##
## MAIN
##
$table_name="";
$old_name="";
$references_table="";
$references_column="";
$is_create=0;
while ( <> )
{
chop;
$str=$_ ;
if ($is_create == 1) {
$table_name=$str;
$is_create=2;
}
if ( $str =~ /^CREATE TABLE/ ){
$is_create=1;
}
if ($is_create == 2) {
if ($str =~ /^FOREIGN KEY/){
($d1,$d2,$d3,$columns,$d4,$d5,$references_table,$d6) = split (/ /,$str,8);
#printf "Table $table_name $columns $references_table\n";
if ($table_name ne $old_name ){
printf "--\n-- Trigger for $table_name\n--\n\n";
}
foreach $i ( split(/,/ , $columns ) ){
print "CREATE INDEX I_$table_name";
print "_$i ON $table_name ( $i ) ;\n";
}
printf "\nCREATE TRIGGER T_P_$table_name";
printf "_$references_table BEFORE INSERT OR UPDATE ON $table_name FOR EACH ROW\n" ;
printf "EXECUTE PROCEDURE\n";
printf "check_primary_key(";
$val=0;
foreach $i ( split(/,/ , $columns ) ){
print "'$i',";
$val=$val+1 ;
}
print "'$references_table',";
$t=1;
foreach $i ( split(/,/,$columns ) ){
print "'$i'";
if ( $t < $val ) {
printf ",";
}
$t=$t+1;
}
print " );\n\n";
printf "CREATE TRIGGER T_F_D_$references_table";
printf "_$table_name BEFORE DELETE ON $references_table FOR EACH ROW\n" ;
printf "EXECUTE PROCEDURE\n";
printf "check_foreign_key(1,'cascade',";
$val=0;
foreach $i ( split(/,/ , $columns ) ){
print "'$i',";
$val=$val+1 ;
}
print "'$table_name',";
$t=1;
foreach $i ( split(/,/,$columns ) ){
print "'$i'";
if ( $t < $val ) {
printf ",";
}
$t=$t+1;
}
print " );\n\n";
printf "CREATE TRIGGER T_F_U_$references_table";
printf "_$table_name AFTER UPDATE ON $references_table FOR EACH ROW\n" ;
printf "EXECUTE PROCEDURE\n";
printf "check_foreign_key(1,'cascade',";
$val=0;
foreach $i ( split(/,/ , $columns ) ){
print "'$i',";
$val=$val+1 ;
}
print "'$table_name',";
$t=1;
foreach $i ( split(/,/,$columns ) ){
print "'$i'";
if ( $t < $val ) {
printf ",";
}
$t=$t+1;
}
print " );\n\n";
if ($table_name ne $old_name ){
printf "-- ********************************\n\n\n";
}
$old_name=$table_name ;
}
}
if ($str =~ /^\)\;/ ) {
$is_create = 0 ;
}
}
|
