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
| CREATE SEQUENCE ID_LOG;
CREATE TABLE LOG
( ID_LOG NUMERIC(18,0) NOT NULL,
TS timestamp DEFAULT CURRENT_TIMESTAMP,
OBJECT VARCHAR(256) NOT NULL,
"VALUE" VARCHAR(256) NOT NULL,
CONSTRAINT PK_LOG PRIMARY KEY (ID_LOG)
);
-- ancienne clé primaire
CREATE INDEX IDX_LOG ON LOG (TS, OBJECT);
-- Trigger générant l'identification
SET TERM !! ;
CREATE TRIGGER LOG_BI FOR LOG
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE tmp DECIMAL(18,0);
BEGIN
-- ici on peut même gérer TS
IF (NEW.TS IS NULL) THEN NEW.TS=CURRENT_TIMESTAMP;
-- gestion id unique
IF (NEW.ID_LOG IS NULL) THEN
NEW.ID_LOG = GEN_ID(ID_LOG, 1);
ELSE
BEGIN
tmp = GEN_ID(ID_LOG, 0);
if (tmp < new.ID_LOG) then
tmp = GEN_ID(ID_LOG, new.ID_LOG-tmp);
END
END!!
SET TERM ; !! |
Partager