Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table m contains the following fields:

id int(11) not null auto_increment, //PK
name varchar(100) null,
something_else varchar(100) default 'not important'

I need to fill up table m by using this trigger

CREATE TRIGGER some_name AFTER INSERT ON `x` FOR EACH ROW INSERT INTO m (name) VALUES (NEW.name);

which I got from this dba question

but the problem is that each row on m should be filled using after insert on multiple different tables like x,y. So, how can this be done.

I hope that my question is clear enough as it is in my mind.

Update 1: table m has the following row:

id - name - something_else
1  - null - not important

on after insert for table x, table m row should become like:

id - name     - something_else
1  - newthing - not important

then, on after insert for table y, table m row should become like:

id - name     - something_else
1  - newthing - new value

Hence, the row on table m had been filled by multiple inserts on different tables and updated accordingly.

share|improve this question
    
trigger is a single object level item. you will have to create one for each interested table. –  Anup Shah Oct 15 '13 at 22:42
    
What should the primary keys of x and y be (the id?) and should these be copied to m too? I see a problem there. –  ypercube Oct 16 '13 at 7:07
    
@ypercube - yes the PK is id in x and y but this will not be copied to m –  mamdouh alramadan Oct 16 '13 at 7:09
    
OK, so the m table has more or less the "Union" of the name values from both x and y. It's like a materialized view of SELECT name FROM x UNION SELECT name FROM y (with the addition of an extra something_else column.) Rolando's answer looks good then. –  ypercube Oct 16 '13 at 7:11
    
yes, it seems legit –  mamdouh alramadan Oct 16 '13 at 7:17
show 1 more comment

1 Answer

up vote 2 down vote accepted

The trigger code you just displayed in your question needs to be manually defined on each table you intend to copy from. For tables x, y and z the answer to your question is:

CREATE TRIGGER some_name AFTER INSERT ON `x` FOR EACH ROW INSERT INTO m (name) VALUES (NEW.name);
CREATE TRIGGER some_name AFTER INSERT ON `y` FOR EACH ROW INSERT INTO m (name) VALUES (NEW.name);
CREATE TRIGGER some_name AFTER INSERT ON `z` FOR EACH ROW INSERT INTO m (name) VALUES (NEW.name);

I suggest you make table m use MyISAM to prevent all potential deadlocks.

UPDATE 2013-10-15 19:39 EDT

First, define the table m like this:

CREATE TABLE m
(
    id int(11) not null auto_increment, //PK
    name varchar(100) null,
    something_else varchar(100) default 'not important',
    primary key (id),
    unique key (name)
) ENGINE = MyISAM;

Then, make the trigger like this:

CREATE TRIGGER some_name AFTER INSERT ON `x`
FOR EACH ROW
    INSERT INTO m (name) VALUES (NEW.name)
    ON DUPLICATE KEY UPDATE
    SET something_else = 'new thing'
;

I would suggest that make something_else have the date and time as well

CREATE TRIGGER some_name AFTER INSERT ON `x`
FOR EACH ROW
    INSERT INTO m (name) VALUES (NEW.name)
    ON DUPLICATE KEY UPDATE
    SET something_else = CONCAT('new thing ',NOW());
;

and maybe the table where the insert came from

CREATE TRIGGER some_name AFTER INSERT ON `x`
FOR EACH ROW
    INSERT INTO m (name) VALUES (NEW.name)
    ON DUPLICATE KEY UPDATE
    SET something_else = CONCAT('new thing from x : ',NOW());
;

Give it a Try !!!

share|improve this answer
    
Thank you Rolando, I updated my question to make clearer. –  mamdouh alramadan Oct 15 '13 at 23:27
    
Each individual trigger within a schema, of course, has to have a unique name... and MyISAM introduces new problems if you roll back an insert, leaving data behind in the MyISAM table (not to mention potential havoc in the binlog). My experience is that concerns related to InnoDB and triggers and deadlocks is significantly overblown when the triggers are doing simple things -- especially AFTER INSERT triggers on simple inserts -- because the thread you might be most likely to deadlock probably held a lock that (slightly) delayed your initial insert and will be done by the time you arrive. –  Michael - sqlbot Oct 16 '13 at 0:19
    
@Michael-sqlbot - What I got from your comment is that using triggers on insert is not a good practice? then, would transactions be a good alternative? –  mamdouh alramadan Oct 16 '13 at 7:12
    
Michael's comment says that you can use InnoDB tables for this, without issues - if I understand correctly. –  ypercube Oct 16 '13 at 7:15
    
I was responding to the suggestion that the table manipulated by the trigger should be MyISAM to avoid a perception that using InnoDB everywhere in this scenario would unnecessarily or significantly increase the risk of deadlocks. I read things like this periodically and it seems more anecdotal than empirical. Of course the more tables are involved, the more deadlock potential theoretically exists, but to introduce MyISAM to solve a problem you are not yet having is a recommendation I disagree with. I use triggers on InnoDB tables to modify other InnoDB tables routinely. –  Michael - sqlbot Oct 16 '13 at 12:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.