Before solving this problem, execute the IF and SELECT-INTO statements given below to build Suppliers_copy from Suppliers

Assuming the manager of ClearWater database 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 from Suppliers_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 the total number of suppliers after the operation.

/*

if object_id('Suppliers_copy') is not null

drop table Suppliers_copy;

select * into Suppliers_copy from Suppliers;

go

*/

Hints:

-- First, the only operation that increases the total suppliers is

INSERT, and the only

-- operation that decreases the total suppliers is DELETE. UPDATE won't

change the total

-- number of suppliers in the table.

-- Second, an AFTER trigger should be easier for Q3 than an INSTEAD of

trigger. Otherwise,

-- you need to include both INSERT and DELETE statements in the trigger

to replace the one

-- that fires but already aborted by the trigger.

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

examine 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(*) against

INSERTED or DELETED

-- to tell which operation fires the trigger.

-- Lastly, a good news is, because Suppliers_copy is created by using

SELECT-INTO, the

-- relationship between Suppliers and Products are not copied into

Suppliers_copy.

-- Otherwise, with this relationship, we won't be able to delete a

supplier if it is

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

the 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.

create trigger tgr_limitTotalSupplier

--

--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;

Below is a snippet of the Suppliers Table:

SupplierlD CompanyName Address City London New Orleans LA Ann Arbor MI Contact Title Purchasing Manager49 Gilbert St Order Administrator Sales Representative Marketing Manager Export Administrator Marketing Representative 92 Setsuko Chuo-ku Marketing Manager Sales Representative Sales Agent Region PostalCode Country Contact Name Charlotte Cooper Shelley Burke Regina Murphy Yoshi Nagase Exotic Liquids New Oreans Cajun Delights Grandma Kelly's Homestead Tokyo Traders Cooperativa de Quesos 'Las Cabras Antonio del Valle Saavedra Mayumi's Pavlova, Ltd Specialty Biscuits, Ltd PB Knckebrod AB NULL EC1 4SD UK 70117 48104 P.O. Box 78934 707 Oxford Rd 9-8 Sekimai Musashino-shi Tokyo Calle del Rosal 4 USA USA Japan Spain Japan Australia NULL 100 Asturias 33007 NULL 545 Oviedo Mayumi Ohno lan Devling Peter Wilson Lars Peterson Osaka 74 Rose St. Moonie Ponds Melboume Victoria 3058 29 King's Way Kaloadagatan 13 Manchester NULL M14 GSD UK Gteborg NULL S-34567 Sweden SupplierlD CompanyName Address City London New Orleans LA Ann Arbor MI Contact Title Purchasing Manager49 Gilbert St Order Administrator Sales Representative Marketing Manager Export Administrator Marketing Representative 92 Setsuko Chuo-ku Marketing Manager Sales Representative Sales Agent Region PostalCode Country Contact Name Charlotte Cooper Shelley Burke Regina Murphy Yoshi Nagase Exotic Liquids New Oreans Cajun Delights Grandma Kelly's Homestead Tokyo Traders Cooperativa de Quesos 'Las Cabras Antonio del Valle Saavedra Mayumi's Pavlova, Ltd Specialty Biscuits, Ltd PB Knckebrod AB NULL EC1 4SD UK 70117 48104 P.O. Box 78934 707 Oxford Rd 9-8 Sekimai Musashino-shi Tokyo Calle del Rosal 4 USA USA Japan Spain Japan Australia NULL 100 Asturias 33007 NULL 545 Oviedo Mayumi Ohno lan Devling Peter Wilson Lars Peterson Osaka 74 Rose St. Moonie Ponds Melboume Victoria 3058 29 King's Way Kaloadagatan 13 Manchester NULL M14 GSD UK Gteborg NULL S-34567 Sweden