0

So I'm trying to do the following: one table, queries like:

INSERT INTO ... (multiple), (values) ON DUPLICATE KEY UPDATE ...

so far this works just fine, no problem. Now I would like to add a trigger which does the following: if NEW.field = 0 it deletes a row from the table (with same primary key) AND prevents the INSERT/UPDATE operation. And this is where I'm lost. How to prevent inserting/updating only that SINGLE row? My understanding is that if I do SIGNAL SQLSTATE ... that would break the whole statement, so none of the records would be inserted/updated, which is not what I want.

I was thinking doing:

INSERT INTO IGNORE... (multiple), (values) ON DUPLICATE KEY UPDATE ... (notice: IGNORE)

but... would this work fine? Does IGNORE ignores the errors for both insert AND the update trigger as well?

I know the obvious (and easy) workaround would be to just use AFTER triggers instead of BEFORE, but since it's a busy table with many thousands of insertion/update per second I wouldn't really want to do that.

  • would this work fine? IGNORE conflicts with ODKU. Either first or second, not both. I would like to add a trigger which does the following: if NEW.field = 0 it deletes a row from the table (with same primary key) AND prevents the INSERT/UPDATE operation. You want INSERT if no duplication or DELETE if dup? Impossible, trigger cannot affect on any (ever the same) record in table it is defined on except altering NEW pseudotable. – Akina May 29 '18 at 20:26
  • This is frustrating. :/ I actually want to insert if the record doesn't exist OR update if it does, both in the case when NEW.field = 1, otherwise I just want to delete the existing record (if there's one) and prevent the insert/update. Is there any way to do this? – Andrew May 29 '18 at 20:42
  • You cannot realize such logic as one query. I'd recommend to create stored procedure performed the action You need. – Akina May 29 '18 at 20:46
  • I'm not really familiar with procedures, but after Googling a bit, would it even be possible to send multiple records to a single procedure call? It looks like I would lose the ability to send one "big" query instead of many single small ones. – Andrew May 29 '18 at 21:15
  • would it even be possible to send multiple records to a single procedure call? No, of course. The standard way to send multiple records to a single procedure call is to insert them into (temporary) table and then call the procedure which extract them and perform the operations needed - altogether or one-by-one... – Akina May 29 '18 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.