summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/trigger.result109
-rw-r--r--mysql-test/t/trigger.test118
-rw-r--r--sql/item.cc43
-rw-r--r--sql/item.h19
-rw-r--r--sql/mysql_priv.h12
-rw-r--r--sql/sql_base.cc72
-rw-r--r--sql/sql_delete.cc45
-rw-r--r--sql/sql_insert.cc163
-rw-r--r--sql/sql_load.cc30
-rw-r--r--sql/sql_trigger.cc45
-rw-r--r--sql/sql_trigger.h39
-rw-r--r--sql/sql_update.cc66
12 files changed, 634 insertions, 127 deletions
diff --git a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
index cf0e0e8f564..1d2fb5989a5 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -140,6 +140,48 @@ drop trigger t1.trg1;
drop trigger t1.trg2;
drop trigger t1.trg3;
drop table t1;
+create table t1 (id int not null primary key, data int);
+create trigger t1_bi before insert on t1 for each row
+set @log:= concat(@log, "(BEFORE_INSERT: new=(id=", new.id, ", data=", new.data,"))");
+create trigger t1_ai after insert on t1 for each row
+set @log:= concat(@log, "(AFTER_INSERT: new=(id=", new.id, ", data=", new.data,"))");
+create trigger t1_bu before update on t1 for each row
+set @log:= concat(@log, "(BEFORE_UPDATE: old=(id=", old.id, ", data=", old.data,
+") new=(id=", new.id, ", data=", new.data,"))");
+create trigger t1_au after update on t1 for each row
+set @log:= concat(@log, "(AFTER_UPDATE: old=(id=", old.id, ", data=", old.data,
+") new=(id=", new.id, ", data=", new.data,"))");
+create trigger t1_bd before delete on t1 for each row
+set @log:= concat(@log, "(BEFORE_DELETE: old=(id=", old.id, ", data=", old.data,"))");
+create trigger t1_ad after delete on t1 for each row
+set @log:= concat(@log, "(AFTER_DELETE: old=(id=", old.id, ", data=", old.data,"))");
+set @log:= "";
+insert into t1 values (1, 1);
+select @log;
+@log
+(BEFORE_INSERT: new=(id=1, data=1))(AFTER_INSERT: new=(id=1, data=1))
+set @log:= "";
+insert ignore t1 values (1, 2);
+select @log;
+@log
+(BEFORE_INSERT: new=(id=1, data=2))
+set @log:= "";
+replace t1 values (1, 3), (2, 2);
+select @log;
+@log
+(BEFORE_INSERT: new=(id=1, data=3))(BEFORE_UPDATE: old=(id=1, data=1) new=(id=1, data=3))(AFTER_UPDATE: old=(id=1, data=1) new=(id=1, data=3))(BEFORE_INSERT: new=(id=2, data=2))(AFTER_INSERT: new=(id=2, data=2))
+alter table t1 add ts timestamp default now();
+set @log:= "";
+replace t1 (id, data) values (1, 4);
+select @log;
+@log
+(BEFORE_INSERT: new=(id=1, data=4))(BEFORE_DELETE: old=(id=1, data=3))(AFTER_DELETE: old=(id=1, data=3))(AFTER_INSERT: new=(id=1, data=4))
+set @log:= "";
+insert into t1 (id, data) values (1, 5), (3, 3) on duplicate key update data= data + 2;
+select @log;
+@log
+(BEFORE_INSERT: new=(id=1, data=5))(BEFORE_UPDATE: old=(id=1, data=4) new=(id=1, data=6))(AFTER_UPDATE: old=(id=1, data=4) new=(id=1, data=6))(BEFORE_INSERT: new=(id=3, data=3))(AFTER_INSERT: new=(id=3, data=3))
+drop table t1;
create table t1 (i int);
create trigger trg before insert on t1 for each row set @a:= old.i;
ERROR HY000: There is no OLD row in on INSERT trigger
@@ -206,3 +248,70 @@ create table t1 (i int);
create trigger trg1 before insert on t1 for each row set @a:= 1;
drop database mysqltest;
use test;
+create table t1 (i int, j int default 10, k int not null, key (k));
+create table t2 (i int);
+insert into t1 (i, k) values (1, 1);
+insert into t2 values (1);
+create trigger trg1 before update on t1 for each row set @a:= @a + new.j - old.j;
+create trigger trg2 after update on t1 for each row set @b:= "Fired";
+set @a:= 0, @b:= "";
+update t1, t2 set j = j + 10 where t1.i = t2.i;
+select @a, @b;
+@a @b
+10 Fired
+insert into t1 values (2, 13, 2);
+insert into t2 values (2);
+set @a:= 0, @b:= "";
+update t1, t2 set j = j + 15 where t1.i = t2.i and t1.k >= 2;
+select @a, @b;
+@a @b
+15 Fired
+create trigger trg3 before delete on t1 for each row set @c:= @c + old.j;
+create trigger trg4 before delete on t2 for each row set @d:= @d + old.i;
+create trigger trg5 after delete on t1 for each row set @e:= "After delete t1 fired";
+create trigger trg6 after delete on t2 for each row set @f:= "After delete t2 fired";
+set @c:= 0, @d:= 0, @e:= "", @f:= "";
+delete t1, t2 from t1, t2 where t1.i = t2.i;
+select @c, @d, @e, @f;
+@c @d @e @f
+48 3 After delete t1 fired After delete t2 fired
+drop table t1, t2;
+create table t1 (i int, j int default 10)|
+create table t2 (i int)|
+insert into t2 values (1), (2)|
+create trigger trg1 before insert on t1 for each row
+begin
+if new.i = 1 then
+set new.j := 1;
+end if;
+end|
+create trigger trg2 after insert on t1 for each row set @a:= 1|
+set @a:= 0|
+insert into t1 (i) select * from t2|
+select * from t1|
+i j
+1 1
+2 10
+select @a|
+@a
+1
+drop table t1, t2|
+create table t1 (i int, j int, k int);
+create trigger trg1 before insert on t1 for each row set new.k = new.i;
+create trigger trg2 after insert on t1 for each row set @b:= "Fired";
+set @b:="";
+load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, i);
+select *, @b from t1;
+i j k @b
+10 NULL 10 Fired
+15 NULL 15 Fired
+set @b:="";
+load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, j);
+select *, @b from t1;
+i j k @b
+10 NULL 10 Fired
+15 NULL 15 Fired
+1 2 1 Fired
+3 4 3 Fired
+5 6 5 Fired
+drop table t1;
diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
index 53144cf3591..79f65bba678 100644
--- a/mysql-test/t/trigger.test
+++ b/mysql-test/t/trigger.test
@@ -150,6 +150,55 @@ drop trigger t1.trg3;
drop table t1;
+# Let us test how triggers work for special forms of INSERT such as
+# REPLACE and INSERT ... ON DUPLICATE KEY UPDATE
+create table t1 (id int not null primary key, data int);
+create trigger t1_bi before insert on t1 for each row
+ set @log:= concat(@log, "(BEFORE_INSERT: new=(id=", new.id, ", data=", new.data,"))");
+create trigger t1_ai after insert on t1 for each row
+ set @log:= concat(@log, "(AFTER_INSERT: new=(id=", new.id, ", data=", new.data,"))");
+create trigger t1_bu before update on t1 for each row
+ set @log:= concat(@log, "(BEFORE_UPDATE: old=(id=", old.id, ", data=", old.data,
+ ") new=(id=", new.id, ", data=", new.data,"))");
+create trigger t1_au after update on t1 for each row
+ set @log:= concat(@log, "(AFTER_UPDATE: old=(id=", old.id, ", data=", old.data,
+ ") new=(id=", new.id, ", data=", new.data,"))");
+create trigger t1_bd before delete on t1 for each row
+ set @log:= concat(@log, "(BEFORE_DELETE: old=(id=", old.id, ", data=", old.data,"))");
+create trigger t1_ad after delete on t1 for each row
+ set @log:= concat(@log, "(AFTER_DELETE: old=(id=", old.id, ", data=", old.data,"))");
+# Simple INSERT - both triggers should be called
+set @log:= "";
+insert into t1 values (1, 1);
+select @log;
+# INSERT IGNORE for already existing key - only before trigger should fire
+set @log:= "";
+insert ignore t1 values (1, 2);
+select @log;
+# REPLACE: before insert trigger should be called for both records,
+# but then for first one update will be executed (and both update
+# triggers should fire). For second after insert trigger will be
+# called as for usual insert
+set @log:= "";
+replace t1 values (1, 3), (2, 2);
+select @log;
+# Now let us change table in such way that REPLACE on won't be executed
+# using update.
+alter table t1 add ts timestamp default now();
+set @log:= "";
+# This REPLACE should be executed via DELETE and INSERT so proper
+# triggers should be invoked.
+replace t1 (id, data) values (1, 4);
+select @log;
+# Finally let us test INSERT ... ON DUPLICATE KEY UPDATE ...
+set @log:= "";
+insert into t1 (id, data) values (1, 5), (3, 3) on duplicate key update data= data + 2;
+select @log;
+
+# This also drops associated triggers
+drop table t1;
+
+
#
# Test of wrong column specifiers in triggers
#
@@ -249,3 +298,72 @@ create trigger trg1 before insert on t1 for each row set @a:= 1;
# This should succeed
drop database mysqltest;
use test;
+
+# Test for bug #5860 "Multi-table UPDATE does not activate update triggers"
+# We will also test how delete triggers wor for multi-table DELETE.
+create table t1 (i int, j int default 10, k int not null, key (k));
+create table t2 (i int);
+insert into t1 (i, k) values (1, 1);
+insert into t2 values (1);
+create trigger trg1 before update on t1 for each row set @a:= @a + new.j - old.j;
+create trigger trg2 after update on t1 for each row set @b:= "Fired";
+set @a:= 0, @b:= "";
+# Check that trigger works in case of update on the fly
+update t1, t2 set j = j + 10 where t1.i = t2.i;
+select @a, @b;
+insert into t1 values (2, 13, 2);
+insert into t2 values (2);
+set @a:= 0, @b:= "";
+# And now let us check that triggers work in case of multi-update which
+# is done through temporary tables...
+update t1, t2 set j = j + 15 where t1.i = t2.i and t1.k >= 2;
+select @a, @b;
+# Let us test delete triggers for multi-delete now.
+# We create triggers for both tables because we want test how they
+# work in both on-the-fly and via-temp-tables cases.
+create trigger trg3 before delete on t1 for each row set @c:= @c + old.j;
+create trigger trg4 before delete on t2 for each row set @d:= @d + old.i;
+create trigger trg5 after delete on t1 for each row set @e:= "After delete t1 fired";
+create trigger trg6 after delete on t2 for each row set @f:= "After delete t2 fired";
+set @c:= 0, @d:= 0, @e:= "", @f:= "";
+delete t1, t2 from t1, t2 where t1.i = t2.i;
+select @c, @d, @e, @f;
+# This also will drop triggers
+drop table t1, t2;
+
+# Test for bug #6812 "Triggers are not activated for INSERT ... SELECT".
+# (We also check the fact that trigger modifies some field does not affect
+# value of next record inserted).
+delimiter |;
+create table t1 (i int, j int default 10)|
+create table t2 (i int)|
+insert into t2 values (1), (2)|
+create trigger trg1 before insert on t1 for each row
+begin
+ if new.i = 1 then
+ set new.j := 1;
+ end if;
+end|
+create trigger trg2 after insert on t1 for each row set @a:= 1|
+set @a:= 0|
+insert into t1 (i) select * from t2|
+select * from t1|
+select @a|
+# This also will drop triggers
+drop table t1, t2|
+delimiter ;|
+
+# Test for bug #8755 "Trigger is not activated by LOAD DATA"
+create table t1 (i int, j int, k int);
+create trigger trg1 before insert on t1 for each row set new.k = new.i;
+create trigger trg2 after insert on t1 for each row set @b:= "Fired";
+set @b:="";
+# Test triggers with file with separators
+load data infile '../../std_data/rpl_loaddata.dat' into table t1 (@a, i);
+select *, @b from t1;
+set @b:="";
+# Test triggers with fixed size row file
+load data infile '../../std_data/loaddata5.dat' into table t1 fields terminated by '' enclosed by '' (i, j);
+select *, @b from t1;
+# This also will drop triggers
+drop table t1;
diff --git a/sql/item.cc b/sql/item.cc
index 30c134ebdd5..7f241955ec4 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -4546,40 +4546,40 @@ void Item_insert_value::print(String *str)
/*
- Bind item representing field of row being changed in trigger
- to appropriate Field object.
+ Find index of Field object which will be appropriate for item
+ representing field of row being changed in trigger.
SYNOPSIS
setup_field()
thd - current thread context
table - table of trigger (and where we looking for fields)
- event - type of trigger event
NOTE
This function does almost the same as fix_fields() for Item_field
- but is invoked during trigger definition parsing and takes TABLE
- object as its argument. If proper field was not found in table
- error will be reported at fix_fields() time.
+ but is invoked right after trigger definition parsing. Since at
+ this stage we can't say exactly what Field object (corresponding
+ to TABLE::record[0] or TABLE::record[1]) should be bound to this
+ Item, we only find out index of the Field and then select concrete
+ Field object in fix_fields() (by that time Table_trigger_list::old_field/
+ new_field should point to proper array of Fields).
+ It also binds Item_trigger_field to Table_triggers_list object for
+ table of trigger which uses this item.
*/
-void Item_trigger_field::setup_field(THD *thd, TABLE *table,
- enum trg_event_type event)
+
+void Item_trigger_field::setup_field(THD *thd, TABLE *table)
{
- uint field_idx= (uint)-1;
bool save_set_query_id= thd->set_query_id;
/* TODO: Think more about consequences of this step. */
thd->set_query_id= 0;
-
- if (find_field_in_real_table(thd, table, field_name,
- strlen(field_name), 0, 0,
- &field_idx))
- {
- field= (row_version == OLD_ROW && event == TRG_EVENT_UPDATE) ?
- table->triggers->old_field[field_idx] :
- table->field[field_idx];
- }
-
+ /*
+ Try to find field by its name and if it will be found
+ set field_idx properly.
+ */
+ (void)find_field_in_real_table(thd, table, field_name, strlen(field_name),
+ 0, 0, &field_idx);
thd->set_query_id= save_set_query_id;
+ triggers= table->triggers;
}
@@ -4604,9 +4604,10 @@ bool Item_trigger_field::fix_fields(THD *thd,
*/
DBUG_ASSERT(fixed == 0);
- if (field)
+ if (field_idx != (uint)-1)
{
- // QQ: May be this should be moved to setup_field?
+ field= (row_version == OLD_ROW) ? triggers->old_field[field_idx] :
+ triggers->new_field[field_idx];
set_field(field);
fixed= 1;
return 0;
diff --git a/sql/item.h b/sql/item.h
index 0e15e539067..7b2344f12d8 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1601,13 +1601,18 @@ enum trg_event_type
TRG_EVENT_INSERT= 0 , TRG_EVENT_UPDATE= 1, TRG_EVENT_DELETE= 2
};
+class Table_triggers_list;
+
/*
Represents NEW/OLD version of field of row which is
changed/read in trigger.
- Note: For this item actual binding to Field object happens not during
- fix_fields() (like for Item_field) but during parsing of trigger
- definition, when table is opened, with special setup_field() call.
+ Note: For this item main part of actual binding to Field object happens
+ not during fix_fields() call (like for Item_field) but right after
+ parsing of trigger definition, when table is opened, with special
+ setup_field() call. On fix_fields() stage we simply choose one of
+ two Field instances representing either OLD or NEW version of this
+ field.
*/
class Item_trigger_field : public Item_field
{
@@ -1617,13 +1622,17 @@ public:
row_version_type row_version;
/* Next in list of all Item_trigger_field's in trigger */
Item_trigger_field *next_trg_field;
+ /* Index of the field in the TABLE::field array */
+ uint field_idx;
+ /* Pointer to Table_trigger_list object for table of this trigger */
+ Table_triggers_list *triggers;
Item_trigger_field(row_version_type row_ver_par,
const char *field_name_par):
Item_field((const char *)NULL, (const char *)NULL, field_name_par),
- row_version(row_ver_par)
+ row_version(row_ver_par), field_idx((uint)-1)
{}
- void setup_field(THD *thd, TABLE *table, enum trg_event_type event);
+ void setup_field(THD *thd, TABLE *table);
enum Type type() const { return TRIGGER_FIELD_ITEM; }
bool eq(const Item *item, bool binary_cmp) const;
bool fix_fields(THD *, struct st_table_list *, Item **);
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 25490b04ab3..82dceb87ed4 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -924,10 +924,18 @@ bool remove_table_from_cache(THD *thd, const char *db, const char *table,
bool return_if_owned_by_thd);
bool close_cached_tables(THD *thd, bool wait_for_refresh, TABLE_LIST *tables);
void copy_field_from_tmp_record(Field *field,int offset);
-bool fill_record(THD *thd, List<Item> &fields, List<Item> &values,
- bool ignore_errors);
bool fill_record(THD *thd, Field **field, List<Item> &values,
bool ignore_errors);
+bool fill_record_n_invoke_before_triggers(THD *thd, List<Item> &fields,
+ List<Item> &values,
+ bool ignore_errors,
+ Table_triggers_list *triggers,
+ enum trg_event_type event);
+bool fill_record_n_invoke_before_triggers(THD *thd, Field **field,
+ List<Item> &values,
+ bool ignore_errors,
+ Table_triggers_list *triggers,
+ enum trg_event_type event);
OPEN_TABLE_LIST *list_open_tables(THD *thd, const char *wild);
inline TABLE_LIST *find_table_in_global_list(TABLE_LIST *table,
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index bda5700b273..d431bb7ddca 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -3813,7 +3813,7 @@ err_no_arena:
TRUE error occured
*/
-bool
+static bool
fill_record(THD * thd, List<Item> &fields, List<Item> &values,
bool ignore_errors)
{
@@ -3840,6 +3840,41 @@ fill_record(THD * thd, List<Item> &fields, List<Item> &values,
/*
+ Fill fields in list with values from the list of items and invoke
+ before triggers.
+
+ SYNOPSIS
+ fill_record_n_invoke_before_triggers()
+ thd thread context
+ fields Item_fields list to be filled
+ values values to fill with
+ ignore_errors TRUE if we should ignore errors
+ triggers object holding list of triggers to be invoked
+ event event type for triggers to be invoked
+
+ NOTE
+ This function assumes that fields which values will be set and triggers
+ to be invoked belong to the same table, and that TABLE::record[0] and
+ record[1] buffers correspond to new and old versions of row respectively.
+
+ RETURN
+ FALSE OK
+ TRUE error occured
+*/
+
+bool
+fill_record_n_invoke_before_triggers(THD *thd, List<Item> &fields,
+ List<Item> &values, bool ignore_errors,
+ Table_triggers_list *triggers,
+ enum trg_event_type event)
+{
+ return (fill_record(thd, fields, values, ignore_errors) ||
+ triggers && triggers->process_triggers(thd, event,
+ TRG_ACTION_BEFORE, TRUE));
+}
+
+
+/*
Fill field buffer with values from Field list
SYNOPSIS
@@ -3875,6 +3910,41 @@ fill_record(THD *thd, Field **ptr, List<Item> &values, bool ignore_errors)
}
+/*
+ Fill fields in array with values from the list of items and invoke
+ before triggers.
+
+ SYNOPSIS
+ fill_record_n_invoke_before_triggers()
+ thd thread context
+ ptr NULL-ended array of fields to be filled
+ values values to fill with
+ ignore_errors TRUE if we should ignore errors
+ triggers object holding list of triggers to be invoked
+ event event type for triggers to be invoked
+
+ NOTE
+ This function assumes that fields which values will be set and triggers
+ to be invoked belong to the same table, and that TABLE::record[0] and
+ record[1] buffers correspond to new and old versions of row respectively.
+
+ RETURN
+ FALSE OK
+ TRUE error occured
+*/
+
+bool
+fill_record_n_invoke_before_triggers(THD *thd, Field **ptr,
+ List<Item> &values, bool ignore_errors,
+ Table_triggers_list *triggers,
+ enum trg_event_type event)
+{
+ return (fill_record(thd, ptr, values, ignore_errors) ||
+ triggers && triggers->process_triggers(thd, event,
+ TRG_ACTION_BEFORE, TRUE));
+}
+
+
static void mysql_rm_tmp_tables(void)
{
uint i, idx;
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index cded9e2a13e..19e9866597a 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -176,13 +176,24 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
if (!(select && select->skip_record())&& !thd->net.report_error )
{
- if (table->triggers)
- table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
- TRG_ACTION_BEFORE);
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
+ TRG_ACTION_BEFORE, FALSE))
+ {
+ error= 1;
+ break;
+ }
if (!(error=table->file->delete_row(table->record[0])))
{
deleted++;
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
+ TRG_ACTION_AFTER, FALSE))
+ {
+ error= 1;
+ break;
+ }
if (!--limit && using_limit)
{
error= -1;
@@ -203,10 +214,6 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
error= 1;
break;
}
-
- if (table->triggers)
- table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
- TRG_ACTION_AFTER);
}
else
table->file->unlock_row(); // Row failed selection, release lock on it
@@ -509,9 +516,19 @@ bool multi_delete::send_data(List<Item> &values)
if (secure_counter < 0)
{
/* If this is the table we are scanning */
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
+ TRG_ACTION_BEFORE, FALSE))
+ DBUG_RETURN(1);
table->status|= STATUS_DELETED;
if (!(error=table->file->delete_row(table->record[0])))
+ {
deleted++;
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
+ TRG_ACTION_AFTER, FALSE))
+ DBUG_RETURN(1);
+ }
else if (!table_being_deleted->next_local ||
table_being_deleted->table->file->has_transactions())
{
@@ -614,12 +631,26 @@ int multi_delete::do_deletes(bool from_send_error)
info.ignore_not_found_rows= 1;
while (!(local_error=info.read_record(&info)) && !thd->killed)
{
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
+ TRG_ACTION_BEFORE, FALSE))
+ {
+ local_error= 1;
+ break;
+ }
if ((local_error=table->file->delete_row(table->record[0])))
{
table->file->print_error(local_error,MYF(0));
break;
}
deleted++;
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
+ TRG_ACTION_AFTER, FALSE))
+ {
+ local_error= 1;
+ break;
+ }
}
end_read_record(&info);
if (thd->killed && !local_error)
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index adb33af05b9..ce90b4ad3e0 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -398,7 +398,9 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
if (fields.elements || !value_count)
{
restore_record(table,s->default_values); // Get empty record
- if (fill_record(thd, fields, *values, 0))
+ if (fill_record_n_invoke_before_triggers(thd, fields, *values, 0,
+ table->triggers,
+ TRG_EVENT_INSERT))
{
if (values_list.elements != 1 && !thd->net.report_error)
{
@@ -419,8 +421,17 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
if (thd->used_tables) // Column used in values()
restore_record(table,s->default_values); // Get empty record
else
- table->record[0][0]= table->s->default_values[0]; // Fix delete marker
- if (fill_record(thd, table->field, *values, 0))
+ {
+ /*
+ Fix delete marker. No need to restore rest of record since it will
+ be overwritten by fill_record() anyway (and fill_record() does not
+ use default values in this case).
+ */
+ table->record[0][0]= table->s->default_values[0];
+ }
+ if (fill_record_n_invoke_before_triggers(thd, table->field, *values, 0,
+ table->triggers,
+ TRG_EVENT_INSERT))
{
if (values_list.elements != 1 && ! thd->net.report_error)
{
@@ -432,14 +443,6 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
}
}
- /*
- FIXME: Actually we should do this before
- check_that_all_fields_are_given_values Or even go into write_record ?
- */
- if (table->triggers)
- table->triggers->process_triggers(thd, TRG_EVENT_INSERT,
- TRG_ACTION_BEFORE);
-
if ((res= table_list->view_check_option(thd,
(values_list.elements == 1 ?
0 :
@@ -473,9 +476,6 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list,
if (error)
break;
thd->row_count++;
-
- if (table->triggers)
- table->triggers->process_triggers(thd, TRG_EVENT_INSERT, TRG_ACTION_AFTER);
}
/*
@@ -802,15 +802,35 @@ static int last_uniq_key(TABLE *table,uint keynr)
/*
- Write a record to table with optional deleting of conflicting records
+ Write a record to table with optional deleting of conflicting records,
+ invoke proper triggers if needed.
+
+ SYNOPSIS
+ write_record()
+ thd - thread context
+ table - table to which record should be written
+ info - COPY_INFO structure describing handling of duplicates
+ and which is used for counting number of records inserted
+ and deleted.
+
+ NOTE
+ Once this record will be written to table after insert trigger will
+ be invoked. If instead of inserting new record we will update old one
+ then both on update triggers will work instead. Similarly both on
+ delete triggers will be invoked if we will delete conflicting records.
- Sets thd->no_trans_update if table which is updated didn't have transactions
+ Sets thd->no_trans_update if table which is updated didn't have
+ transactions.
+
+ RETURN VALUE
+ 0 - success
+ non-0 - error
*/
int write_record(THD *thd, TABLE *table,COPY_INFO *info)
{
- int error;
+ int error, trg_error= 0;
char *key=0;
DBUG_ENTER("write_record");
@@ -881,25 +901,33 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info)
restore_record(table,record[1]);
DBUG_ASSERT(info->update_fields->elements ==
info->update_values->elements);
- if (fill_record(thd, *info->update_fields, *info->update_values, 0))
- goto err;
+ if (fill_record_n_invoke_before_triggers(thd, *info->update_fields,
+ *info->update_values, 0,
+ table->triggers,
+ TRG_EVENT_UPDATE))
+ goto before_trg_err;
/* CHECK OPTION for VIEW ... ON DUPLICATE KEY UPDATE ... */
if (info->view &&
(res= info->view->view_check_option(current_thd, info->ignore)) ==
VIEW_CHECK_SKIP)
- break;
+ goto ok_or_after_trg_err;
if (res == VIEW_CHECK_ERROR)
- goto err;
+ goto before_trg_err;
if ((error=table->file->update_row(table->record[1],table->record[0])))
{
if ((error == HA_ERR_FOUND_DUPP_KEY) && info->ignore)
- break;
+ goto ok_or_after_trg_err;
goto err;
}
info->updated++;
- break;
+
+ trg_error= (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+ TRG_ACTION_AFTER, TRUE));
+ info->copied++;
+ goto ok_or_after_trg_err;
}
else /* DUP_REPLACE */
{
@@ -916,20 +944,48 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info)
(table->timestamp_field_type == TIMESTAMP_NO_AUTO_SET ||
table->timestamp_field_type == TIMESTAMP_AUTO_SET_ON_BOTH))
{
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+ TRG_ACTION_BEFORE, TRUE))
+ goto before_trg_err;
if ((error=table->file->update_row(table->record[1],
table->record[0])))
goto err;
info->deleted++;
- break; /* Update logfile and count */
+ trg_error= (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+ TRG_ACTION_AFTER,
+ TRUE));
+ /* Update logfile and count */
+ info->copied++;
+ goto ok_or_after_trg_err;
+ }
+ else
+ {
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
+ TRG_ACTION_BEFORE, TRUE))
+ goto before_trg_err;
+ if ((error=table->file->delete_row(table->record[1])))
+ goto err;
+ info->deleted++;
+ if (!table->file->has_transactions())
+ thd->no_trans_update= 1;
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_DELETE,
+ TRG_ACTION_AFTER, TRUE))
+ {
+ trg_error= 1;
+ goto ok_or_after_trg_err;
+ }
+ /* Let us attempt do write_row() once more */
}
- else if ((error=table->file->delete_row(table->record[1])))
- goto err;
- info->deleted++;
- if (!table->file->has_transactions())
- thd->no_trans_update= 1;
}
}
info->copied++;
+ trg_error= (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_INSERT,
+ TRG_ACTION_AFTER, TRUE));
}
else if ((error=table->file->write_row(table->record[0])))
{
@@ -939,18 +995,27 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info)
table->file->restore_auto_increment();
}
else
+ {
info->copied++;
+ trg_error= (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_INSERT,
+ TRG_ACTION_AFTER, TRUE));
+ }
+
+ok_or_after_trg_err:
if (key)
my_safe_afree(key,table->s->max_unique_length,MAX_KEY_LENGTH);
if (!table->file->has_transactions())
thd->no_trans_update= 1;
- DBUG_RETURN(0);
+ DBUG_RETURN(trg_error);
err:
- if (key)
- my_afree(key);
info->last_errno= error;
table->file->print_error(error,MYF(0));
+
+before_trg_err:
+ if (key)
+ my_safe_afree(key, table->s->max_unique_length, MAX_KEY_LENGTH);
DBUG_RETURN(1);
}
@@ -2013,12 +2078,27 @@ bool select_insert::send_data(List<Item> &values)
DBUG_RETURN(1);
}
}
- if (!(error= write_record(thd, table,&info)) && table->next_number_field)
+ if (!(error= write_record(thd, table, &info)))
{
- /* Clear for next record */
- table->next_number_field->reset();
- if (! last_insert_id && thd->insert_id_used)
- last_insert_id=thd->insert_id();
+ if (table->triggers)
+ {
+ /*
+ If triggers exist then whey can modify some fields which were not
+ originally touched by INSERT ... SELECT, so we have to restore
+ their original values for the next row.
+ */
+ restore_record(table, s->default_values);
+ }
+ if (table->next_number_field)
+ {
+ /*
+ Clear auto-increment field for the next record, if triggers are used
+ we will clear it twice, but this should be cheap.
+ */
+ table->next_number_field->reset();
+ if (!last_insert_id && thd->insert_id_used)
+ last_insert_id= thd->insert_id();
+ }
}
DBUG_RETURN(error);
}
@@ -2027,9 +2107,11 @@ bool select_insert::send_data(List<Item> &values)
void select_insert::store_values(List<Item> &values)
{
if (fields->elements)
- fill_record(thd, *fields, values, 1);
+ fill_record_n_invoke_before_triggers(thd, *fields, values, 1,
+ table->triggers, TRG_EVENT_INSERT);
else
- fill_record(thd, table->field, values, 1);
+ fill_record_n_invoke_before_triggers(thd, table->field, values, 1,
+ table->triggers, TRG_EVENT_INSERT);
}
void select_insert::send_error(uint errcode,const char *err)
@@ -2172,7 +2254,8 @@ select_create::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
void select_create::store_values(List<Item> &values)
{
- fill_record(thd, field, values, 1);
+ fill_record_n_invoke_before_triggers(thd, field, values, 1,
+ table->triggers, TRG_EVENT_INSERT);
}
diff --git a/sql/sql_load.cc b/sql/sql_load.cc
index c827bbace3e..1545055f475 100644
--- a/sql/sql_load.cc
+++ b/sql/sql_load.cc
@@ -21,6 +21,8 @@
#include <my_dir.h>
#include <m_ctype.h>
#include "sql_repl.h"
+#include "sp_head.h"
+#include "sql_trigger.h"
class READ_INFO {
File file;
@@ -568,7 +570,11 @@ read_fixed_length(THD *thd, COPY_INFO &info, TABLE_LIST *table_list,
ER(ER_WARN_TOO_MANY_RECORDS), thd->row_count);
}
- if (fill_record(thd, set_fields, set_values, ignore_check_option_errors))
+ if (thd->killed ||
+ fill_record_n_invoke_before_triggers(thd, set_fields, set_values,
+ ignore_check_option_errors,
+ table->triggers,
+ TRG_EVENT_INSERT))
DBUG_RETURN(1);
switch (table_list->view_check_option(thd,
@@ -580,7 +586,7 @@ read_fixed_length(THD *thd, COPY_INFO &info, TABLE_LIST *table_list,
DBUG_RETURN(-1);
}
- if (thd->killed || write_record(thd,table,&info))
+ if (write_record(thd, table, &info))
DBUG_RETURN(1);
thd->no_trans_update= no_trans_update;
@@ -592,8 +598,10 @@ read_fixed_length(THD *thd, COPY_INFO &info, TABLE_LIST *table_list,
*/
if (!id && thd->insert_id_used)
id= thd->last_insert_id;
- if (table->next_number_field)
- table->next_number_field->reset(); // Clear for next record
+ /*
+ We don't need to reset auto-increment field since we are restoring
+ its default value at the beginning of each loop iteration.
+ */
if (read_info.next_line()) // Skip to next line
break;
if (read_info.line_cuted)
@@ -725,7 +733,11 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list,
}
}
- if (fill_record(thd, set_fields, set_values, ignore_check_option_errors))
+ if (thd->killed ||
+ fill_record_n_invoke_before_triggers(thd, set_fields, set_values,
+ ignore_check_option_errors,
+ table->triggers,
+ TRG_EVENT_INSERT))
DBUG_RETURN(1);
switch (table_list->view_check_option(thd,
@@ -738,7 +750,7 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list,
}
- if (thd->killed || write_record(thd, table, &info))
+ if (write_record(thd, table, &info))
DBUG_RETURN(1);
/*
If auto_increment values are used, save the first one
@@ -748,8 +760,10 @@ read_sep_field(THD *thd, COPY_INFO &info, TABLE_LIST *table_list,
*/
if (!id && thd->insert_id_used)
id= thd->last_insert_id;
- if (table->next_number_field)
- table->next_number_field->reset(); // Clear for next record
+ /*
+ We don't need to reset auto-increment field since we are restoring
+ its default value at the beginning of each loop iteration.
+ */
thd->no_trans_update= no_trans_update;
if (read_info.next_line()) // Skip to next line
break;
diff --git a/sql/sql_trigger.cc b/sql/sql_trigger.cc
index 670c618bec5..95524a6dfbf 100644
--- a/sql/sql_trigger.cc
+++ b/sql/sql_trigger.cc
@@ -85,7 +85,7 @@ bool mysql_create_or_drop_trigger(THD *thd, TABLE_LIST *tables, bool create)
DBUG_RETURN(TRUE);
}
- if (!(table->triggers= new (&table->mem_root) Table_triggers_list()))
+ if (!(table->triggers= new (&table->mem_root) Table_triggers_list(table)))
DBUG_RETURN(TRUE);
}
@@ -190,17 +190,16 @@ bool Table_triggers_list::create_trigger(THD *thd, TABLE_LIST *tables)
to other tables from trigger we won't be able to catch changes in other
tables...
- To simplify code a bit we have to create Fields for accessing to old row
- values if we have ON UPDATE trigger.
+ Since we don't plan to access to contents of the fields it does not
+ matter that we choose for both OLD and NEW values the same versions
+ of Field objects here.
*/
- if (!old_field && lex->trg_chistics.event == TRG_EVENT_UPDATE &&
- prepare_old_row_accessors(table))
- return 1;
+ old_field= new_field= table->field;
for (trg_field= (Item_trigger_field *)(lex->trg_table_fields.first);
trg_field; trg_field= trg_field->next_trg_field)
{
- trg_field->setup_field(thd, table, lex->trg_chistics.event);
+ trg_field->setup_field(thd, table);
if (!trg_field->fixed &&
trg_field->fix_fields(thd, (TABLE_LIST *)0, (Item **)0))
return 1;
@@ -318,34 +317,35 @@ Table_triggers_list::~Table_triggers_list()
for (int j= 0; j < 2; j++)
delete bodies[i][j];
- if (old_field)
- for (Field **fld_ptr= old_field; *fld_ptr; fld_ptr++)
+ if (record1_field)
+ for (Field **fld_ptr= record1_field; *fld_ptr; fld_ptr++)
delete *fld_ptr;
}
/*
- Prepare array of Field objects which will represent OLD.* row values in
- ON UPDATE trigger (by referencing to record[1] instead of record[0]).
+ Prepare array of Field objects referencing to TABLE::record[1] instead
+ of record[0] (they will represent OLD.* row values in ON UPDATE trigger
+ and in ON DELETE trigger which will be called during REPLACE execution).
SYNOPSIS
- prepare_old_row_accessors()
+ prepare_record1_accessors()
table - pointer to TABLE object for which we are creating fields.
RETURN VALUE
False - success
True - error
*/
-bool Table_triggers_list::prepare_old_row_accessors(TABLE *table)
+bool Table_triggers_list::prepare_record1_accessors(TABLE *table)
{
Field **fld, **old_fld;
- if (!(old_field= (Field **)alloc_root(&table->mem_root,
- (table->s->fields + 1) *
- sizeof(Field*))))
+ if (!(record1_field= (Field **)alloc_root(&table->mem_root,
+ (table->s->fields + 1) *
+ sizeof(Field*))))
return 1;
- for (fld= table->field, old_fld= old_field; *fld; fld++, old_fld++)
+ for (fld= table->field, old_fld= record1_field; *fld; fld++, old_fld++)
{
/*
QQ: it is supposed that it is ok to use this function for field
@@ -406,7 +406,7 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
parser->type()->length))
{
Table_triggers_list *triggers=
- new (&table->mem_root) Table_triggers_list();
+ new (&table->mem_root) Table_triggers_list(table);
if (!triggers)
DBUG_RETURN(1);
@@ -417,8 +417,11 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
table->triggers= triggers;
- /* TODO: This could be avoided if there is no ON UPDATE trigger. */
- if (triggers->prepare_old_row_accessors(table))
+ /*
+ TODO: This could be avoided if there is no triggers
+ for UPDATE and DELETE.
+ */
+ if (triggers->prepare_record1_accessors(table))
DBUG_RETURN(1);
List_iterator_fast<LEX_STRING> it(triggers->definitions_list);
@@ -478,7 +481,7 @@ bool Table_triggers_list::check_n_load(THD *thd, const char *db,
(Item_trigger_field *)(lex.trg_table_fields.first);
trg_field;
trg_field= trg_field->next_trg_field)
- trg_field->setup_field(thd, table, lex.trg_chistics.event);
+ trg_field->setup_field(thd, table);
lex_end(&lex);
}
diff --git a/sql/sql_trigger.h b/sql/sql_trigger.h
index 90c906fc72f..d61da8ff06b 100644
--- a/sql/sql_trigger.h
+++ b/sql/sql_trigger.h
@@ -8,10 +8,20 @@ class Table_triggers_list: public Sql_alloc
/* Triggers as SPs grouped by event, action_time */
sp_head *bodies[3][2];
/*
- Copy of TABLE::Field array with field pointers set to old version
- of record, used for OLD values in trigger on UPDATE.
+ Copy of TABLE::Field array with field pointers set to TABLE::record[1]
+ buffer instead of TABLE::record[0] (used for OLD values in on UPDATE
+ trigger and DELETE trigger when it is called for REPLACE).
*/
+ Field **record1_field;
+ /*
+ During execution of trigger new_field and old_field should point to the
+ array of fields representing new or old version of row correspondingly
+ (so it can point to TABLE::field or to Tale_triggers_list::record1_field)
+ */
+ Field **new_field;
Field **old_field;
+ /* TABLE instance for which this triggers list object was created */
+ TABLE *table;
/*
Names of triggers.
Should correspond to order of triggers on definitions_list,
@@ -26,8 +36,8 @@ public:
*/
List<LEX_STRING> definitions_list;
- Table_triggers_list():
- old_field(0)
+ Table_triggers_list(TABLE *table_arg):
+ record1_field(0), table(table_arg)
{
bzero((char *)bodies, sizeof(bodies));
}
@@ -36,7 +46,8 @@ public:
bool create_trigger(THD *thd, TABLE_LIST *table);
bool drop_trigger(THD *thd, TABLE_LIST *table);
bool process_triggers(THD *thd, trg_event_type event,
- trg_action_time_type time_type)
+ trg_action_time_type time_type,
+ bool old_row_is_record1)
{
int res= 0;
@@ -48,6 +59,17 @@ public:
thd->net.no_send_ok= TRUE;
#endif
+ if (old_row_is_record1)
+ {
+ old_field= record1_field;
+ new_field= table->field;
+ }
+ else
+ {
+ new_field= record1_field;
+ old_field= table->field;
+ }
+
/*
FIXME: We should juggle with security context here (because trigger
should be invoked with creator rights).
@@ -79,8 +101,13 @@ public:
bodies[TRG_EVENT_DELETE][TRG_ACTION_AFTER]);
}
+ bool has_before_update_triggers()
+ {
+ return test(bodies[TRG_EVENT_UPDATE][TRG_ACTION_BEFORE]);
+ }
+
friend class Item_trigger_field;
private:
- bool prepare_old_row_accessors(TABLE *table);
+ bool prepare_record1_accessors(TABLE *table);
};
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 95268c41aed..291f829a4e3 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -398,14 +398,13 @@ int mysql_update(THD *thd,
if (!(select && select->skip_record()))
{
store_record(table,record[1]);
- if (fill_record(thd, fields, values, 0))
+ if (fill_record_n_invoke_before_triggers(thd, fields, values, 0,
+ table->triggers,
+ TRG_EVENT_UPDATE))
break; /* purecov: inspected */
found++;
- if (table->triggers)
- table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_BEFORE);
-
if (compare_record(table, query_id))
{
if ((res= table_list->view_check_option(thd, ignore)) !=
@@ -425,6 +424,14 @@ int mysql_update(THD *thd,
{
updated++;
thd->no_trans_update= !transactional_table;
+
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+ TRG_ACTION_AFTER, TRUE))
+ {
+ error= 1;
+ break;
+ }
}
else if (!ignore || error != HA_ERR_FOUND_DUPP_KEY)
{
@@ -435,9 +442,6 @@ int mysql_update(THD *thd,
}
}
- if (table->triggers)
- table->triggers->process_triggers(thd, TRG_EVENT_UPDATE, TRG_ACTION_AFTER);
-
if (!--limit && using_limit)
{
error= -1; // Simulate end of file
@@ -1073,8 +1077,8 @@ multi_update::initialize_tables(JOIN *join)
NOTES
We can update the first table in join on the fly if we know that
- a row in this tabel will never be read twice. This is true under
- the folloing conditions:
+ a row in this table will never be read twice. This is true under
+ the following conditions:
- We are doing a table scan and the data is in a separate file (MyISAM) or
if we don't update a clustered key.
@@ -1082,6 +1086,10 @@ multi_update::initialize_tables(JOIN *join)
- We are doing a range scan and we don't update the scan key or
the primary key for a clustered table handler.
+ When checking for above cases we also should take into account that
+ BEFORE UPDATE trigger potentially may change value of any field in row
+ being updated.
+
WARNING
This code is a bit dependent of how make_join_readinfo() works.
@@ -1099,15 +1107,21 @@ static bool safe_update_on_fly(JOIN_TAB *join_tab, List<Item> *fields)
case JT_EQ_REF:
return TRUE; // At most one matching row
case JT_REF:
- return !check_if_key_used(table, join_tab->ref.key, *fields);
+ return !check_if_key_used(table, join_tab->ref.key, *fields) &&
+ !(table->triggers &&
+ table->triggers->has_before_update_triggers());
case JT_ALL:
/* If range search on index */
if (join_tab->quick)
- return !join_tab->quick->check_if_keys_used(fields);
+ return !join_tab->quick->check_if_keys_used(fields) &&
+ !(table->triggers &&
+ table->triggers->has_before_update_triggers());
/* If scanning in clustered key */
if ((table->file->table_flags() & HA_PRIMARY_KEY_IN_READ_INDEX) &&
table->s->primary_key < MAX_KEY)
- return !check_if_key_used(table, table->s->primary_key, *fields);
+ return !check_if_key_used(table, table->s->primary_key, *fields) &&
+ !(table->triggers &&
+ table->triggers->has_before_update_triggers());
return TRUE;
default:
break; // Avoid compler warning
@@ -1170,8 +1184,10 @@ bool multi_update::send_data(List<Item> &not_used_values)
{
table->status|= STATUS_UPDATED;
store_record(table,record[1]);
- if (fill_record(thd, *fields_for_table[offset],
- *values_for_table[offset], 0))
+ if (fill_record_n_invoke_before_triggers(thd, *fields_for_table[offset],
+ *values_for_table[offset], 0,
+ table->triggers,
+ TRG_EVENT_UPDATE))
DBUG_RETURN(1);
found++;
@@ -1207,8 +1223,15 @@ bool multi_update::send_data(List<Item> &not_used_values)
DBUG_RETURN(1);
}
}
- else if (!table->file->has_transactions())
- thd->no_trans_update= 1;
+ else
+ {
+ if (!table->file->has_transactions())
+ thd->no_trans_update= 1;
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+ TRG_ACTION_AFTER, TRUE))
+ DBUG_RETURN(1);
+ }
}
}
else
@@ -1329,6 +1352,11 @@ int multi_update::do_updates(bool from_send_error)
copy_field_ptr++)
(*copy_field_ptr->do_copy)(copy_field_ptr);
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+ TRG_ACTION_BEFORE, TRUE))
+ goto err2;
+
if (compare_record(table, thd->query_id))
{
if ((local_error=table->file->update_row(table->record[1],
@@ -1338,6 +1366,11 @@ int multi_update::do_updates(bool from_send_error)
goto err;
}
updated++;
+
+ if (table->triggers &&
+ table->triggers->process_triggers(thd, TRG_EVENT_UPDATE,
+ TRG_ACTION_AFTER, TRUE))
+ goto err2;
}
}
@@ -1360,6 +1393,7 @@ err:
table->file->print_error(local_error,MYF(0));
}
+err2:
(void) table->file->ha_rnd_end();
(void) tmp_table->file->ha_rnd_end();
View Lorry Controller Status