(seems long, but it\'s not, a lot of useless info was added in the instructions)

Question

(seems long, but it's not, a lot of useless info was added in the instructions)

Assuming the manager of ClearWater wants to keep the total number of suppliers between 25 and 29. Currently in the original ClearWater database, the Suppliers table has 29 suppliers. This means up to four suppliers can be deleted fro mSuppliers_copy without failing the rule; otherwise, the DELETE should be rejected. On the other hand, unless some suppliers are deleted first, inserting new suppliers into Suppliers_copy should be rejected because that makes the total beyond 29.

Your job here is to create a trigger tgr_limitTotalSupplier to support the manager's new rule. When the rule is violated, print one of the error messages below, depending on the firing operation.

'Total suppliers cannot beyond 29. Your INSERT is aborted.

''Total suppliers cannot below 25. Your DELETE is aborted.

'If the rule is followed, then, print one of the two sets of messages below to acknowledge the operation.

'M suppliers are inserted successfully.

'N suppliers are found in Suppliers_copy.'or' M suppliers are deleted successfully.'

'N suppliers are found in Suppliers_copy where M is the total rows of suppliers inserted or deleted and N is thetotal number ofsuppliers after the operation.

*/if object_id('Suppliers_copy') is not null drop table Suppliers_copy; select * into Suppliers_copy from Suppliers; go

--> Analysis:

-- First, the only operation that increases the total suppliers isINSERT, and the only

-- operation that decreases the total suppliers is DELETE. UPDATE won'tchange the total-- number of suppliers in the table.-- Second, an AFTER trigger should be easier for Q3 than an INSTEAD oftrigger. Otherwise,

-- you need to include both INSERT and DELETE statements in the triggerto replace the one

-- that fires but already aborted by the trigger.

-- Third, in this trigger, we need to split the code into two parts toexamine if the total

-- of suppliers is greater than 29 and if it is below 25.

-- Fourth, how do we know the firing operation is INSERT or DELETE? This is simple because

-- if it is INSERT, the DELETED table must contains no rows, and,similarly, if it is DELETE,

-- the INSERTED table must be empty. We can use a simple COUNT(*) againstINSERTED or DELETED

-- to tell which operation fires the trigger.

-- Lastly, a good news is, because Suppliers_copy is created by usingSELECT-INTO, the

-- relationship between Suppliers and Products are not copied intoSuppliers_copy.

-- Otherwise, with this relationship, we won't be able to delete asupplier if it is

-- referenced by any products and, in this case, we won't be able to testthe trigger

-- for one important server behavior: when an operation is executed,constraints of columns

-- or tables or relationships will be checked before triggers are fired.If any constraint

-- fails and errors occur, triggers won't be fired and cannot be tested.

--

--insert your code of answer here (about 30 or so lines)

--

go

--Test Case 1: DELETE but failed

delete from Suppliers_copy where SupplierID between 1 and 10; -- 29 - 10 =19, below 25

--Test Case 2: DELETE and passed

delete from Suppliers_copy where SupplierID between 1 and 4; -- 29 - 4 =25, not below 25

delete from Suppliers_copy where SupplierID = 22; --> failed because only 25 suppliers left

--Test Case 3: INSERT and passed

insert into Suppliers_copy

select * from Suppliers where SupplierID between 1 and 3;

--Test Case 4: INSERT but failed

insert into Suppliers_copy

select * from Suppliers where SupplierID between 11 and 22;

Explanation / Answer

CREATE TRIGGER tgr_limitTotalSupplier ON Suppliers_copy
AFTER INSERT,DELETE
AS
DECLARE @RowCount INT;
set nocount on;
IF (SELECT COUNT(*) FROM Suppliers_copy ) > 29
BEGIN
    ROLLBACK TRANSACTION;
    raiserror('Total suppliers cannot beyond 29. Your INSERT is aborted.');
ELSE
   RowCount=select count(*) from inserted
   PRINT (RowCount+' suppliers are inserted successfully');

IF (SELECT COUNT(*) FROM Suppliers_copy ) > 29
BEGIN
   ROLLBACK TRANSACTION;
   RAISERROR ('Total suppliers cannot below 25. Your DELETE is aborted.');
ELSE
   RowCount=select count(*) from deleted
   PRINT (RowCount+' suppliers are inserted successfully');  
RETURN
END;

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

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