i have found exercise in sql book study, not solved , can not solve it.
the goal implement trigger avoids overlapping contracts. if customer current contract signs new one, end date of previous 1 one day before new start date.
tables given are:
create table clients ( clientid varchar2(15), dni varchar2(9), name varchar2(100) not null, surname varchar2(100) not null, sec_surname varchar2(100), email varchar2(100) not null, phonen number(12), birthdate date, constraint pk_clients primary key (clientid), constraint uk1_clients unique (dni), constraint uk2_clients unique (email), constraint uk3_clients unique (phonen), ); create table contracts( contractid varchar2(10), clientid varchar2(15), startdate date not null, enddate date, contract_type varchar2(50), address varchar2(100) not null, town varchar2(100) not null, zipcode varchar2(8) not null, country varchar2(100) not null, constraint pk_contracts primary key (contractid), constraint fk_contracts1 foreign key (clientid) references clients ); any suggestions?
i agree posted comments helps have specifics failing in prior attempts, , recommend not using trigger @ kind of thing.
study exercise, here examples might starting place.
i've modified tables disallow null primary keys in these examples.
to started, create tables:
create table clients ( clientid varchar2(15) not null, dni varchar2(9), name varchar2(100) not null, surname varchar2(100) not null, sec_surname varchar2(100), email varchar2(100) not null, phonen number(12), birthdate date, constraint pk_clients primary key (clientid), constraint uk1_clients unique (dni), constraint uk2_clients unique (email), constraint uk3_clients unique (phonen) ); create table contracts ( contractid varchar2(10) not null, clientid varchar2(15) not null, startdate date not null, enddate date, contract_type varchar2(50), address varchar2(100) not null, town varchar2(100) not null, zipcode varchar2(8) not null, country varchar2(100) not null, constraint pk_contracts primary key (contractid), constraint fk_contracts1 foreign key (clientid) references clients ); then, create first clients:
insert clients values (1,null,'frodo','baggins',null,'the.real.frodo@adventure.com',null,null); insert clients values (2,null,'chewbacca','unknown',null,'chewio.@kashyyyk.org',null,null); commit; then create trigger. in first example, trigger after statement type.
simple inefficient since evaluates every client after each insert statement.
against large data set, or in face of multiple triggers, problem.
trigger check prior contract , set enddate 1 day before new contract, if null or after start of new contract.
create or replace trigger contract_enddate_adjuster after insert on contracts begin merge contracts using ( select contractid, candidate_enddate enddate (select contracts.contractid, (trunc(lead(startdate) on (partition clientid order startdate asc) - 1)) candidate_enddate, dense_rank() on (partition clientid order startdate desc) contract_order contracts) contract_order = 2) candidate_contract on (contracts.contractid = candidate_contract.contractid) when matched update set contracts.enddate = candidate_contract.enddate contracts.enddate null or contracts.enddate > candidate_contract.enddate; end; / then, test out.
add initial contracts. no enddate changes expected, these first. frodo's contract here has end-date set.
insert contracts values('break-ring',1,to_date('19560511','yyyymmdd'), to_date('19851014','yyyymmdd'), null, 'no 1', 'doom mountain', 'mord', 'middle-earth'); insert contracts values('savegalaxy',2,to_date('19770615','yyyymmdd'), null, null, 'no 75', 'rwookrrorro', 'rwkr', 'kashyyyk'); select contractid, clientid, startdate, enddate contracts order clientid asc, startdate asc; contractid clientid startdate enddate break-ring 1 11-may-56 14-oct-85 savegalaxy 2 15-jun-77 then add new contracts.
frodo's new contract starts before end of existing contract, enddate adjusted.
chewie's initial contract had no enddate, adjusted well.
insert contracts values('gobackhome',1,to_date('19570219','yyyymmdd'), null, null, 'no 13', 'hobbiton', 'hbtn', 'middle-earth'); insert contracts values('defendhoth',2,to_date('19801115','yyyymmdd'), null, null, 'meteor crater', 'ice ridge', 'meteo', 'hoth'); select contractid, clientid, startdate, enddate contracts order clientid asc, startdate asc; contractid clientid startdate enddate break-ring 1 11-may-56 18-feb-57 gobackhome 1 19-feb-57 savegalaxy 2 15-jun-77 14-nov-80 defendhoth 2 15-nov-80 and other contracts signed, pattern continues:
insert contracts values('gowedding',2,to_date('19830309','yyyymmdd'), null, null, 'main hall', 'grand palace', 'allnc', 'coruscant'); insert contracts values('gardening',1,to_date('19570503','yyyymmdd'), null, null, 'no 13', 'hobbiton', 'hbtn', 'middle-earth'); select contractid, clientid, startdate, enddate contracts order clientid asc, startdate asc; contractid clientid startdate enddate break-ring 1 11-may-56 18-feb-57 gobackhome 1 19-feb-57 02-may-57 gardening 1 03-may-57 savegalaxy 2 15-jun-77 14-nov-80 defendhoth 2 15-nov-80 08-mar-83 gowedding 2 09-mar-83 to stabilize workload on query, compound trigger may used instead. second example achieves same result first, interrogates contracts of clients have changed:
first, rollback; then, create type used trigger:
create or replace type number_list table of number; / then create compound trigger:
create or replace trigger contract_enddate_adjuster insert on contracts compound trigger v_clients number_list; before statement begin v_clients:= number_list(); end before statement; after each row begin v_clients.extend(); v_clients(v_clients.count) := :new.clientid; end after each row; after statement begin merge contracts using ( select contractid, candidate_enddate enddate (select contracts.contractid, (trunc(lead(startdate) on (partition clientid order startdate asc) - 1)) candidate_enddate, dense_rank() on (partition clientid order startdate desc) contract_order contracts contracts.clientid in (select * table(v_clients))) contract_order = 2) candidate_contract on (contracts.contractid = candidate_contract.contractid) when matched update set contracts.enddate = candidate_contract.enddate contracts.enddate null or contracts.enddate > candidate_contract.enddate; end after statement; end contract_enddate_adjuster; / after repeating above inserts, result same:
contractid clientid startdate enddate break-ring 1 11-may-56 18-feb-57 gobackhome 1 19-feb-57 02-may-57 gardening 1 03-may-57 savegalaxy 2 15-jun-77 14-nov-80 defendhoth 2 15-nov-80 08-mar-83 gowedding 2 09-mar-83 
Comments
Post a Comment