Write the following as triggers. In each case, disallow or undo the modification

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) When a new class is inserted into Classes, also insert a ship with the name of that class and a NULL launch date. When a new class is inserted with a displacement greater than 35,000 tons, allow the insertion, but change the displacement to 35,000. 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. 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.

Explanation / Answer

a)

CREATE TRIGGER TRG_InsertClass
ON dbo.Classes
AFTER INSERT AS
BEGIN
   INSERT INTO Ships(name,launched)
   SELECT class,launched
   FROM
   (
   SELECT class AS class FROM INSERTED
   JOIN
   SELECT null AS launched
   )
END

c)

CREATE TRIGGER TRG_InsertOutcome
ON dbo.Outcomes
AFTER INSERT AS
BEGIN
   DECLARE @ship VARCHAR(100)
   DECLARE @battle VARCHAR(100)
   SELECT @ship = ship, @battle = battle FROM INSERTED
   IF NOT EXISTS(SELECT 1 FROM dbo.Ships WHERE name = @ship)
   BEGIN
       INSERT INTO Ships(name,class,launched)
       SELECT @ship, null, null
   END
   IF NOT EXISTS(SELECT 1 FROM dbo.Battles WHERE name = @battle)
   BEGIN
       INSERT INTO Battles(name,date)
       SELECT @ship, null
   END
END

d)

CREATE TRIGGER TRG_InsertAndUpdateShip
ON dbo.Ships
AFTER INSERT,UPDATE AS
BEGIN
   DECLARE @class VARCHAR(100)
   DECLARE @country VARCHAR(100)
   DECLARE @count int  
   SELECT @class = class FROM INSERTED
   SELECT @country = country FROM dbo.Classes where class = @class
   SELECt @count = COUNT(*) from Classes WHERE country = @country
   IF @count > 20
   BEGIN
       RAISERROR('There are more than 20 ships for this country',16 ,1)
       ROLLBACK
   END
END

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.