CODEWITHSUNDEEP
mysqlsqltriggers
Allen
Allen

Reputation: 472

sql trigger inserting row into two tables

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.

Upvotes: 5

Views: 10279

Answers (5)

Kushal Sharma
Kushal Sharma

Reputation: 230

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 ;

Upvotes: 3

Allen
Allen

Reputation: 472

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.

Upvotes: 1

Jan
Jan

Reputation: 16038

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;

Upvotes: 4

Will Marcouiller
Will Marcouiller

Reputation: 24142

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.

Upvotes: 1

Eppz
Eppz

Reputation: 3226

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.

Upvotes: 0

Related Questions