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 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. Analysis: -- 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. --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;