OVERVIEW

This lab provides you the opportunity to implement triggers with the use of SQL commands. The lab will utilize the FLIX2YOU problem, the current schema.

In order to start this lab, you must have successfully completed Lab # 6 and 7. In these labs, you created FLIX2YOU tables and populated data into the tables in your folder in SQL Server in the VLABS environment. Lab # 8 requires you to make a modification to the database structure, create triggers, and then test the triggers. You will need to run the script in your own personal database.

All TRIGGERs when executed must be free of errors and warnings.

COMMANDS FOR TRIGGERS in SQL Server:

DROP TRIGGER trigger_name; CREATE TRIGGER trigger_name On table_name

FOR event_name AS

BEGIN

Commands;

END;

NOTE: The textbook (chapter 8) provides examples of triggers to be used in an Oracle database server. The :OLD and :NEW attributes as provided in the textbook does not apply to SQL Server. For SQL Server, if you are referring to the old data you will access a temporary table called DELETED. If you are referring to the new data you will access a temporary table called INSERTED.

Example: SQL code for SQL Server to create a trigger to update

CREATE TRIGGER tr_order_value_update ON Orderline FOR UPDATE AS BEGIN

UPDATE orders SET order_value = order_value - ((SELECT qty from DELETED) * (SELECT unitprice FROM DELETED)) + ((SELECT qty FROM INSERTED) * (SELECT unitprice FROM INSERTED)) WHERE orders.orderno = (SELECT orderno from INSERTED);

END;

A new requirement has been identified for the FLIX2YOU database. Inventory personnel at FLIX2YOU frequently need data on how many times a particular movie has been rental. There is a move within FLIX2YOU to retain the privacy of all customers and it has been mandated that inventory personnel cannot have access to customer information including rental transaction data. So, the solution is to add accumulative data to the movie table. This is done by including a field in the MOVIES table that will contain a value representing the number of times a movie has been rented. A trigger will be created to update the value in MOVIES each time a customer rental record has been added, deleted or updated.

PART 1

