Search code examples
db2database-trigger

Using a trigger to make a table read-only in DB2

I need to make a table read-only in DB2.

Even though one option is to revoke insert, update, and delete privileges, I decided not do try this one since it would still allow the DBAs to change data in the table. Also I would like to show a nice clear error message, if possible.

Therefore, I wanted to explore the option of a DB2 trigger, but I'm afraid I'm far from an expert on the matter. When I try to create it I get an error.

At this point I've tried a myriad of variants but can't make it work. Here's the DDL:

create table employee_state (
  id int primary key not null,
  description varchar(40) not null
);

insert into employee_state (id, description) values (1, 'Applying');
insert into employee_state (id, description) values (2, 'Rejected');
insert into employee_state (id, description) values (3, 'Active');
insert into employee_state (id, description) values (4, 'Inactive');

create trigger employee_state_read_only 
  before delete on employee_state for each statement
begin
  raise_error(-20001, 
    'Delete operation not allowed on read-only table "employee_state".');
end//

DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=raise_error;ach statement
begin
;RETURN, DRIVER=4.21.29

What am I doing wrong?

Solution

  • Yo can use SIGNAL SQLSTATE with a custom message.

    CREATE TRIGGER EMPLOYEE_STATE_READ_ONLY 
BEFORE DELETE ON EMPLOYEE_STATE FOR EACH ROW
WHEN (1=1)
  BEGIN ATOMIC
    SIGNAL SQLSTATE '80001' SET MESSAGE_TEXT = 'READ ONLY';
  END

    Then on delete attempt it will throw errors like SQL0723 - SQL trigger EMPLOYEE_STATE_READ_ONLY in GLTCUAT failed with SQLCODE -438 SQLSTATE 80001.