patternsqlMinor

Can I permanently lock a MySQL table to prevent inserts

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

canpermanentlypreventinsertsmysqltablelock

Problem

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.

Solution

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;

Code Snippets

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;

Context

StackExchange Database Administrators Q#80969, answer score: 4

Revisions (0)

No revisions yet.