3

I have a complicated situation where I would like to prevent inserts into a MySQL table, but keep it open for edits/updates to existing data. Can this be done? I've searched around here, but could not find a similar situation.

2 Answers 2

4

I would use a before insert trigger:

CREATE TRIGGER trigger_name
BEFORE INSERT
   ON table_name FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE VALUE '99999'
      SET MESSAGE_TEXT = 'You cannot insert rows to this table.';
END;
2
  • CREATE TRIGGER no_more_work_orders BEFORE INSERT ON work_orders FOR EACH ROW BEGIN SIGNAL SQLSTATE VALUE '99999' SET MESSAGE_TEXT = 'You can no longer use this system to create work orders.'; END; Caused the following error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SQLSTATE VALUE '99999' SET MESSAGE_TEXT = 'You can no longer use this' at line 5 Oct 23, 2014 at 20:15
  • Anyone reading this needs to add DELIMTER to make this work. After which it works perfect.
    – Bacon Brad
    Oct 26, 2016 at 19:32
1

Use a before insert trigger to set the new values back to the old ones? Or, even better, set it to do nothing at all - as per below. As Eric Raymond pointed out in his (good) book "The Art of Unix Programming", the fastest code is that which doesn't have to run at all!. (Note - don't have a running system to test - but I think the underlying concept is pretty clear!).

DELIMITER $

CREATE TRIGGER blank_insert BEFORE INSERT ON test1
  FOR EACH ROW
  BEGIN
    ;
  END;
$

DELIMITER ;

Perhaps an even more elegant way would be to do what's outlined here - i.e. set PRIVILEGES to UPDATE and SELECT only for your users on your table?

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.