1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107
| CREATE DATABASE '..\..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET WIN1252;
-- =========
-- Exception
-- =========
create exception verif '''val'' compris entre 10 et 25 !';
-- =====================
-- création table "test"
-- =====================
create table test (
id integer generated by default as identity not null constraint pk primary key,
val decimal(15,2) constraint verif check (val between 10 and 25 and val is not null),
num integer not null
);
-- =======
-- Trigger
-- =======
SET TERM #;
CREATE TRIGGER verif
FOR test ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
IF (new.num not between 10 and 25) THEN exception verif;
END#
SET TERM ;#
commit;
-- =====================
-- insertion dans 'test'
-- =====================
insert into test (val,num) values (15.0, 12);
insert into test (val,num) values (33.0, 12);
Statement failed, SQLSTATE = 23000
Operation violates CHECK constraint VERIF on view or table TEST
-At trigger 'CHECK_1'
After line 41 in file Base.sql
insert into test (val,num) values (12.0, 12);
insert into test (val,num) values ( 6.0, 12);
Statement failed, SQLSTATE = 23000
Operation violates CHECK constraint VERIF on view or table TEST
-At trigger 'CHECK_1'
After line 43 in file Base.sql
insert into test (val,num) values (20.0, 12);
insert into test (val,num) values (12.0, 15);
insert into test (val,num) values (12.0, 33);
Statement failed, SQLSTATE = HY000
exception 1
-VERIF
-'val' compris entre 10 et 25 !
-At trigger 'VERIF' line: 6, col: 43
After line 48 in file Base.sql
insert into test (val,num) values (12.0, 12);
insert into test (val,num) values (12.0, 6);
Statement failed, SQLSTATE = HY000
exception 1
-VERIF
-'val' compris entre 10 et 25 !
-At trigger 'VERIF' line: 6, col: 43
After line 50 in file Base.sql
insert into test (val,num) values (12.0, 20);
-- ================
-- Vidage de 'test'
-- ================
select * from test;
ID VAL NUM
============ ===================== ============
1 15.00 12
3 12.00 12
5 20.00 12
6 12.00 15
8 12.00 12
10 12.00 20
-- =====================
-- Liste des contraintes
-- =====================
select *
from RDB$RELATION_CONSTRAINTS
where RDB$RELATION_NAME = 'TEST';
RDB$CONSTRAINT_NAME RDB$CONSTRAINT_TYPE RDB$RELATION_NAME RDB$DEFERRABLE RDB$INITIALLY_DEFERRED RDB$INDEX_NAME
=============================== =================== =============================== ============== ====================== ===============================
PK PRIMARY KEY TEST NO NO PK
VERIF CHECK TEST NO NO <null>
INTEG_1 NOT NULL TEST NO NO <null>
exit;
Appuyez sur une touche pour continuer... |
Partager