Lab 8: Triggers

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 folder in SQL Server.

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.

Customers have been inquiring on how many movies available at FLIX2YOU that a particular actor appears.

So, the solution is to add accumulative data to the ACTORS table. This is done by including a field in the ACTORS table that will contain a value representing the number of movies that an actor has been identified. A trigger will be created to update the value in ACTORS each time a MOVIE_CAST record has been added, deleted or updated.

PART 1

Write and execute the proper SQL commands to add a field (name: num_movies datatype: int) to the ACTORS table. You will also need to initialize the field value to zero for all existing records in the ACTORS table. (See the w3 schools page). 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_movies 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.