Annex 1 to Martin et al (2021) “Data Curation, Fisheries and Ecosystem-based Management: The Case Study of the Pecheker Database”

https://doi.org/10.2218/ijdc.v16i1.674


SQL script to deploy the full Pecheker model, including Oracle sequences and triggers


--Creation script of the new PECHEKER relational model
/*SQL creation script for the deployement of the PECHEKER relational model including the code for tables, sequences and triggers allowing the mangement of the creation date and ID*/

--p_campagne

CREATE TABLE p_campagne -- all the cruises, commercial, exploratory or scientific 
(
pkcampagne INTEGER PRIMARY KEY,
fkrole INTEGER REFERENCES p_role(pkrole),
fkarmement INTEGER REFERENCES p_armement(pkarmement),
fknavire INTEGER REFERENCES p_navire(pknavire),
nom VARCHAR2 (50), -- cruise name
rang NUMBER (15), -- cruise rank
nature VARCHAR2 (50), -- cruise nature. For example commercial, exploratory or scientific
date_dep DATE, -- cruise start date
date_ret DATE, -- cruise end date
maree VARCHAR2 (15), -- cruise code including the season and the cruise number
ce_cp_xls_fichier VARCHAR2 (50), -- name of the data source file
code_fao_espece_cible VARCHAR2 (15),
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row 
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_campagneSEQ start with 1;

CREATE OR REPLACE TRIGGER CRp_campagne 
BEFORE INSERT ON pecheker.p_campagne 
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW DECLARE
   numero p_campagne.pkcampagne%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkcampagne is NULL THEN
select p_campagneSEQ.nextval into numero from dual;
:new.pkcampagne:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_campagne 
BEFORE UPDATE ON pecheker.p_campagne 
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/

--p_station

CREATE TABLE p_station -- all the stations (setting)
(
pkstation INTEGER PRIMARY KEY,
fkengin INTEGER REFERENCES p_engin(pkengin),
fkcampagne INTEGER REFERENCES p_campagne(pkcampagne),
fkrole INTEGER REFERENCES p_role(pkrole),
fksecteur INTEGER REFERENCES p_secteur(pksecteur),
station NUMBER, -- station number
nature VARCHAR2 (50), --station nature (for example commercial or research)
date_deb DATE, --station start date
date_fin DATE, --station end date
deb_lat_deg NUMBER, --station start latitude (in degres)
deb_lat_min NUMBER, --station start latitude (in minutes)
deb_lon_deg NUMBER, --station start longitude (in degres)
deb_lon_min NUMBER, --station start longitude (in minutes)
deb_sonde NUMBER, --station start depth (in meters)
fin_lat_deg NUMBER, --station end latitude (in degres)
fin_lat_min NUMBER, --station end latitude (in minutes)
fin_lon_deg NUMBER, --station end longitude (in degres)
fin_lon_min NUMBER, --station end longitude (in minutes)
fin_sonde NUMBER, --station end depth (in meters)
date_fond DATE, --for bottom trawl, date when the gear touches the bottom
fond_lat_deg NUMBER, --for bottom trawl, latitude (in degres) when the gear touches the bottom
fond_lat_min NUMBER, --for bottom trawl, latitude (in minutes) when the gear touches the bottom
fond_lon_deg NUMBER, --for bottom trawl, longitude (in degres) when the gear touches the bottom
fond_lon_min NUMBER, --for bottom trawl, longitude (in minutes) when the gear touches the bottom
fond_sonde NUMBER, --for bottom trawl, depth (in meters) when the gear touches the bottom
zone_spatial VARCHAR2 (50), --area code
c_deb_lon NUMBER, --station start longitude (in decimal)
c_deb_lat NUMBER, --station start latitude (in decimal)
c_fin_lon NUMBER, --station end longitude (in decimal)
c_fin_lat NUMBER, --station end latitude (in decimal)
c_fond_lon NUMBER, --for bottom trawl, longitude (in decimal) when the gear touches the bottom
c_fond_lat NUMBER, --for bottom trawl, latitude (in decimal) when the gear touches the bottom
ce_cp_xls_fichier VARCHAR2 (50), -- name of the data source file
observation VARCHAR2(255 BYTE), --observation,
ref_chalut VARCHAR2(30 BYTE), --trawl reference
remarque VARCHAR2 (255), -- comment
ik INTEGER, --internal key
ID_journee VARCHAR2(50 BYTE), --intenal ID for the daily management of the Saint Paul et Amsterdam fishery
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_stationSEQ start with 1;

CREATE OR REPLACE TRIGGER CRp_station 
BEFORE INSERT ON pecheker.p_station 
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW DECLARE
   numero p_station.pkstation%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkstation is NULL THEN
select p_stationSEQ.nextval into numero from dual;
:new.pkstation:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_station 
BEFORE UPDATE ON pecheker.p_station 
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/

--p_desc

CREATE TABLE p_desc -- all the descriptive data of stations, events and cruises. 
--nested structure where data is qualified by a domain, a category, an information type, a variable, a unit and a quality index
(
pkdesc INTEGER PRIMARY KEY,
fkcampagne INTEGER REFERENCES p_campagne(pkcampagne),
fkstation INTEGER REFERENCES p_station(pkstation),
fkevt INTEGER REFERENCES p_evt(pkevt),
fkobs INTEGER REFERENCES p_obs(pkobs) ,
domaine VARCHAR2 (255), -- first level to qualify the data (for example fishery method)
categorie VARCHAR2 (255), -- second level to qualify the data (for example hook number)
information VARCHAR2 (255), -- third level to qualify the data (for example hook number of type 1)
inf_norm VARCHAR2 (50), -- additional column to qualify the data
variable VARCHAR2 (255), --value of the qualified data 
unite_variable VARCHAR2 (255), --unit of the data
qualite_variable VARCHAR2 (50), -- fourth level to qualify the data (for example during setting or during hauling) 
observation VARCHAR2 (255), --observation
ce_cp_xls_fichier VARCHAR2 (50), -- name of the data source file
remarque VARCHAR2 (255), -- comment
ID_evt VARCHAR2 (255), --internal event ID
ID_obs VARCHAR2 (255), --internal observation ID
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_descSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_desc 
BEFORE INSERT ON pecheker.p_desc
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW DECLARE
   numero p_desc.pkdesc%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkdesc is NULL THEN
select p_descSEQ.nextval into numero from dual;
:new.pkdesc:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_desc 
BEFORE UPDATE ON pecheker.p_desc 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/

--p_evt

CREATE TABLE p_evt -- all the events related to a cruise or a station (for example hauling, 25% observation…)
(
pkevt INTEGER PRIMARY KEY,
fkcampagne INTEGER REFERENCES p_campagne(pkcampagne),
fkstation INTEGER REFERENCES p_station(pkstation),
fkembarcation INTEGER REFERENCES p_embarcation (pkembarcation),
fkengin INTEGER REFERENCES p_engin (pkengin),
ce_cp_xls_fichier VARCHAR2 (50), -- name of the data source file
station NUMBER, --station number of the associated event
rang NUMBER, --event rank
type VARCHAR2 (50), --event type (for example haul, 25% observation, bird counting…)
quart NUMBER, --quarter number (for the 25% observation)
segment NUMBER, --segment number (for the VME observation)
date_deb DATE, --event start date
date_fin DATE, -- event end date
deb_lat_deg NUMBER, -- event start latitude (in degres)
deb_lat_min NUMBER, -- event start latitude (in minutes)
deb_lon_deg NUMBER, -- event start longitude (in degres)
deb_lon_min NUMBER, -- event start longitude (in minutes)
deb_sonde NUMBER, -- event start depth (in meters)
fin_lat_deg NUMBER, -- event end latitude (in degres)
fin_lat_min NUMBER, -- event end latitude (in minutes)
fin_lon_deg NUMBER, -- event end longitude (in degres)
fin_lon_min NUMBER, -- event end longitude (in minutes)
fin_sonde NUMBER, -- event end depth (in meters)
c_deb_lon NUMBER, -- event start longitude (in decimal)
c_deb_lat NUMBER, -- event start latitude (in decimal)
c_fin_lon NUMBER, -- event end longitude (in decimal)
c_fin_lat NUMBER, -- event end latitude (in decimal)
prof_deb NUMBER, -- evant start bathymetry (in meters)
prof_fin NUMBER, -- evant end bathymetry (in meters)
observation VARCHAR2 (255), --observation
remarque VARCHAR2 (255), -- comment
ID_evt VARCHAR2 (255), --internal event ID
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_evtSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_evt 
BEFORE INSERT ON pecheker.p_evt 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_evt.pkevt%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkevt is NULL THEN
select p_evtSEQ.nextval into numero from dual;
:new.pkevt:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_evt 
BEFORE UPDATE ON pecheker.p_evt 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/

--p_obs

CREATE TABLE p_obs -- all the observation and counting (for examples for marine mammals observation during hauling or birds counting)
(
pkobs INTEGER PRIMARY KEY,
fkstation INTEGER REFERENCES p_station(pkstation),
fkevt INTEGER REFERENCES p_evt(pkevt),
fktaxon INTEGER REFERENCES p_taxon(pktaxon),
libelle_taxon VARCHAR2 (50), --species name as used in data source file,
type VARCHAR2 (50), -- observation type (for example marine mammals, bird or VME counting, opportunistic observations…) 
qualification VARCHAR2 (50), -- observation precision (for example in flight, on the water…) 
etat VARCHAR2 (30), -- state of the observed individual (for example alive, dead, injured…)
sexe VARCHAR2 (30), -- sex of the observed individual (for example male, female, unknown or undetermined)
stade VARCHAR2 (30), -- maturity stage of the observed individual (for example adult, juvenil or a stage code)
ind VARCHAR2 (30), -- used when disctinction between sex or maturity stage can not be done (for example no disctintion between female 
-- and juvenil
nb NUMBER, --number of observed individuals
nb_min NUMBER, -- minimal number of observed individuals
nb_max NUMBER, -- maximal number of observed individuals
pres_abs VARCHAR2 (30), -- presence or absence of the observed individuals
poids NUMBER, -- weight of observed individuals (in kg)
volume NUMBER, -- volume of observed individuals (in m3 for benthic organisms)
ce_cp_xls_fichier VARCHAR2 (50), -- name of the data source file
observation VARCHAR2 (255), -- observation
remarque VARCHAR2 (255), -- comment
ID_obs VARCHAR2 (255), -- internal observation ID
ID_evt VARCHAR2 (255), -- internal event ID
ok INTEGER, -- old internal key
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_obsSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_obs 
BEFORE INSERT ON pecheker.p_obs
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_obs.pkobs%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkobs is NULL THEN
select p_obsSEQ.nextval into numero from dual;
:new.pkobs:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_obs 
BEFORE UPDATE ON pecheker.p_obs 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/


--p_capture

CREATE TABLE p_capture  -- all the catches
(
pkcapture INTEGER PRIMARY KEY,
fkstation INTEGER REFERENCES p_station(pkstation),
fkevt INTEGER REFERENCES p_evt(pkevt),
fktaxon INTEGER REFERENCES p_taxon(pktaxon),
libelle_taxon VARCHAR2 (50), --species name as used in data source file,
type VARCHAR2 (50), -- catches type (for example catches reported by the crew or catches reported by the fishery observer during the 
-- 25% observation)
qualite VARCHAR2 (255), -- catches quality (for example precision if the catches were weighted or estimated…) 
mis_bord VARCHAR2 (50), --  landed on board or not
produit VARCHAR2 (50), -- fish product (for example fillet, headed gutted and tailed …)
etat VARCHAR2 (50), -- fish state (for example fresh or frozen)
destination VARCHAR2 (255), -- fish destination (for example retained or discarded)
calibre VARCHAR2 (50), -- caliber for certain species as rock lobster
nb NUMBER, -- number of individuals
poids_net NUMBER, -- processed weight, -- processed weight in kg
poids_brut NUMBER, -- green weight -- green weight (does not come from a processed weight so no conversion factor involved) in kg
poids_brut_in NUMBER, -- green weight from processed weight (this weight is calculated using the conversion factors and the processed
 -- weights) in kg
poids_net_cd NUMBER, -- corrected processed weight in kg (this takes into account the landings in port and reweight the all cargo)
poids_brut_cd NUMBER, -- corrected green weight in kg (this takes into account the landings in port and reweight the all cargo)
observation VARCHAR2 (255),
etat_puce VARCHAR2 (50),
sonde NUMBER,
ce_cp_xls_fichier VARCHAR2 (50), -- name of the data source file
ok INTEGER,
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_captureSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_capture 
BEFORE INSERT ON pecheker.p_capture 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_capture.pkcapture%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkcapture is NULL THEN
select p_captureSEQ.nextval into numero from dual;
:new.pkcapture:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_capture 
BEFORE UPDATE ON pecheker.p_capture 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/


--p_biometrie

CREATE TABLE p_biometrie -- all the biological measurements
(
pkbiometrie INTEGER PRIMARY KEY,
fktaxon INTEGER REFERENCES p_taxon(pktaxon),
libelle_taxon VARCHAR2 (50), --species name as used in data source file,
fkstation INTEGER REFERENCES p_station(pkstation),
nb NUMBER, -- number of individuals measured
LS NUMBER, -- standard lenght in cm
LT NUMBER, -- total length in cm
LA NUMBER, -- anal length in cm
LF NUMBER, -- fork length in cm
LC NUMBER, -- carapace length in cm
CW NUMBER, -- carapace width in cm
envergure NUMBER, -- wingspan in cm
sexe VARCHAR2 (30), -- sex of the measured individual (for example male, female, unknown or undetermined) 
stade VARCHAR2 (30), -- maturity stage of the measured individual (for example adult, juvenil or a stage code)
poids_net NUMBER, -- processed weight in kg
poids_brut NUMBER, -- green weight in kg
type_echantillonnage VARCHAR2(50 BYTE), -- sampling strategy (for example random or not)
parasite_lithode VARCHAR2(30 BYTE), -- lithod parasite
observation VARCHAR2 (255), -- observation
contenu_sto_tx NUMBER, -- stomach contents,  filling rate
contenu_sto_nat VARCHAR2(255 BYTE), -- stomach contents, composition
ce_cp_xls_fichier VARCHAR2 (50), -- name of the data source file
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;

CREATE SEQUENCE p_biometrieSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_biometrie 
BEFORE INSERT ON pecheker.p_biometrie 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_biometrie.pkbiometrie%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkbiometrie is NULL THEN
select p_biometrieSEQ.nextval into numero from dual;
:new.pkbiometrie:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_biometrie 
BEFORE UPDATE ON pecheker.p_biometrie 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/

--p_echant

CREATE TABLE p_echant -- all the samples made during a cruise
(
pkechant INTEGER PRIMARY KEY,
fktaxon INTEGER REFERENCES p_taxon(pktaxon),
libelle_taxon VARCHAR2 (50), --species name as used in data source file,
fkcampagne INTEGER REFERENCES p_campagne(pkcampagne),
fkstation INTEGER REFERENCES p_station(pkstation),
nature VARCHAR2 (50), -- type of sample (for example alcohol, otolith, frozen…)
reference VARCHAR2 (255), -- unique number of the sample
LS NUMBER, -- standard lenght in cm 
LT NUMBER, -- total length in cm
LA NUMBER, -- anal length in cm
LF NUMBER, -- fork length in cm
LC NUMBER, -- carapace length in cm
CW NUMBER, -- carapace width in cm
envergure NUMBER, -- wingspan in cm
sexe VARCHAR2 (30), -- sex of the sampled individual (for example male, female, unknown or undetermined)
stade VARCHAR2 (30), -- maturity stage of the sample individual (for example adult, juvenil or a stage code)
poids_net NUMBER, -- processed weight in kg
poids_brut NUMBER, -- green weight in kg
origine VARCHAR2 (255), -- origin of the sample (for example found at the factory, in a stomach content or caught on a hook)
retour_protocole VARCHAR2 (255), -- protocol reference
type_echantillonnage VARCHAR2(50 BYTE), -- sampling strategy (for example random or not)
observation VARCHAR2 (255), -- observation
destinataire VARCHAR2 (255), -- sample recipient
envoi_destinataire DATE, -- date of sending to the recipient
identificateur VARCHAR2 (255), -- person who made the species identification
fkuser NUMBER,
reception DATE, -- receiving date
ce_cp_xls_fichier VARCHAR2 (50), -- name of the data source file
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_echantSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_echant 
BEFORE INSERT ON pecheker.p_echant 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_echant.pkechant%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkechant is NULL THEN
select p_echantSEQ.nextval into numero from dual;
:new.pkechant:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_echant 
BEFORE UPDATE ON pecheker.p_echant 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/


--p_marquage

CREATE TABLE p_marquage -- all the tagging made during the cruise
(
pkmarquage INTEGER PRIMARY KEY,
fktaxon INTEGER REFERENCES p_taxon(pktaxon),
fkstation INTEGER REFERENCES p_station(pkstation),
reference1 VARCHAR2 (255), -- number on the first tag
reference2 VARCHAR2 (255), -- number on the second tag
texte1 VARCHAR2 (255), -- wording on the first tag
texte2 VARCHAR2 (255), -- wording on the second tag
couleur1 VARCHAR2 (50), -- color of the first tag
couleur2 VARCHAR2 (50), -- color of the second tag
qualite1 VARCHAR2 (255), -- description if the first tag is the right or the left tag
qualite2 VARCHAR2 (255), -- description if the second tag is the right or the left tag
LS NUMBER, -- standard lenght in cm
LT NUMBER, -- total length in cm
LA NUMBER, -- anal length in cm
LF NUMBER, -- fork length in cm
envergure NUMBER, -- wingspan in cm
poids_brut NUMBER, -- green weight in kg
etat_individu VARCHAR2 (255), -- individual state
distance_parcourue NUMBER, -- distance steamed if release was delayed (for examples due to seals)
bac_destressage VARCHAR2 (50), -- was the fish put in a large tank to recover from stress?
observation VARCHAR2 (255), -- observation
ce_cp_xls_fichier VARCHAR2 (50), -- name of the data source file
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
LC NUMBER, -- carapace length in cm,
sexe VARCHAR2 (30), -- sex of the tagged individual (for rock lobster only)
etat_marquage VARCHAR2 (255), -- tag state
relache_lat_deg NUMBER, --  latitude if release was delayed (in degres)
relache_lat_min NUMBER, --  latitude if release was delayed (in minutes)
relache_lon_deg NUMBER, --  longitude if release was delayed (in degres)
relache_lon_min NUMBER, --  longitude if release was delayed (in minutes)

) ;


CREATE SEQUENCE p_marquageSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_marquage 
BEFORE INSERT ON pecheker.p_marquage 
REFERENCING OLD AS OLD NEW AS NEW	
FOR EACH ROW DECLARE
   numero p_marquage.pkmarquage%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkmarquage is NULL THEN
select p_marquageSEQ.nextval into numero from dual;
:new.pkmarquage:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_marquage 
BEFORE UPDATE ON pecheker.p_marquage 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/

--p_recapture

CREATE TABLE p_recapture -- all the recaptures of tagged individual
(
pkrecapture INTEGER PRIMARY KEY,
fktaxon INTEGER REFERENCES p_taxon(pktaxon),
fkstation INTEGER REFERENCES p_station(pkstation),
reference1 VARCHAR2 (255), -- number on the first tag, 
reference2 VARCHAR2 (255), -- number on the second tag
texte1 VARCHAR2 (255), -- wording on the first tag
texte2 VARCHAR2 (255), -- wording on the second tag
couleur1 VARCHAR2 (50), -- color of the first tag
couleur2 VARCHAR2 (50), -- color of the second tag
qualite1 VARCHAR2 (255), -- description if the first tag is the right or the left tag
qualite2 VARCHAR2 (255), -- description if the second tag is the right or the left tag
LS NUMBER, -- standard lenght in cm
LT NUMBER, -- total length in cm
LA NUMBER, -- anal length in cm
LF NUMBER, -- fork length in cm
envergure NUMBER, -- wingspan in cm
sexe VARCHAR2 (30), -- sex of the recaptured individual (for example male, female, unknown or undetermined)
stade VARCHAR2 (30), -- maturity stage of the recaptured individual (for example adult, juvenil or a stage code)
poids_brut NUMBER, -- green weight in kg
poids_net NUMBER, -- processed weight in kg
qualite_poids_brut VARCHAR2 (50), -- was greenweight estimated through processed weight
oto_ref VARCHAR2 (50), --otolith unique number
decouvreur VARCHAR2 (255), -- person who found the recaptured individual
etat_individu VARCHAR2 (255), -- state of the fish
etat_cicatrisation VARCHAR2 (255), -- healing state at the point where tag was inserted
position_tag VARCHAR2 (255), -- information on the correct position of tags according to CCAMLR instructions
transpondeur VARCHAR2 (50), -- presence or absence of a pit tag
photo VARCHAR2 (50), -- picture associated
observation VARCHAR2 (255), --observation
AAD VARCHAR2 (50), -- AAD code for australian recaptured fish
ce_cp_xls_fichier VARCHAR2 (50), -- name of the data source file
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
LC NUMBER, -- carapace length in cm
LCspine NUMBER, -- carapace length in cm taking into account the spine
) ;


CREATE SEQUENCE p_recaptureSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_recapture 
BEFORE INSERT ON pecheker.p_recapture
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_recapture.pkrecapture%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkrecapture is NULL THEN
select p_recaptureSEQ.nextval into numero from dual;
:new.pkrecapture:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_recapture 
BEFORE UPDATE ON pecheker.p_recapture 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/

--p_coef

CREATE TABLE p_coef -- all conversion factor made during the cruise
(
pkcoef INTEGER PRIMARY KEY,
fktaxon INTEGER REFERENCES p_taxon(pktaxon),
fkstation INTEGER REFERENCES p_station(pkstation),
produit VARCHAR2 (50), -- type of product (for example fillet, headed gutted and tailed …)
nb NUMBER, -- number of individuals
LT_min NUMBER, -- total length of the smallest individuals
LT_max NUMBER,-- total length of the largest individuals
poids_brut NUMBER, -- green weight in kg
poids_net NUMBER, -- processed weight in kg
observation VARCHAR2 (255),-- observation
coef NUMBER, --conversion factor
ce_cp_xls_fichier VARCHAR2 (50), -- name of the data source file
zone VARCHAR2(30 BYTE), -- area
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_coefSEQ start with 1 ;


CREATE OR REPLACE TRIGGER CRp_coef 
BEFORE INSERT ON pecheker.p_coef 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_coef.pkcoef%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkcoef is NULL THEN
select p_coefSEQ.nextval into numero from dual;
:new.pkcoef:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_coef 
BEFORE UPDATE ON pecheker.p_coef 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/


--p_calendrier

CREATE TABLE p_calendrier -- description of the ship activity every day
(
pkcalendrier INTEGER PRIMARY KEY,
fkcampagne INTEGER REFERENCES p_campagne(pkcampagne),
date_deb DATE, -- start date
date_fin DATE, -- end date
situation VARCHAR2 (255), -- description of the ship activity 
secteur_engage VARCHAR2 (50), -- fishing area
observation VARCHAR2 (255), -- observation
ce_cp_xls_fichier VARCHAR2 (50), -- name of the data source file
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_calendrierSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_calendrier 
BEFORE INSERT ON pecheker.p_calendrier 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_calendrier.pkcalendrier%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkcalendrier is NULL THEN
select p_calendrierSEQ.nextval into numero from dual;
:new.pkcalendrier:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_calendrier 
BEFORE UPDATE ON pecheker.p_calendrier 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/

--p_role

CREATE TABLE p_role -- description of the different role involved during the cruise
(
pkrole INTEGER PRIMARY KEY,
ok INTEGER, -- internal old key
fonction VARCHAR2 (50), -- profession of the person (for example captain or fishery observer)
nom VARCHAR2 (50), -- last name of the person
prenom VARCHAR2 (50), -- first name of the person
sigle VARCHAR2 (10), -- unique code for the person
naissance_date DATE,	 -- birth date
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_roleSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_role 
BEFORE INSERT ON pecheker.p_role 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_role.pkrole%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkrole is NULL THEN
select p_roleSEQ.nextval into numero from dual;
:new.pkrole:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_role 
BEFORE UPDATE ON pecheker.p_role 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/

--p_taxon

CREATE TABLE p_taxon -- table for taxa classification
(
pktaxon INTEGER PRIMARY KEY,
fkmorph INTEGER REFERENCES p_morph(pkmorph),
classe VARCHAR2 (50), -- class
ordre VARCHAR2 (50), -- order
famille VARCHAR2 (50), -- family
genre VARCHAR2 (50), -- gender
espece VARCHAR2 (50), --species
nom_vernaculaire VARCHAR2 (50), -- vernacular name (or french name)
nom_anglais VARCHAR2 (50), -- english name
categorie VARCHAR2 (50), -- species targeted or not by the fishery
ce_fao VARCHAR2 (50), -- FAO code
ce_ccamlr VARCHAR2 (255), --ccamlr code 
ce_ecopath VARCHAR2 (50), -- ecopath code
ce_fb_syn VARCHAR2 (50), -- fishbase code
esp_cod VARCHAR2 (50), -- harmonie code
gesp_id NUMBER,
genre_espece VARCHAR2 (255), -- scientific name
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_taxonSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_taxon 
BEFORE INSERT ON pecheker.p_taxon 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_taxon.pktaxon%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pktaxon is NULL THEN
select p_taxonSEQ.nextval into numero from dual;
:new.pktaxon:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_taxon 
BEFORE UPDATE ON pecheker.p_taxon 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/

--p_morph

CREATE TABLE p_morph -- morphtype description	
(
pkmorph INTEGER PRIMARY KEY,
nom VARCHAR2 (50), -- morphotype name
auteur VARCHAR2 (50), -- morphotype autor
date_crea DATE, -- morphotype creation date
nom_planche VARCHAR2 (255), -- document containing the morphotype description
url VARCHAR2 (255), -- url containing the morphotype description
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_morphSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_morph 
BEFORE INSERT ON pecheker.p_morph
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_morph.pkmorph%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkmorph is NULL THEN
select p_morphSEQ.nextval into numero from dual;
:new.pkmorph:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_morph 
BEFORE UPDATE ON pecheker.p_morph 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/


--p_secteur

CREATE TABLE p_secteur -- description of all the areas 
(
pksecteur INTEGER PRIMARY KEY,
latitude_angle_no NUMBER, -- north west latitude of the statistical square
longitude_angle_no NUMBER, -- north west longitude of the statiscal square
plateau VARCHAR2 (50), -- name of the Plateau (for example kerguelen, crozet, saint paul Amsterdam)
sect_cod NUMBER, -- code of the statistical square
sous_secteur VARCHAR2 (50), -- code of an inner statistical square
zee VARCHAR2 (50), -- Excclusive Economic Zone (for example ZEE KER, ZEE CRO sud de 45, ZEE CRO nord de 45, hors ZEE)
asd VARCHAR2 (50), -- FAO area or subarea (for example 58.5.1, 58.6, 51, 58.4.3a)
orgp VARCHAR2 (50), -- RFMO (for example CCAMLR, SIOFA)
ile VARCHAR2 (50), -- island name (for example Saint Paul, Amsterdam)
toponymie VARCHAR2 (50), -- toponymy 
qualite_toponymie VARCHAR2 (50), -- description of the toponymy type
ce_harmonie VARCHAR2 (50), -- harmonie code
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_secteurSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_secteur 
BEFORE INSERT ON pecheker.p_secteur
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_secteur.pksecteur%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pksecteur is NULL THEN
select p_secteurSEQ.nextval into numero from dual;
:new.pksecteur:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_secteur 
BEFORE UPDATE ON pecheker.p_secteur 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/

--p_debarque

CREATE TABLE p_debarque -- landing in port
(
pkdebarque INTEGER PRIMARY KEY,
fkcampagne INTEGER REFERENCES p_campagne(pkcampagne),
fktaxon INTEGER REFERENCES p_taxon(pktaxon),
produit VARCHAR2 (50), -- fish product (for example fillet, headed gutted and tailed …)
etat VARCHAR2 (50), -- fish state (for example fresh or frozen)
poids_net NUMBER, -- processed weight in kg
zone_peche VARCHAR2 (50), -- fishing area
calibre VARCHAR2 (50), -- caliber for rock lobster
observation VARCHAR2 (255), -- observation
ce_cp_xls_fichier VARCHAR2 (50), -- name of the data source file
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_debarqueSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_debarque 
BEFORE INSERT ON pecheker.p_debarque 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_debarque.pkdebarque%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkdebarque is NULL THEN
select p_debarqueSEQ.nextval into numero from dual;
:new.pkdebarque:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_debarque 
BEFORE UPDATE ON pecheker.p_debarque 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/

--p_user

CREATE TABLE p_user -- data user description
(
pkuser INTEGER PRIMARY KEY,
nom VARCHAR2 (50), -- user last name
prenom VARCHAR2 (50), -- user first name
labo_orga VARCHAR2 (255), -- user laboratory or research organization
departement VARCHAR2 (255), -- user department
umr VARCHAR2 (255), -- user UMR
ville VARCHAR2 (50), -- user city
adresse VARCHAR2 (255), -- user adress
code_postal VARCHAR2 (50), -- user postal code
telephone VARCHAR2 (50), -- user phone number
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_userSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_user 
BEFORE INSERT ON pecheker.p_user 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_user.pkuser%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkuser is NULL THEN
select p_userSEQ.nextval into numero from dual;
:new.pkuser:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_user 
BEFORE UPDATE ON pecheker.p_user 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/


--p_navire


CREATE TABLE p_navire – ship description
(
pknavire INTEGER PRIMARY KEY,
navire VARCHAR2 (50), -- ship name
construction_nom VARCHAR2 (50), -- ship construction name
construction_nationalite VARCHAR2 (50), -- ship construction nationality
construction_annee DATE, -- ship construction date
nationalite VARCHAR2 (50), -- ship nationality code
longueur NUMBER, -- ship length
jauge_tx NUMBER, -- ship gauge
puissance_kw NUMBER, -- ship power in kw
classe VARCHAR2 (50), -- ship class
ce_kerpeche VARCHAR2 (50), -- kerpeche ship code
ce_harmonie VARCHAR2 (50), -- harmonie ship code
ce_camlr VARCHAR2 (50),-- ccamlr ship code
ce_acro_cp VARCHAR2 (50), -- ship code
grt_category_size NUMBER, -- ship Gross Register Tonnage size
grt NUMBER, -- ship Gross Register Tonnage
puissance_chev NUMBER, -- ship power
ccamlr_code NUMBER, -- ccamlr code
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_navireSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_navire 
BEFORE INSERT ON pecheker.p_navire 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_navire.pknavire%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pknavire is NULL THEN
select p_navireSEQ.nextval into numero from dual;
:new.pknavire:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_navire 
BEFORE UPDATE ON pecheker.p_navire 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/

--p_armateur

CREATE TABLE p_armateur – ship owner
(
pkarmateur INTEGER PRIMARY KEY,
statut VARCHAR2 (50), -- ship owner status
localisation VARCHAR2 (255), -- ship owner adress
nationalite VARCHAR2 (50), -- ship owner nationality
nom VARCHAR2 (50),-- ship owner name
naissance_annee NUMBER, -- ship owner starting year
naissance_date DATE, -- ship owner starting date
c_armateur VARCHAR2 (50), -- ship owner code
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_armateurSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_armateur 
BEFORE INSERT ON pecheker.p_armateur 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_armateur.pkarmateur%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkarmateur is NULL THEN
select p_armateurSEQ.nextval into numero from dual;
:new.pkarmateur:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_armateur 
BEFORE UPDATE ON pecheker.p_armateur 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/


--p_armement

CREATE TABLE p_armement -- shipping line
(
pkarmement INTEGER PRIMARY KEY,
fkarmateur INTEGER REFERENCES p_armateur(pkarmateur),
fknavire INTEGER REFERENCES p_navire(pknavire),
nom VARCHAR2 (50), -- shipping line name
metier VARCHAR2 (50), -- shipping line profession (fishing technique) (for example longliner, trawler…)
immatriculation VARCHAR2 (50), -- immatriculation
date_debut DATE, -- shipping line start date
date_fin DATE,-- shipping line end date
quartier VARCHAR2 (50), -- shipping line associated port
equipage_nb NUMBER, -- shipping line member number
equipage_nationalite VARCHAR2 (50), -- shipping line nationality
tlieu_cod NUMBER,
lieu_cod VARCHAR2 (50),
lieu_lib VARCHAR2 (50),
navs_cod NUMBER,
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_armementSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_armement 
BEFORE INSERT ON pecheker.p_armement 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_armement.pkarmement%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkarmement is NULL THEN
select p_armementSEQ.nextval into numero from dual;
:new.pkarmement:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_armement 
BEFORE UPDATE ON pecheker.p_armement 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/

--p_engin

CREATE TABLE p_engin -- gear
(
pkengin INTEGER PRIMARY KEY,
type VARCHAR2 (50), -- gear type (for example botton trawl, Spanish trotline, autoline…)
categorie VARCHAR2 (50), -- gear category (for example traw, longligne…)
description VARCHAR2 (255), -- gear description
ce_fao VARCHAR2 (50), -- FAO gear code
engin_cod VARCHAR2 (50), -- gear code
typeng_fam VARCHAR2 (50),
greng_id NUMBER,
ccamlr_longline_code VARCHAR2 (50),
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_enginSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_engin 
BEFORE INSERT ON pecheker.p_engin 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_engin.pkengin%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkengin is NULL THEN
select p_enginSEQ.nextval into numero from dual;
:new.pkengin:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_engin 
BEFORE UPDATE ON pecheker.p_engin 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/


--p_embarcation


CREATE TABLE p_embarcation -- small boat description for Saint Paul Amsterdam fishery
(
pkembarcation INTEGER PRIMARY KEY,
fknavire INTEGER REFERENCES p_navire(pknavire),
type VARCHAR2 (20), -- small boat type
nom VARCHAR2 (30), -- small boat name
description VARCHAR2 (255), -- small boat description
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_embarcationSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_embarcation
BEFORE INSERT ON pecheker.p_embarcation
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_embarcation.pkembarcation%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkembarcation is NULL THEN
select p_embarcationSEQ.nextval into numero from dual;
:new.pkembarcation:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_embarcation
BEFORE UPDATE ON pecheker.p_embarcation
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/



--p_boite


CREATE TABLE p_boite -- archive box
(
pkboite INTEGER PRIMARY KEY,
cote VARCHAR2 (50), -- classification number of the archive box
intitule_niveau1 VARCHAR2 (255), -- first level of the box description
intitule_niveau2 VARCHAR2 (255), -- second level of the box description
metadata LONG, -- metada description
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_boiteSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_boite
BEFORE INSERT ON pecheker.p_boite
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_boite.pkboite%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkboite is NULL THEN
select p_boiteSEQ.nextval into numero from dual;
:new.pkboite:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_boite
BEFORE UPDATE ON pecheker.p_boite
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/


--p_boitecorres


CREATE TABLE p_boitecorres -- link between archives boxes and cruises
(
pkboitecorres INTEGER PRIMARY KEY,
fkboite INTEGER REFERENCES p_boite(pkboite),
fkcampagne INTEGER REFERENCES p_campagne(pkcampagne),
remarque VARCHAR2 (255), -- comment
datemaj DATE, --last update date of the row
datecre DATE, --creation date of the row
usercre NUMBER (15), --user ID of the row creation
usermaj NUMBER (15) --user ID of the row update
) ;


CREATE SEQUENCE p_boitecorresSEQ start with 1 ;

CREATE OR REPLACE TRIGGER CRp_boitecorres
BEFORE INSERT ON pecheker.p_boitecorres
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW DECLARE
   numero p_boitecorres.pkboitecorres%TYPE;
BEGIN
:new.DATEMAJ:=NULL;
:new.USERMAJ:=NULL;
:new.DATECRE:=SYSDATE;
:new.USERCRE:=UID;
IF :new.pkboitecorres is NULL THEN
select p_boitecorresSEQ.nextval into numero from dual;
:new.pkboitecorres:=numero;
END IF;
END;
/
CREATE OR REPLACE TRIGGER MJp_boitecorres
BEFORE UPDATE ON pecheker.p_boitecorres
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW 
BEGIN
:new.DATEMAJ:=SYSDATE;
:new.USERMAJ:=UID;
:new.DATECRE:=:old.DATECRE;
:new.USERCRE:=:old.USERCRE;
END;
/