sql trigger inserting row into two tables

Question

I was looking for a way to create a trigger that would insert the same row into two tables with the same values.

For example, a new row is inserted into pushNotificationQueue as soon as that is inserted, I would like that same exact row to be inserted into messages.

I tried this

CREATE TRIGGER add_to_messages
after insert on mbb_pushNotificationQueue
FOR EACH ROW
insert into mbb_messages select * from mbb_pushNotificationQueue

the only problem with that is that it goes through and adds entries that have already been previously added.

Jan · Accepted Answer · 2010-12-23 21:08:04Z

4

You have to say with which rdbms you are working. Anyway, you have to use a special table normally named inserted or similar.

This is for Sql Server:

INSERT INTO mbb_messages SELECT * FROM INSERTED

Others like Sybase use a REFERENCES clause to get to the newly inserted record:

create trigger TriggerName after insert on
TableName
referencing new as new_name

And for MySQL (which you are seem to use) you can refer to the newly inserted records by using the NEW table:

CREATE TRIGGER add_to_messages
after insert on mbb_pushNotificationQueue
FOR EACH ROW BEGIN
    insert into mbb_messages select * from NEW;
END;

edited Dec 23, 2010 at 21:08

answered Dec 23, 2010 at 20:45

Jan

16.1k5 gold badges38 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Allen Over a year ago

I tried using the NEW, but it says that it doesn't exist, the same goes for INSERTED.

Jan Over a year ago

INSERTED is for Sql Server. NEW should work for MySQL. Try to enclose your INSERT into a BEGIN / END block. I have updated my answer. See the MySql reference manual for the description and examples: dev.mysql.com/doc/refman/5.0/en/create-trigger.html

Allen Over a year ago

I tried the updated code, and it gives me the error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5.

Jan Over a year ago

Maybe it was the missing semicolons? I'm not an MySQL expert :)

Allen Over a year ago

Hi Jan, I actually tried adding the semicolons myself, but it's still giving me the same error, it just moves the line number up. I appreciate the help though.

Kushal Sharma · Accepted Answer · 2016-04-19 09:18:22Z

3

You need to use the column name with new keyword. Please find the trigger below:

DELIMITER $$
CREATE TRIGGER add_to_message
after insert on mbb_pushNotificationQueue
FOR EACH ROW BEGIN
    insert into mbb_oushNotificationQueue(`col1`, `col2`) values(new.col1, new.col2);
END$$
DELIMITER ;

answered Apr 19, 2016 at 9:18

Kushal Sharma

2301 gold badge2 silver badges10 bronze badges

Comments

Will Marcouiller · Accepted Answer · 2010-12-23 20:51:07Z

1

First of all, I say that using select * with an insert-select statement is really, really a bad idea. The reason is that you can never predict the order of the columns that are returned from a selection.

Secondly, assuming SQL Server, I would suggest using the following:

create trigger add_to_message
    instead of insert on mbb_pushNotificationQueue
    for each row 
as
    begin transaction
        insert into mbb_oushNotificationQueue (col1, col2, col3)
            select col1, col2, col3
                from inserted

        insert into mbb_messages (col1, col2, col3)
            select col1, col2, col3
                from inserted

       if @@ERROR_LEVEL = 0
           commit
      else
           rollback

Disclaimer:

This code has not been tested and may require some minor fixes, but is illustrating the idea very well.

answered Dec 23, 2010 at 20:51

Will Marcouiller

24.3k26 gold badges100 silver badges166 bronze badges

2 Comments

Allen Over a year ago

Hi Will, will this work for MySQL? I don't have a ton of experience with it, when I tried using this it told me the syntax wasn't correct on the instead of line.

Allen Over a year ago

I've tried that, it doesn't resolve any of the issues. I've played around with the syntax a bit.

Allen · Accepted Answer · 2010-12-23 22:22:47Z

1

I ended up using

CREATE TRIGGER add_to_messages
after insert on mbb_pushNotificationQueue
FOR EACH ROW
INSERT INTO mbb_messages SET messageID = NEW.messageID, 
toUserID = NEW.toUserID, 
fromUserID =  NEW.fromUserID, message = NEW.message, dateReceived = NEW.dateReceived

Thanks to everyone who posted.

answered Dec 23, 2010 at 22:22

Allen

4821 gold badge6 silver badges19 bronze badges

2 Comments

Will Marcouiller Over a year ago

Besides, will this guarantee data integrity, that is, if one of the inserts fail, what happens? Is it possible that one insert will be performed without the other one being successful? That is the concern of the transaction in my code sample. And sorry, I didn't see you were using MySQL.

Allen Over a year ago

I do not want it to insert into the second table if the first fails, the mbb_messages table is to keep a log of messages that have been sent out, as the mbb_pushNotificationQueue will be cleared every so often.

Eppz · Accepted Answer · 2010-12-23 20:46:46Z

0

So only add the last record added.

insert into mbb_messages select blah from mbb_pushNotificationQueue where blah meets some criteria....

Having max(id) or something.

answered Dec 23, 2010 at 20:46

Eppz

3,2362 gold badges21 silver badges26 bronze badges

1 Comment

Jan Over a year ago

No, thats very dangerous. Every rdbms gives you the inserted records via a special table or a REFERENCES clause

Collectives™ on Stack Overflow

sql trigger inserting row into two tables

5 Answers 5

5 Comments

Comments

2 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

2 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related