Example of preventing operations on tables using triggers

Suppose you want to prevent undeliverable email from being stored in a table named ELECTRONIC_MAIL. To do so, you must prevent the execution of certain SQL INSERT statements.

There are two ways to do this:

Define a BEFORE trigger that returns an error whenever the subject of an email is undelivered mail:

  CREATE TRIGGER BLOCK_INSERT 
    NO CASCADE BEFORE INSERT ON ELECTRONIC_MAIL 
    REFERENCING NEW AS N 
    FOR EACH ROW
    WHEN (SUBJECT(N.MESSAGE) = 'undelivered mail') 
    BEGIN ATOMIC 
      SIGNAL SQLSTATE '85101'
        SET MESSAGE_TEXT = ('Attempt to insert undelivered mail'); 
       END

Define a check constraint forcing values of the new column SUBJECT to be different from undelivered mail:

  ALTER TABLE ELECTRONIC_MAIL 
    ADD CONSTRAINT NO_UNDELIVERED 
    CHECK (SUBJECT <> 'undelivered mail')