Write the following as triggers. In each case, disallow or undo the modification
ID: 3823107 • Letter: W
Question
Write the following as triggers. In each case, disallow or undo the modification if it does not satisfy the stated constraint. The database schema is from the battleships example of Exercise 2.4.3. Classes(class, type, country, numGuns, bore, displacement) Ships(name, class, launched) Battles(name, date) Outcomes(ship, battle, result) a) When a new class is inserted into Classes, also insert a ship with the name of that class and a NULL launch date. b) When a new class is inserted with a displacement greater than 35,000 tons, allow the insertion, but change the displacement to 35,000. ! c) If a tuple is inserted into Outcomes, check that the ship and battle are listed in Ships and Battles, respectively, and if not, insert tuples into one or both of these relations, with NULL components where necessary. ! d) When there is an insertion into Ships or an update of the class attribute of Ships, check that no country has more than 20 ships. !! e) Check, under all circumstances that could cause a violation, that no ship fought in a battle that was at a later date than another battle in which that ship was sunk.
Explanation / Answer
create or replace trigger price_update_trigger
before update of price on PC //checks before updation
for each row
declare
minprice number; //declare variables
sp number;
begin
if ( updating )
select price into minprice from PC where speed=:new.speed; //put the min value of price with same speed
dbms_output.put_line( 'Cannot update PC with Higher Price and with the same speed”);
end if;
end;
/
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.