[SOLVED] MySQL Database Triggers: Inserting or updating the same table

rm_-rf_windows · 03-13-2012, 04:41 PM

Hi all,

Is there a database section in LinuxQuestions? If there is, I couldn't find it. I tried some DB forums, but you don't get much response. Not a very sexy subject.

I have a very large database and need to stock data concerning elements within the same table and in the same table to avoid joining tables, for optimisation purposes.

I noticed that you cannot make an update or insert in a trigger that is triggered off by an insert on the same table.

For example, I have a "word" table. In the word table there is a 'one_word' BOOLEAN attribute (I know, the purists will tell me it should be here, but that's not the point), when someone inserts the word (or multiword expression) "Linux Questions" (two separate words separated by a space), I would like that tuple's one_word attribute set to FALSE. If someone inserts "LinuxQuestions" (in a single word) I'd like that attribute set to TRUE, with a trigger, or another way so long as it is automatically updated after an insert. The regex's don't pose a problem, of course, it's the trigger acting on the same table that activated it that does.

1/ Does anybody know if MySQL triggers can be altered to do this (doesn't even work if you put "AFTER INSERT ON..."?

and/or

2/ Does anybody know of a good work around? (There are a over a dozen attributes that have to be set or updated when a new word is inserted).

Thanks in advance,

rm

rigor · 03-13-2012, 10:02 PM

I'm not sure that you've provided enough information to reach a conclusion as to what might be best. It would probably help to know more about the overall situation.

In response to your question # 2, usually, if I had several values to be entered into a table, and I wanted substantial optimization, I'd use a stored procedure.

I would ask:

* are all the attributes about as simple as one_word, or do some require much more processing to determine ?
* why are you thinking in terms of triggers ?
* what's the reason the attributes shouldn't be set in some other way ?
* why not simply insert the attributes right along with the word, especially if the attributes are in the same table?

devnull10 · 03-14-2012, 07:08 AM

I'm not too familiar with MYSQL (I'm Oracle) so forgive me if this doesn't apply to you...

But - you should easily be able to do what you want with a BEFORE INSERT trigger. Yes, you can't select from the same table in the trigger else it will mutate, but (in Oracle at least anyway), one would just use a before insert trigger and set :NEW.one_word.

If you are updating then you will obviously change the trigger to suit.

rm_-rf_windows · 03-17-2012, 11:32 AM

Many thanks for the replies.

I am using a stored procedures to set pre-existing tuples to their proper values. The db contains some 300,000 words, expressions and miscellany phrases. However users can add words to the database and when they do so I'd like this information to be updated.

I've googled it, this is indeed a problem with MySQL, but I haven't found a solution or work-around.

Trigger:

Code:

DELIMITER |
DROP TRIGGER IF EXISTS node_trig |
CREATE TRIGGER node_trig AFTER INSERT ON node
FOR EACH ROW
BEGIN
    -- contains numbers only
IF name REGEXP '^[0-9]+$' THEN
UPDATE node SET NEW.num_only=TRUE WHERE id=NEW.id;
END IF;
END;
|
DELIMITER ;

Triggers succeeds, but queries don't:

Code:

mysql> INSERT INTO node (id, name) VALUES (999999999, "nonumbers");
ERROR 1054 (42S22): Unknown column 'name' in 'field list'
mysql> SELECT name, num_only FROM node WHERE id=999999999;
+-----------+----------+
| name      | num_only |
+-----------+----------+
| nonumbers |     NULL |
+-----------+----------+
1 row in set (0.00 sec)

mysql> INSERT INTO node (id, name) VALUES (888888888, "94893");
ERROR 1054 (42S22): Unknown column 'name' in 'field list'
mysql> SELECT name, num_only FROM node WHERE id=888888888;
+-------+----------+
| name  | num_only |
+-------+----------+
| 94893 |     NULL |
+-------+----------+
1 row in set (0.00 sec)

mysql>

rm_-rf_windows · 03-18-2012, 06:54 PM

I found the solution:

Code:

DELIMITER |
DROP TRIGGER IF EXISTS node_trig |
CREATE TRIGGER node_trig BEFORE INSERT ON node
FOR EACH ROW
BEGIN
    -- contains numbers only
IF NEW.name REGEXP '^[0-9]+$' THEN
    SET NEW.num_only=TRUE;
END IF;
END;
|
DELIMITER ;

(boldface shows what needed to be changed)

1/ Strange as it may sound, one must use BEFORE INSERT ON and not AFTER INSERT ON.

2/ Do not use UPDATE statement, use simple SET NEW.attribute.