0

I have a table named ticket. I get the data of all tickets from another base and put it in mine with simple Insert.

By clicking a button on my website, I want the user to be able to update the tickets. Basically I would like to do all these points without making too much code :

  • The new tickets that are not already in my base will be inserted
  • The tickets already inserted which data is not up to date will be updated
  • The tickets already inserted which data is up to date will be ignored

By now, I've tried to make a trigger which looks like this:

CREATE TRIGGER before_insert_task BEFORE INSERT
ON ticket FOR EACH ROW
BEGIN
    UPDATE ticket
    SET technician = NEW.technician,
    date = NEW.date,
    closeDate = NEW.closeDate,
    solution = NEW.solution,
    content = NEW.content,
    actiontime = NEW.actiontime;
END

This does not work, I saw that you cant make trigger that update before/after insert.

Politank-Z
  • 3,653
  • 3
  • 24
  • 28
Alex85651
  • 38
  • 1
  • 8
  • 1
    In my opinion, trigger is not best approach for your problem. trigger is use to do different task before/after insert or update, example like sumOfOrder will increase if we submit new item etc etc. for your problem i suggest stored procedure to check record first with "Select" and do the logic operation like the requirement. :) – Dika Arta Karunia Jun 09 '15 at 08:17
  • 1
    Any reason a simple [`REPLACE`](http://dev.mysql.com/doc/refman/5.6/en/replace.html) won't suffice? – shmosel Jun 09 '15 at 08:30
  • 1
    Did you try this ? http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update – Reena Parekh Jun 09 '15 at 08:30
  • I don't know a lot about procedures, I will check this. I will also try `ON DUPLICATE KEY`, it looks like adapted to me. Thanks guys – Alex85651 Jun 09 '15 at 08:32

0 Answers0