1

This is my create triggers . I want when Insert vhftofass it execute this query(UPDATE vhf_msg_rx SET msg_text="";) that means Its make Null this table vhf_msg_rx.But when i write that query after insert Query it make error (Can't update table 'vhf_msg_rx' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.) Please help me how can i do this

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */

    TRIGGER `VHFtoFASS` AFTER UPDATE ON `vhf_msg_rx` 
    FOR EACH ROW BEGIN
    INSERT INTO  vhftofass  SELECT NULL,msg_text,NOW(),0 FROM vhf_msg_rx WHERE  msg_text<>"";
    END;
$$
Ashaduzaman Bhuiyan
  • 11
  • 3
  • http://stackoverflow.com/questions/1582683/mysql-trigger-stored-trigger-is-already-used-by-statement-which-invoked-stored-t – Paul Dixon Jul 18 '13 at 06:43
  • If you're inserting into `vhftofass` on every update to `vhf_msg_rx`, why is it necessary to insert every non-empty `msg_text` in the latter table on *each* occasion? Surely it would suffice to insert into `vhftofass` only the newly updated record if its `NEW.msg_text` is now non-empty? This would avoid having to read from the locked table. – eggyal Jul 18 '13 at 06:44
  • as you've found, in MySQL you can't modify a table within a update trigger assigned to that table - presumably to avoid having to deal with the recursion this could imply. Describe what you're trying to do here, there might be a way to do it with a BEFORE trigger. – Paul Dixon Jul 18 '13 at 06:45
  • If table vhf_msg_rx table come new data it will be insert table vhftofass.And make vhf_msg_rx null so that I make sure this message have in vhf_msg_rx its new message which i can not read. At a time two message may be same.If i don't do null How i understand This message new or old. Please any solution give me . thank you – Ashaduzaman Bhuiyan Jul 18 '13 at 06:56

1 Answers1

0

I'm not completely clear on what you're trying to do, but it seems like you want to copy modified rows of one table into another, but only retaining certain fields. You could do that by referencing the inserted record with NEW.columnname inside the FOR EACH ROW loop, for example

CREATE TRIGGER `VHFtoFASS` AFTER UPDATE ON `vhf_msg_rx` 
FOR EACH ROW BEGIN
    INSERT INTO  vhftofass VALUES(NULL,NEW.msg_text,NOW(), 0);
END;

Note that this will only handle updates - you'll need an INSERT trigger to capture new rows.

Paul Dixon
  • 295,876
  • 54
  • 310
  • 348
  • I understand your code .Its ok and insert vhftofass .But if I want, when Insert vhftofass table the vhf_msg_rx table will be truncate. How i do this? – Ashaduzaman Bhuiyan Jul 18 '13 at 08:43
  • You could try an after-insert trigger on vhftofass, but i think vhf_msg_rx might be locked. If that doesn't work, you could maybe use a scheduled event to automatically perform the table maintenance every few minutes. – Paul Dixon Jul 18 '13 at 09:01
  • Thank you Paul Dixon, for help.I want to using triggers. – Ashaduzaman Bhuiyan Jul 18 '13 at 10:01