Hello, is it possible in some way by means of mysql when records appear in one table to selectively put them into another. For example, I have a table with mailing data containing a bunch of SMS ('Index', 'Number', 'Message Text'). And you need a second table that will contain general information about the list ('Index', 'Number of SMS').

  • 2
    With triggers Triggers - Stanislav Grotto
  • @ Stanislav CREATE TRIGGER 'Delivery' AFTER INSERT ON 'pending' FOR EACH ROW INSERT INTO delivery Set 'index' = 'pending'.'index', 'countSMS' = COUNT('pending') , CREATE TRIGGER 'Delivery' AFTER INSERT ON 'pending' FOR EACH ROW INSERT INTO delivery Set 'index' = 'pending'.'index', 'countSMS' = COUNT('pending') what is wrong? - Anton Burak
  • In Google, type "MySQL trigger" and see examples. At a minimum, the trigger body starts at begin and ends at end - Mike
  • one
    @ AntonBurak It was necessary to mention me in the last message so that I would find out what they were writing to me ... In general, the problem is there is a kopek so that the word index is reserved (it’s not necessary to call the column) and as a result the word index should be in reverse apostrophes. and the word set is not needed. With these fixes, my trigger was created and works. - Mike
  • one
    @ AntonBurak And what is most interesting, if I don’t write begin and end, then yelling that I don’t know the NEW.index column. and with them - everything is OK - Mike

1 answer 1

Ensure that the index field in the pending table is unique (for example, by creating a unique index if this field is not the primary key). After that, the trigger to add may look like this:

 DELIMITER // CREATE TRIGGER `Delivery` AFTER INSERT ON `pending` FOR EACH ROW BEGIN insert into delivery(`index`, countsms) values(NEW.`index`, 1) on duplicate key update countSMS=countSMS+1; END//

In addition, you will most likely need a delete trigger that will subtract 1 from the record count.

  • And triggers get under rules of normalization? (this clarification for me is not relevant to the question). - perfect
  • one
    @ AntonBurak Well, I don’t even know, corrected the answer to how it’s written by me - this is how you need to create it from the standard MySQL tool so that it doesn’t get confused with semicolons. In other means of working with the database may be different! - Mike
  • one
    @AntonBurak By the way, I tried phpmyadmin. with those DELIMITERs perfectly created - Mike
  • one
    @ AntonBurak I still have a field for entering the “separator” there at the bottom, you can “//” write there, instead of the DELIMITER phrase at the beginning - Mike
  • one
    @AntonBurak In MySQL, there are no triggers for several lines and there is always one entry in NEW. If it is inserted several times at a time, then the trigger is called several times so that neither - Mike