Firebird 3.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version
     , rdb$character_set_name
from rdb$database;


VERSION
RDB$CHARACTER_SET_NAME


3.0.10
UTF8


CREATE TABLE clients (
  c1 INT NOT NULL,                                           -- no automatic CHECK\_% triggers
  c2 INT CHECK (c2 IS NOT NULL),                             -- two auto CHECK triggers
  direction VARCHAR(16) CHECK (direction IS NOT NULL AND direction IN ('hither','yon'))); -- two more
SELECT TRIM(TRIM(RDB$RELATION_NAME) || '.' || RDB$TRIGGER_NAME) AS trigger_name,
       TRIM(RDB$TRIGGER_SOURCE) AS trigger_body,
       TRIM(CASE RDB$TRIGGER_TYPE
        WHEN 1 THEN 'BEFORE INSERT'
        WHEN 2 THEN 'AFTER INSERT'
        WHEN 3 THEN 'BEFORE UPDATE'
        WHEN 4 THEN 'AFTER UPDATE'
        WHEN 5 THEN 'BEFORE DELETE'
        WHEN 6 THEN 'AFTER DELETE'
       END) AS trigger_when,
       CASE RDB$TRIGGER_INACTIVE
        WHEN 1 THEN 'no' ELSE 'yes'
       END AS trigger_enabled,
       RDB$DESCRIPTION AS trigger_comment
  FROM RDB$TRIGGERS
 WHERE RDB$RELATION_NAME = 'CLIENTS'


TRIGGER_NAME
TRIGGER_BODY
TRIGGER_WHEN
TRIGGER_ENABLED
TRIGGER_COMMENT


CLIENTS.CHECK_1
CHECK (c2 IS NOT NULL)
BEFORE INSERT
yes
null

CLIENTS.CHECK_2
CHECK (c2 IS NOT NULL)
BEFORE UPDATE
yes
null

CLIENTS.CHECK_3
CHECK (direction IS NOT NULL AND direction IN ('hither','yon'))
BEFORE INSERT
yes
null

CLIENTS.CHECK_4
CHECK (direction IS NOT NULL AND direction IN ('hither','yon'))
BEFORE UPDATE
yes
null


INSERT INTO clients (c1, c2, direction) VALUES (NULL, 2, 'yon');
validation error for column "CLIENTS"."C1", value "*** null ***"  rows affected

INSERT INTO clients (c1, c2, direction) VALUES (1, 2, NULL);
Operation violates CHECK constraint INTEG_3 on view or table CLIENTS At trigger 'CHECK_3'  rows affected

DROP TRIGGER CHECK_4;
unsuccessful metadata update DROP TRIGGER CHECK_4 failed Triggers created automatically cannot be modified  rows affected

TRIGGER_NAME	TRIGGER_BODY	TRIGGER_WHEN	TRIGGER_ENABLED	TRIGGER_COMMENT
CLIENTS.CHECK_1	CHECK (c2 IS NOT NULL)	BEFORE INSERT	yes	null
CLIENTS.CHECK_2	CHECK (c2 IS NOT NULL)	BEFORE UPDATE	yes	null
CLIENTS.CHECK_3	CHECK (direction IS NOT NULL AND direction IN ('hither','yon'))	BEFORE INSERT	yes	null
CLIENTS.CHECK_4	CHECK (direction IS NOT NULL AND direction IN ('hither','yon'))	BEFORE UPDATE	yes	null