| Src |
CREATE OR REPLACE FUNCTION sample_trg_aiud()
RETURNS trigger AS
$$
declare
cnt int4;
begin
if tg_op = 'INSERT' then
-- HANDLE ARCHIVE
-- if current_archive is entered
if new.current_archive is not null and new.current_archive <> '' then
-- check if it exists in the archive_lkup table
select count(*)
into cnt
from archive_lkup
where name = new.current_archive;
-- if it does not exist in the archive_lkup table, put it there
if cnt = 0 then
insert into archive_lkup values (new.current_archive);
end if;
end if;
-- HANDLE COLLECTION METHOD
-- if collection_method is entered
if new.collection_method is not null and new.collection_method <> '' then
-- check if it exists in the collection_method_lkup table
select count(*)
into cnt
from collection_method_lkup
where name = new.collection_method;
-- if it does not exist in the collection_method_lkup table, put it there
if cnt = 0 then
insert into collection_method_lkup values (new.collection_method);
end if;
end if;
-- HANDLE PLATFORM TYPE
-- if platform_type is entered
if new.platform_type is not null and new.platform_type <> '' then
-- check if it exists in the platform_type_lkup table
select count(*)
into cnt
from platform_type_lkup
where name = new.platform_type;
-- if it does not exist in the platform_type_lkup table, put it there
if cnt = 0 then
insert into platform_type_lkup values (new.platform_type);
end if;
end if;
-- HANDLE PRIMARY LOCATION TYPE
-- if primary_location_type is entered
if new.primary_location_type is not null and new.primary_location_type <> '' then
-- check if it exists in the primary_location_type_lkup table
select count(*)
into cnt
from primary_location_type_lkup
where name = new.primary_location_type;
-- if it does not exist in the primary_location_type_lkup table, put it there
if cnt = 0 then
insert into primary_location_type_lkup values (new.primary_location_type);
end if;
end if;
-- HANDLE PRIMARY LOCATION NAME
-- if primary_location_name is entered
if new.primary_location_name is not null and new.primary_location_name <> '' then
-- check if it exists in the primary_location_name_lkup table
select count(*)
into cnt
from primary_location_name_lkup
where name = new.primary_location_name;
-- if it does not exist in the primary_location_name_lkup table, put it there
if cnt = 0 then
insert into primary_location_name_lkup values (new.primary_location_name);
end if;
end if;
-- HANDLE REGISTRANT
-- if cur_registrant_id is entered
if new.cur_registrant_id is not null then
-- check if it exists in the registrar_lkup table
select count(*)
into cnt
from registrar_lkup
where registrar_id = new.cur_registrant_id;
-- if it does not exist in the registrar_lkup table, put it there
if cnt = 0 then
insert into registrar_lkup values (new.cur_registrant_id);
end if;
end if;
-- if org_registrant_id is entered
if new.org_registrant_id is not null then
-- check if it exists in the registrar_lkup table
select count(*)
into cnt
from registrar_lkup
where registrar_id = new.org_registrant_id;
-- if it does not exist in the registrar_lkup table, put it there
if cnt = 0 then
insert into registrar_lkup values (new.org_registrant_id);
end if;
end if;
-- HANDLE COLLECTOR
-- if collector is entered
if new.collector is not null and new.collector <> '' then
-- check if it exists in the registrar_lkup table
select count(*)
into cnt
from collector_lkup
where name = new.collector;
-- if it does not exist in the registrar_lkup table, put it there
if cnt = 0 then
insert into collector_lkup values (new.collector);
end if;
end if;
-- HANDLE CRUISE_FIELD_PRGRM
-- if cruise_field_prgrm is entered
if new.cruise_field_prgrm is not null and new.cruise_field_prgrm <> '' then
-- check if it exists in the registrar_lkup table
select count(*)
into cnt
from cruise_field_prgrm_lkup
where name = new.cruise_field_prgrm;
-- if it does not exist in the registrar_lkup table, put it there
if cnt = 0 then
insert into cruise_field_prgrm_lkup values (new.cruise_field_prgrm);
end if;
end if;
-- HANDLE PLATFORM_NAME
-- if platform_name is entered
if new.platform_name is not null and new.platform_name <> '' then
-- check if it exists in the registrar_lkup table
select count(*)
into cnt
from platform_name_lkup
where name = new.platform_name;
-- if it does not exist in the registrar_lkup table, put it there
if cnt = 0 then
insert into platform_name_lkup values (new.platform_name);
end if;
end if;
end if;
if tg_op = 'UPDATE' then
-- HANDLE ARCHIVE
-- if archive was updated
if new.current_archive != old.current_archive then
-- does new archive exist in archive_lkup table
select count(*)
into cnt
from archive_lkup
where name = new.current_archive;
-- if not put it there
if cnt = 0 then
insert into archive_lkup values (new.current_archive);
end if;
-- does old archive still exist in sample table
select count(*)
into cnt
from sample
where current_archive = old.current_archive;
-- if not, remove it from archive_lkup table
if cnt = 0 then
delete from archive_lkup where name = old.current_archive;
end if;
end if;
-- HANDLE COLLECTION METHOD
-- if collection_method is entered
if new.collection_method != new.collection_method then
-- check if it exists in the collection_method_lkup table
select count(*)
into cnt
from collection_method_lkup
where name = new.collection_method;
-- if it does not exist in the collection_method_lkup table, put it there
if cnt = 0 then
insert into collection_method_lkup values (new.collection_method);
end if;
-- does old collection_method still exist in sample table
select count(*)
into cnt
from sample
where collection_method = old.collection_method;
-- if not, remove it from collection_method_lkup table
if cnt = 0 then
delete from collection_method_lkup where name = old.collection_method;
end if;
end if;
-- HANDLE PLATFORM TYPE
-- if platform_type is entered
if new.platform_type is not null and new.platform_type <> '' then
-- check if it exists in the platform_type_lkup table
select count(*)
into cnt
from platform_type_lkup
where name = new.platform_type;
-- if it does not exist in the platform_type_lkup table, put it there
if cnt = 0 then
insert into platform_type_lkup values (new.platform_type);
end if;
-- does old platform_type still exist in sample table
select count(*)
into cnt
from sample
where platform_type = old.platform_type;
-- if not, remove it from platform_type_lkup table
if cnt = 0 then
delete from platform_type_lkup where name = old.platform_type;
end if;
end if;
-- HANDLE PRIMARY LOCATION TYPE
-- if primary_location_type is entered
if new.primary_location_type is not null and new.primary_location_type <> '' then
-- check if it exists in the primary_location_type_lkup table
select count(*)
into cnt
from primary_location_type_lkup
where name = new.primary_location_type;
-- if it does not exist in the primary_location_type_lkup table, put it there
if cnt = 0 then
insert into primary_location_type_lkup values (new.primary_location_type);
end if;
-- does old primary_location_type still exist in sample table
select count(*)
into cnt
from sample
where primary_location_type = old.primary_location_type;
-- if not, remove it from primary_location_type_lkup table
if cnt = 0 then
delete from primary_location_type_lkup where name = old.primary_location_type;
end if;
end if;
-- HANDLE PRIMARY LOCATION NAME
-- if primary_location_name is entered
if new.primary_location_name is not null and new.primary_location_name <> '' then
-- check if it exists in the primary_location_name_lkup table
select count(*)
into cnt
from primary_location_name_lkup
where name = new.primary_location_name;
-- if it does not exist in the primary_location_name_lkup table, put it there
if cnt = 0 then
insert into primary_location_name_lkup values (new.primary_location_name);
end if;
-- does old primary_location_name still exist in sample table
select count(*)
into cnt
from sample
where primary_location_name = old.primary_location_name;
-- if not, remove it from primary_location_name_lkup table
if cnt = 0 then
delete from primary_location_name_lkup where name = old.primary_location_name;
end if;
end if;
-- HANDLE REGISTRAR LOOKUP
-- if cur_registrant_id is entered
if new.cur_registrant_id is not null then
-- check if it exists in the cur_registrant_id_lkup table
select count(*)
into cnt
from registrar_lkup
where registrar_id = new.cur_registrant_id;
-- if it does not exist in the cur_registrant_id_lkup table, put it there
if cnt = 0 then
insert into registrar_lkup values (new.cur_registrant_id);
end if;
-- does old cur_registrant_id still exist in sample table
-- as either cur or org registrant
select count(*)
into cnt
from sample
where cur_registrant_id = old.cur_registrant_id
or cur_registrant_id = old.org_registrant_id;
-- if not, remove it from cur_registrar_lkup table
if cnt = 0 then
delete from registrar_lkup where registrar_id = old.cur_registrant_id;
end if;
end if;
-- if org_registrant_id is entered
if new.org_registrant_id is not null then
-- check if it exists in the registrar_lkup table
select count(*)
into cnt
from registrar_lkup
where registrar_id = new.org_registrant_id;
-- if it does not exist in the registrar_lkup table, put it there
if cnt = 0 then
insert into registrar_lkup values (new.org_registrant_id);
end if;
-- does old org_registrant_id still exist in sample table
select count(*)
into cnt
from sample
where org_registrant_id = old.org_registrant_id
or org_registrant_id = old.cur_registrant_id;
-- if not, remove it from registrar_lkup table
if cnt = 0 then
delete from registrar_lkup where registrar_id = old.org_registrant_id;
end if;
end if;
-- HANDLE COLLECTOR
if new.collector is not null and new.collector <> '' then
-- check if it exists in the collector_lkup table
select count(*)
into cnt
from collector_lkup
where name = new.collector;
-- if it does not exist in the collector_lkup table, put it there
if cnt = 0 then
insert into collector_lkup values (new.collector);
end if;
-- does old collector still exist in sample table
select count(*)
into cnt
from sample
where collector = old.collector;
-- if not, remove it from collector_lkup table
if cnt = 0 then
delete from collector_lkup where name = old.collector;
end if;
end if;
-- HANDLE CRUISE_FIELD_PRGRM
if new.cruise_field_prgrm is not null and new.cruise_field_prgrm <> '' then
-- check if it exists in the cruise_field_prgrm_lkup table
select count(*)
into cnt
from cruise_field_prgrm_lkup
where name = new.cruise_field_prgrm;
-- if it does not exist in the cruise_field_prgrm_lkup table, put it there
if cnt = 0 then
insert into cruise_field_prgrm_lkup values (new.cruise_field_prgrm);
end if;
-- does old cruise_field_prgrm still exist in sample table
select count(*)
into cnt
from sample
where cruise_field_prgrm = old.cruise_field_prgrm;
-- if not, remove it from cruise_field_prgrm_lkup table
if cnt = 0 then
delete from cruise_field_prgrm_lkup where name = old.cruise_field_prgrm;
end if;
end if;
-- HANDLE PLATFORM_NAME
if new.platform_name is not null and new.platform_name <> '' then
-- check if it exists in the platform_name_lkup table
select count(*)
into cnt
from platform_name_lkup
where name = new.platform_name;
-- if it does not exist in the platform_name_lkup table, put it there
if cnt = 0 then
insert into platform_name_lkup values (new.platform_name);
end if;
-- does old platform_name still exist in sample table
select count(*)
into cnt
from sample
where platform_name = old.platform_name;
-- if not, remove it from platform_name_lkup table
if cnt = 0 then
delete from platform_name_lkup where name = old.platform_name;
end if;
end if;
end if;
if tg_op = 'DELETE' then
-- HANDLE ARCHIVE
-- if there was an archive in delete row
if old.current_archive is not null and old.current_archive <> '' then
-- does does archive still exist in sample table
select count(*)
into cnt
from sample
where current_archive = old.current_archive;
-- if not, remove it from archive_lkup table
if cnt = 0 then
delete from archive_lkup where name = old.current_archive;
end if;
end if;
-- HANDLE COLLECTION METHOD
-- if there was a collection_method in the deleted row
if old.collection_method is not null and old.collection_method <> '' then
-- does old collection_method still exist in sample table
select count(*)
into cnt
from sample
where collection_method = old.collection_method;
-- if not, remove it from collection_method_lkup table
if cnt = 0 then
delete from collection_method_lkup where name = old.collection_method;
end if;
end if;
-- HANDLE PLATFORM TYPE
-- if there was a platform_type in the deleted row
if old.platform_type is not null and old.platform_type <> '' then
-- does old platform_type still exist in sample table
select count(*)
into cnt
from sample
where platform_type = old.platform_type;
-- if not, remove it from platform_type_lkup table
if cnt = 0 then
delete from platform_type_lkup where name = old.platform_type;
end if;
end if;
-- HANDLE PRIMARY LOCATION TYPE
-- if there was a primary_location_type in the deleted row
if old.primary_location_type is not null and old.primary_location_type <> '' then
-- does old primary_location_type still exist in sample table
select count(*)
into cnt
from sample
where primary_location_type = old.primary_location_type;
-- if not, remove it from primary_location_type_lkup table
if cnt = 0 then
delete from primary_location_type_lkup where name = old.primary_location_type;
end if;
end if;
-- HANDLE PRIMARY LOCATION NAME
-- if there was a primary_location_name in the deleted row
if old.primary_location_name is not null and old.primary_location_name <> '' then
-- does old primary_location_name still exist in sample table
select count(*)
into cnt
from sample
where primary_location_name = old.primary_location_name;
-- if not, remove it from primary_location_name_lkup table
if cnt = 0 then
delete from primary_location_name_lkup where name = old.primary_location_name;
end if;
end if;
-- HANDLE REGISTRAR
-- if there was a cur_registrant_id in the deleted row
if old.cur_registrant_id is not null then
-- does old cur_registrant_id still exist in sample table
select count(*)
into cnt
from sample
where cur_registrant_id = old.cur_registrant_id
or cur_registrant_id = old.org_registrant_id;
-- if not, remove it from primary_location_name_lkup table
if cnt = 0 then
delete from registrar_lkup where registrar_id = old.cur_registrant_id;
end if;
end if;
-- if there was a org_registrant_id in the deleted row
if old.org_registrant_id is not null then
-- does old org_registrant_id still exist in sample table
select count(*)
into cnt
from sample
where org_registrant_id = old.org_registrant_id
or org_registrant_id = old.cur_registrant_id;
-- if not, remove it from primary_location_name_lkup table
if cnt = 0 then
delete from registrar_lkup where registrar_id = old.org_registrant_id;
end if;
end if;
-- HANDLE COLLECTOR
-- if there was a collector in the deleted row
if old.collector is not null and old.collector <> '' then
-- does old collector still exist in sample table
select count(*)
into cnt
from sample
where collector = old.collector;
-- if not, remove it from collector_lkup table
if cnt = 0 then
delete from collector_lkup where name = old.collector;
end if;
end if;
-- HANDLE CRUISE_FIELD_PRGRM
-- if there was a cruise_field_prgrm in the deleted row
if old.cruise_field_prgrm is not null and old.cruise_field_prgrm <> '' then
-- does old cruise_field_prgrm still exist in sample table
select count(*)
into cnt
from sample
where cruise_field_prgrm = old.cruise_field_prgrm;
-- if not, remove it from cruise_field_prgrm_lkup table
if cnt = 0 then
delete from cruise_field_prgrm_lkup where name = old.cruise_field_prgrm;
end if;
end if;
-- HANDLE PLATFORM_NAME
-- if there was a platform_name in the deleted row
if old.platform_name is not null and old.platform_name <> '' then
-- does old platform_name still exist in sample table
select count(*)
into cnt
from sample
where platform_name = old.platform_name;
-- if not, remove it from platform_name_lkup table
if cnt = 0 then
delete from platform_name_lkup where name = old.platform_name;
end if;
end if;
end if;
return new;
end
$$
LANGUAGE plpgsql VOLATILE
COST 100;
|
