ansaurus

Question

how to activate trigger after all the bulk insert(s) in mysql

Answer 1

A:

If your concern is performance, you can rest assured that the operation is pretty fast, even though it is performed on each inserted row separately. But if you really think this will lead to performance problems, you should profile.

The single alternative I can think of is dropping the trigger, performing the insert query, and re-adding the trigger, which is actually a horrible solution (unfortunately you cannot disable triggers for the session in mysql - actually you cannot disable them at all).

soulmerge 2010-03-01 13:45:59

updated the question

JPro 2010-03-01 13:58:02

Answer 2

A:

The trigger syntax (>>after insert on<<) inherently says that it will be done for every insert. I don't know of a way to switch that off. A possible workaround could be to have a flag (some magic value for one of the columns e.g id column of -200?) in your insert statement that is only true for the very last insert and then have an if statement iside your trigger that checks the flag. Feels a bit like a hack though. Perhaps you can give us a bit more context, there may be a better way of skinning this cat.

Hannes de Jager 2010-03-01 13:46:54

some external source bulk updates my datbase and I want to fill in some additional fileds based on the values that it supplies, which is for the same table by the way. I though it might cause conflict when trigger gets activated and tries to update the table, whilew another insert is going on for the same table. Any ideas?

JPro 2010-03-01 13:53:33

Have no docs to confirm this, but the trigger will probably finish its work before the next row will be inserted. Even if not, mysql will handle the locking for you. Innodb supports row-level locking which means if one thread is busy updating a row others will not be able to update it as well. Don't think you have to worry about conflicts. Do you want to update fields in the same row that the trigger is activated for of do you want to insert/update another row?

Hannes de Jager 2010-03-01 14:13:43

it gives the following errors `ERROR: ODBC: State S1000: [MySQL][ODBC 5.1 Driver][mysqld-5.1.37]Can't update table 'my_table' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

JPro 2010-03-01 14:27:52

I assume then that you want to update a different row in the table then? If you are trying to update the same row that was inserted then use the NEW.col_name syntax which is available in insert triggers. Otherwise how about using mysql events (http://dev.mysql.com/tech-resources/articles/mysql-events.html) to perform what you want to do periodically. Maybe you should give us more context on what is is that you want to achieve to prevent us from giving answers that won't work.

Hannes de Jager 2010-03-02 07:45:04

Answer 3

+1 A:

take a look here and see if you can implement this trick that basically wraps the trigger in an if statement controlled by a variable you can switch on and off

if (@DISABLE_TRIGER <> 1 ) then
#trigger body
end if;

and than if you want to disable triggers on import just:

SET @DISABLE_TRIGER = 1;

#do imports

SET @DISABLE_TRIGER = 0;

CheeseConQueso 2010-03-23 22:40:23

ansaurus

tags:

views:

answers:

how to activate trigger after all the bulk insert(s) in mysql

related questions