Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- PART 1:
- ALTER TABLE movies ADD num_rentals_datatype INT;
- ------------------
- Command(s) completed successfully.
- SELECT movie_id, num_rentals_datatype FROM movies
- ---------------
- movie_id num_rentals_datatype
- 1 NULL
- 2 NULL
- 3 NULL
- 4 NULL
- 5 NULL
- (5 ROW(s) affected)
- UPDATE movies SET num_rentals_datatype =0;
- -----------------------
- movie_id num_rentals_datatype
- 1 0
- 2 0
- 3 0
- 4 0
- 5 0
- ALTER TABLE movies ALTER COLUMN num_rentals_datatype INT NOT NULL;
- ---------------
- Command(s) completed successfully.
- PART 2:
- CREATE TRIGGER tr_Customer_Rentals_insert
- ON CustomerRentals FOR INSERT
- AS
- BEGIN
- UPDATE movies
- SET num_rentals_datatype = num_rentals_datatype +1
- WHERE movie_id = (SELECT movie_id FROM inserted);
- END;
- ----------------
- Command(s) completed successfully.
- CREATE TRIGGER tr_Customer_Rentals_delete
- ON CustomerRentals FOR DELETE
- AS
- BEGIN
- UPDATE movies
- SET num_rentals_datatype = num_rentals_datatype -1
- WHERE movie_id = (SELECT movie_id FROM DELETED);
- END;
- ---------------
- Command(s) completed successfully.
- CREATE TRIGGER tr_Customer_Rentals_update
- ON CustomerRentals FOR UPDATE
- AS
- BEGIN
- UPDATE movies
- SET num_rentals_datatype = num_rentals_datatype -1
- WHERE movie_id = (SELECT movie_id FROM DELETED);
- UPDATE movies
- SET num_rentals_datatype = num_rentals_datatype +1
- WHERE movie_id = (SELECT movie_id FROM INSERTED);
- END;
- ---------------------
- Command(s) completed successfully.
- PART 3:
- INSERT INTO CustomerRentals
- VALUES(2, 1, ‘Active’, ‘6/9/2018’, ‘6/12/2018’, 10.00)
- --------
- (1 ROW(s) affected)
- (1 ROW(s) affected)
- movie_id num_rentals_datatype
- 1 1
- 2 0
- 3 0
- 4 0
- 5 0
- UPDATE CustomerRentals
- SET movie_id = 5
- WHERE customer_id = 2
- AND movie=id = 1
- AND rental_date_out = ‘6/9/2018’
- -----------
- (1 ROW(s) affected)
- (1 ROW(s) affected)
- (1 ROW(s) affected)
- movie_id num_rentals_datatype
- 1 0
- 2 0
- 3 0
- 4 1
- 5 0
- D
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement