Using SQL Server to Create Triggers
The purpose of these instructions is to instruct the user to useSQL Server to create triggers in the Premier Products database.
Connecting and Managing the Premier Products Database
Follow the SQL Server Connection Instructions and run thePremier Products Master Script-
Premier Products Master Script:
–DROP TABLE “REP” cascade constraints;–DROP TABLE “CUSTOMER” cascade constraints;–DROP TABLE “ORDERS” cascade constraints;–DROP TABLE “PART” cascade constraints;–DROP TABLE “ORDER_LINE” cascade constraints;CREATE TABLE REP(REP_NUM CHAR(2) PRIMARY KEY,LAST_NAME CHAR(15),FIRST_NAME CHAR(15),STREET CHAR(15),CITY CHAR(15),STATE CHAR(2),ZIP CHAR(5),COMMISSION DECIMAL(7,2),RATE DECIMAL(3,2) );CREATE TABLE CUSTOMER(CUSTOMER_NUM CHAR(3) PRIMARY KEY,CUSTOMER_NAME CHAR(35) NOT NULL,STREET CHAR(15),CITY CHAR(15),STATE CHAR(2),ZIP CHAR(5),BALANCE DECIMAL(8,2),CREDIT_LIMIT DECIMAL(8,2),REP_NUM CHAR(2) );CREATE TABLE ORDERS(ORDER_NUM CHAR(5) PRIMARY KEY,ORDER_DATE DATE,CUSTOMER_NUM CHAR(3) );CREATE TABLE PART(PART_NUM CHAR(4) PRIMARY KEY,DESCRIPTION CHAR(15),ON_HAND DECIMAL(4,0),CLASS CHAR(2),WAREHOUSE CHAR(1),PRICE DECIMAL(6,2),MINIMUM DECIMAL(4,0),REORDER CHAR(1) );CREATE TABLE ORDER_LINE(ORDER_NUM CHAR(5),PART_NUM CHAR(4),NUM_ORDERED DECIMAL(3,0),QUOTED_PRICE DECIMAL(6,2),PRIMARY KEY (ORDER_NUM, PART_NUM) );INSERT INTO CUSTOMERVALUES(‘148′,’Al”s Appliance and Sport’,’2837 Greenway’,’Fillmore’,’FL’,’33336′,6550.00,7500.00,’20’);INSERT INTO CUSTOMERVALUES(‘282′,’Brookings Direct’,’3827 Devon’,’Grove’,’FL’,’33321′,431.50,10000.00,’35’);INSERT INTO CUSTOMERVALUES(‘356′,’Ferguson”s’,’382 Wildwood’,’Northfield’,’FL’,’33146′,5785.00,7500.00,’65’);INSERT INTO CUSTOMERVALUES(‘408′,’The Everything Shop’,’1828 Raven’,’Crystal’,’FL’,’33503′,5285.25,5000.00,’35’);INSERT INTO CUSTOMERVALUES(‘462′,’Bargains Galore’,’3829 Central’,’Grove’,’FL’,’33321′,3412.00,10000.00,’65’);INSERT INTO CUSTOMERVALUES(‘524′,’Kline”s’,’838 Ridgeland’,’Fillmore’,’FL’,’33336′,12762.00,15000.00,’20’);INSERT INTO CUSTOMERVALUES(‘608′,’Johnson”s Department Store’,’372 Oxford’,’Sheldon’,’FL’,’33553′,2106.00,10000.00,’65’);INSERT INTO CUSTOMERVALUES(‘687′,’Lee”s Sport and Appliance’,’282 Evergreen’,’Altonville’,’FL’,’32543′,2851.00,5000.00,’35’);INSERT INTO CUSTOMERVALUES(‘725′,’Deerfield”s Four Seasons’,’282 Columbia’,’Sheldon’,’FL’,’33553′,248.00,7500.00,’35’);INSERT INTO CUSTOMERVALUES(‘842′,’All Season’,’28 Lakeview’,’Grove’,’FL’,’33321′,8221.00,7500.00,’20’);INSERT INTO ORDERSVALUES(‘21608′,’20-OCT-2003′,’148’);INSERT INTO ORDERSVALUES(‘21610′,’20-OCT-2003′,’356’);INSERT INTO ORDERSVALUES(‘21613′,’21-OCT-2003′,’408’);INSERT INTO ORDERSVALUES(‘21614′,’21-OCT-2003′,’282’);INSERT INTO ORDERSVALUES(‘21617′,’23-OCT-2003′,’608’);INSERT INTO ORDERSVALUES(‘21619′,’23-OCT-2003′,’148’);INSERT INTO ORDERSVALUES(‘21623′,’23-OCT-2003′,’608’);INSERT INTO PARTVALUES(‘AT94′,’Iron’,50,’HW’,’3′,24.95,20,’N’);INSERT INTO PARTVALUES(‘BV06′,’Home Gym’,45,’SG’,’2′,794.95,30,’N’);INSERT INTO PARTVALUES(‘CD52′,’Microwave Oven’,32,’AP’,’1′,165.00,11,’N’);INSERT INTO PARTVALUES(‘DL71′,’Cordless Drill’,21,’HW’,’3′,129.95,20,’N’);INSERT INTO PARTVALUES(‘DR93′,’Gas Range’,8,’AP’,’2′,495.00,4,’N’);INSERT INTO PARTVALUES(‘DW11′,’Washer’,12,’AP’,’3′,399.99,8,’N’);INSERT INTO PARTVALUES(‘FD21′,’Stand Mixer’,22,’HW’,’3′,159.95,22,’Y’);INSERT INTO PARTVALUES(‘KL62′,’Dryer’,12,’AP’,’1′,349.95,7,’N’);INSERT INTO PARTVALUES(‘KT03′,’Dishwasher’,8,’AP’,’3′,595.00,3,’N’);INSERT INTO PARTVALUES(‘KV29′,’Treadmill’,9,’SG’,’2′,1390.00,5,’Y’);INSERT INTO REPVALUES(’20’,’Kaiser’,’Valerie’,’624 Randall’,’Grove’,’FL’,’33321′,20542.50,0.05);INSERT INTO REPVALUES(’35’,’Hull’,’Richard’,’532 Jackson’,’Sheldon’,’FL’,’33553′,39216.00,0.07);INSERT INTO REPVALUES(’65’,’Perez’,’Juan’,’1626 Taylor’,’Fillmore’,’FL’,’33336′,23487.00,0.05);INSERT INTO ORDER_LINEVALUES(‘21608′,’AT94’,11,21.95);INSERT INTO ORDER_LINEVALUES(‘21610′,’DR93’,1,495.00);INSERT INTO ORDER_LINEVALUES(‘21610′,’DW11’,1,399.99);INSERT INTO ORDER_LINEVALUES(‘21613′,’KL62’,4,329.95);INSERT INTO ORDER_LINEVALUES(‘21614′,’KT03’,2,595.00);INSERT INTO ORDER_LINEVALUES(‘21617′,’BV06’,2,794.95);INSERT INTO ORDER_LINEVALUES(‘21617′,’CD52’,4,150.00);INSERT INTO ORDER_LINEVALUES(‘21619′,’DR93’,1,495.00);INSERT INTO ORDER_LINEVALUES(‘21623′,’KV29’,2,1290.00);
Creating Triggers
The following will instruct the user to create new triggers inSQL Server. For this example, we will create a trigger to implementthe following business rule.
BUSINESS RULE: increase a customer’s credit limit by $500 eachtime they achieve a zero balance on their account.
ForthePremierdatabase,Right-clickandselectNewQuery.
UsetheCREATETRIGGERstatementtocreateatriggernamedTRG_CREDIT_LIMIT.Belowis
the general structure of the CREATE TRIGGER statement.
ON { table { FOR
AS {
HereisthecodeforTRG_CREDIT_LIMIT.ExecuteitintheNewQuerywindow.
CREATE TRIGGER TRG_CREDIT_LIMIT ON CUSTOMER
FOR UPDATE
AS
UPDATE CUSTOMER
SET CREDIT_LIMIT = CREDIT_LIMIT + 500 WHERE BALANCE = 0;
CheckthetriggerbyclickingontheRefreshicon,thenexpandthedbo.CUSTOMERtablebyclicking on the + sign. Expand the Triggers folder by clicking onthe + sign. TRG_CREDIT_LIMIT should be listed there.
Ifyouneedtochangethetrigger,usetheALTERTRIGGERoption.
CREATE
[ OR ALTER ]
TRIGGER
[ schema_name . ]
trigger_name
| view }
[ WITH <dml_trigger_option> [ ,…n ] ]
| AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [
UPDATE
] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
sql_statement
[ ; ] [ ,…n ] | EXTERNAL NAME
<method specifier [ ; ] > }
Checkthecurrentbalance($8,221)andcreditlimit($7,500)forthecustomer,AllSeason.
SELECT CUSTOMER_NAME, BALANCE, CREDIT_LIMIT FROM CUSTOMER
WHERE CUSTOMER_NAME = ‘All Season’;
To test the TRG_CREDIT_LIMIT trigger, author SQL to set thebalance to zero for customer, All Season.
UPDATE CUSTOMER
SET BALANCE = 0
WHERE CUSTOMER_NAME = ‘All Season’;
Checkthecurrentbalance($0)andcreditlimit($8,000)forthecustomer,AllSeason.
Creating Triggers Assignment
Now create a trigger named, TRG_REORDER, to handle the followingbusiness rule. Use the UPDATE command for this trigger.
BUSINESS RULE: When the quantity on hand falls below the minimumreorder point, make sure the reorder field is set to ‘Y’.
After creating the trigger, TRG_REORDER, execute the SQLstatements below. Your trigger must pass ALL these tests to becorrect.
TriggerTest1
SELECT *
FROM PART
WHERE PART_NUM = ‘AT94’;
UPDATE PART
SET ON_HAND = 19
WHERE PART_NUM = ‘AT94’;
SELECT *
FROM PART
WHERE PART_NUM = ‘AT94’;
Is the REORDER field set to ‘Y’ for part AT94? If yes, then yourtrigger passed test 1.
TriggerTest2
SELECT *
FROM PART
WHERE PART_NUM = ‘CD52’;
UPDATE PART
SET MINIMUM = 49
WHERE PART_NUM = ‘CD52’;
SELECT *
FROM PART
WHERE PART_NUM = ‘CD52’;
Is the REORDER field set to ‘Y’ for part CD52? If yes, then yourtrigger passed test 2.
TriggerTest3
SELECT *
FROM PART
WHERE PART_NUM = ‘FD21’;
UPDATE PART
SET MINIMUM = 2
WHERE PART_NUM = ‘FD21’;
SELECT *
FROM PART
WHERE PART_NUM = ‘FD21’;
Is the REORDER field set to ‘N’ for part FD21? If yes, then yourtrigger passed test 3.
TriggerTest4
SELECT *
FROM PART
WHERE PART_NUM = ‘KV29’;
UPDATE PART
SET MINIMUM = 9
WHERE PART_NUM = ‘KV29’;
SELECT *
FROM PART
WHERE PART_NUM = ‘KV29’;
Is the REORDER field set to ‘N’ for part KV29? If yes, then yourtrigger passed test 4.
To Maximize Your Points
Use tabs and whitespace to make your code as readable aspossible. SQL statements should be lined up. Programming statementsshould include tabs. Ask the instructor if you are unsure aboutyour formatting.
Submission Instructions
After creating the trigger, TRG_REORDER, execute the SQLstatement below. Then, paste the results into a Notepad document.Then, skip a line in the notepad document and copy and paste thecode for TRG_REORDER into the same Notepad document. Save it as,LastnameFITrigger.txt, e.g. WellsMTrigger.txt. Drop this file intothe Trigger dropbox on D2L.
SELECT * FROM PART;
Expert Answer
Answer to Using SQL Server to Create Triggers The purpose of these instructions is to instruct the user to use SQL Server to creat…