Hi Michael
My environment :
-
SQL Nav 7.3 beta 32 bit, but it also occurred in former versions like 7.2
-
OS : Win 7 64 bit pro
-
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Here the DDL :
– Start of DDL Script for Table PLDA.S_TARIC_CEE_NEW
– Generated 20/05/2016 09:53:39 from PLDA@D_plda.world
CREATE TABLE s_taric_cee_new
(cod_pays VARCHAR2(2 BYTE) NOT NULL,
cod_taric VARCHAR2(10 BYTE) NOT NULL,
dat_valid_from DATE NOT NULL,
dat_valid_to DATE,
dat_insert DATE,
dat_update DATE,
mat_insert NUMBER(5,0),
mat_update NUMBER(5,0),
remarque VARCHAR2(100 BYTE))
SEGMENT CREATION IMMEDIATE
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE plda
STORAGE (
INITIAL 1024000
NEXT 1024000
MINEXTENTS 1
MAXEXTENTS 2147483645
)
NOCACHE
MONITORING
NOPARALLEL
NOLOGGING
/
– Constraints for S_TARIC_CEE_NEW
ALTER TABLE s_taric_cee_new
ADD CONSTRAINT pk_s_taric_cee_new PRIMARY KEY (cod_pays, cod_taric)
USING INDEX
PCTFREE 5
INITRANS 2
MAXTRANS 255
TABLESPACE indx_plda
STORAGE (
INITIAL 1024000
NEXT 1048576
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/
– Triggers for S_TARIC_CEE_NEW
CREATE OR REPLACE TRIGGER s_taric_cee_new_b_ins_upd
BEFORE
INSERT OR UPDATE
ON s_taric_cee_new
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
vn_count PLS_INTEGER;
BEGIN
IF INSERTING THEN
:new.dat_insert := SYSDATE;
:new.mat_insert := pk_base.f_get_matr;
:new.dat_update := SYSDATE;
:new.mat_update := pk_base.f_get_matr;
:new.dat_valid_from := NVL(:new.dat_valid_from, TRUNC(SYSDATE));
:new.dat_valid_to := NVL(:new.dat_valid_to, TRUNC(SYSDATE) + 30);
ELSIF UPDATING THEN
:new.dat_update := SYSDATE;
:new.mat_update := pk_base.f_get_matr;
END IF;
SELECT COUNT(*)
INTO vn_count
FROM s_taric_cee
WHERE cod_pays = :new.cod_pays AND cod_taric = :new.cod_taric;
IF vn_count = 1 THEN
vn_count := 1 / 0; – Code taric existe déjà dans s_taric_cee !
END IF;
END;
/
– Comments for S_TARIC_CEE_NEW
COMMENT ON TABLE s_taric_cee_new IS ‘Codes tarics qui ont été ajoutés au courant du mois et ne sont donc pas encore présents dans s_taric_cee… et s_taric_meas. La table sert aux tests dynamiques afin de ne pas exécuter du codes pour ces nouveaux tarics non documentés’
/
COMMENT ON COLUMN s_taric_cee_new.cod_pays IS ‘Code Pays’
/
COMMENT ON COLUMN s_taric_cee_new.cod_taric IS ‘Code taric’
/
COMMENT ON COLUMN s_taric_cee_new.dat_insert IS ‘Date insertion record’
/
COMMENT ON COLUMN s_taric_cee_new.dat_update IS ‘Date modification record’
/
COMMENT ON COLUMN s_taric_cee_new.dat_valid_from IS ‘Code valide a partir de …’
/
COMMENT ON COLUMN s_taric_cee_new.dat_valid_to IS ‘Code valide jusqu’‘au …’
/
COMMENT ON COLUMN s_taric_cee_new.mat_insert IS ‘Matricule insertion record’
/
COMMENT ON COLUMN s_taric_cee_new.mat_update IS ‘Matricule modification record’
/
COMMENT ON COLUMN s_taric_cee_new.remarque IS ‘Remarque éventuelle’
/
– End of DDL Script for Table PLDA.S_TARIC_CEE_NEW
Regards
Martin