Write and execute the proper SQL commands to add a field (name: num_rentals datatype: int) to the MOVIES table. You will also need to initialize the field value to zero for all existing records in the MOVIES table. (See the w3 schools page (https://www.w3schools.com/sql/sql_alter.asp)Links to an external site.for help with adding a field to an existing table).

PART 2

Create three triggers for each of the events INSERT, UPDATE, DELETE. The trigger will either add, subtract or add/subtract the occurrence of the event.

PART 3

Write and execute SQL commands to test the three events. You need to show the value of num_rentals with a SELECT statement before the event and then after the event.

Attach a document to the drop box for this lab that contains the copy/pasted SQL commands that you executed AND ALL the messages returned from SQL Server from the execution of the commands. Be sure to title each section of your document with the appropriate PART .. that is PART 1, PART 2, and PART 3.

====================

And the following is the demonstration:

/* File: WCIST210_LAB_08_DEMONSTRATION.txt PLEASE NOTE .. THIS IS NOT THE LAB

Script Created by Gary Heberling on November 14, 2017 by Gary Heberling

This script will make modifications to db schema for FLIX2YOU in VLABS, SQL SERVER to allow the accumulation of number of videos in stock by video store.

*/

/* Add an addition field to the VIDEO_STORES table to hold a numeric value */

GO ALTER TABLE video_stores ADD num_videos_in_stock INT;

/* Set value of num_videos_in_stock in VIDEO_STORES for existing rows to zero */

GO UPDATE video_stores SET num_videos_in_stock = 0;

/* Alter num_videos_in_stock in VIDEO_STORES to include NOT NULL condition */

GO ALTER TABLE video_stores ALTER COLUMN num_videos_in_stock INT NOT NULL;

/* Create trigger for the INSERT event on MOVIES */

GO CREATE TRIGGER tr_movies_num_movies_add ON movies FOR INSERT AS BEGIN UPDATE video_stores SET num_videos_in_stock = num_videos_in_stock + (SELECT number_in_stock FROM INSERTED) WHERE store_id = (SELECT store_id from INSERTED); END;

/* Create trigger for the DELETE event on MOVIES */

GO CREATE TRIGGER tr_movies_num_movies_delete ON movies FOR DELETE AS BEGIN UPDATE video_stores SET num_videos_in_stock = num_videos_in_stock - (SELECT number_in_stock FROM DELETED) WHERE store_id = (SELECT store_id from DELETED); END;

/* Create trigger for the UPDATE event on MOVIES */

GO CREATE TRIGGER tr_movies_num_movies_update ON movies FOR UPDATE AS BEGIN UPDATE video_stores SET num_videos_in_stock = num_videos_in_stock - (SELECT number_in_stock FROM DELETED) WHERE store_id = (SELECT store_id from DELETED); UPDATE video_stores SET num_videos_in_stock = num_videos_in_stock + (SELECT number_in_stock FROM INSERTED) WHERE store_id = (SELECT store_id FROM INSERTED); END;

/* The following commands are commented out of this script but is available here for testing of the triggers

THIS IS AN EXAMPLE OF HOW YOU MAY WANT TO TEST YOUR TRIGGERS .. NOTE THAT THERE ARE SELECT AFTER EACH STATEMENT

IN ORDER TO TEST, you should copy and paste each command at a time ... and do NOT run the following as a script

FIRST TEST THE INSERT TRIGGER */

/* BEGIN THE TESTS

COMMENT: TEST THE INSERT trigger

SELECT store_id, store_name, num_videos_in_stock FROM video_stores;

INSERT INTO movies VALUES (1, 1, 1, 1, 2012, 'Test Movie A', 'Description of test movie a', 111, 1, 1.99, 25.39);

SELECT store_id, store_name, num_videos_in_stock FROM video_stores;

INSERT INTO movies VALUES (1, 1, 1, 2, 2012, 'Test Movie B', 'Description of test movie B', 222, 1, 1.99, 25.39);

SELECT store_id, store_name, num_videos_in_stock FROM video_stores;

INSERT INTO movies VALUES (1, 1, 1, 2, 2012, 'Test Movie C', 'Description of test movie C', 333, 1, 1.99, 25.39)

SELECT store_id, store_name, num_videos_in_stock FROM video_stores;

COMMENT: TEST THE UPDATE TRIGGER

UPDATE movies set number_in_stock = 333 WHERE movie_title = 'Test Movie B';

SELECT store_id, store_name, num_videos_in_stock FROM video_stores;

UPDATE movies set store_id = 1 WHERE movie_title = 'Test Movie B';

SELECT store_id, store_name, num_videos_in_stock FROM video_stores;

COMMENT : TEST the DELTE trigger

DELETE FROM movies where movie_title = 'Test Movie C'

SELECT store_id, store_name, num_videos_in_stock FROM video_stores;

END OF TEST */

/* END OF SCRIPT */

Video Stores Fk store_id Flix2You-ERD (Initial Design: July 2002) store_name store address store phone store_email other_store details Movies Customers ercustomer_id Pkmovie_id Fcondition_code Fr format type_code Fik genre_type_code F store_id Customer_Rentals PRitem_rental_id FR customer_id Fkmovie_id @vental-status-code member_yr membership_number date_became member customer first _name customer_ last_name customer address customer_phone customer_email customer dob Condition_ Codes release_year movie title movie_description number_in_stock rental or sale_or_both rental daily rate sale_price Pcondition_code condition_description eg Rental, Used for Sale rental date out rental _date_ returned rental_amount due other_rental_details Format_Types PRformat_type_code format_ type_description eg DVD, Video. Financial Transactions Pktransaction_id account-id @item-rental-id Fr previous transaction id Frtransaction_type_code Accounts PBaccount_id FR customer id FRpayment_method_code Genre_Codes Movie Cast Pk genre_code movie_ id PF actor id account _name account_details genre_description eg Comedy, Western transaction_date transaction_amount transaction_comment Actors PR actor_id Transaction_Types Payment Methods Rental Status_ Codes PRrental status_code actor_gender actor_first_name actor_ last_name other actor_details Pk transaction_type_code Pk payment_method_code transaction_type_description rental status_description eg Overdue. payment_method_description eg Cash, CC (Credit Card). eg Payment, Refund