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.

Answer 1

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;

Answer 2

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 ;

Answer 3

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.