-- DATABASE MANAGEMENT SYSTEMS 2020
-- LECTURE week 8  QUERIES
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Database Transactions
-- ~~~~~~~~~~~~~~~~~~~~~


-- NOTE: To create an EXPLICIT transaction, the SQL statements must be enclosed within explicit boundaries: 
--		BEGIN TRANSACTION statement (to EXPLICITLY mark the start of the transaction) and 
--		COMMIT or ROLLBACK (to EXPLICITLY mark the end of the transaction)

-- Speicifying a TRANSACTION name is optional


-- THROW: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql
-- I have used the THROW command in some of the statements below. It simply raises an exception (sends an error).
-- If used without any parameters in a CATCH block, this will throw (raise) the caught error again to calling program


-- Use an EXPLICIT transaction to add 2 new properties for the owner with ID = CO19051.
-- First try with a property number that already exists for the first insert
-- This example includes an invalid property - PropertyNo P190024 that already exists in the database
BEGIN TRANSACTION AddNewProperty
	-- Add Property 1
	INSERT INTO Property(PropertyNo, OwnerNo, [Type], Rooms, Rent, StreetAddress, City, Postcode, BranchNo, ManagedBy)
	VALUES ('P190024', 'CO19051', 'Bungalow', 3, 800, 'Craig-Y-Mor', 'Caswell Bay', 'SA3 3BS', 'B0001', NULL)

	-- Add Property 2
	INSERT INTO Property(PropertyNo, OwnerNo, [Type], Rooms, Rent, StreetAddress, City, Postcode, BranchNo, ManagedBy)
	VALUES ('P190101', 'CO19051', 'House', 3, 750, '24 Sealands Drive', 'Mumbles', 'SA3 4JU', 'B0001', NULL)

COMMIT TRANSACTION AddNewProperty;


-- Run the following query to check what was added to the database
-- You will notice that the second property got added even though the first property failed
-- This is because there was no ROLLBACK
--		It is the responsibility of the programmer to indicate that a rollback should occur if any of the statements fail, 
--		which will also depend on the requirements of the application.
SELECT * FROM Property WHERE PropertyNo = 'P190024' OR PropertyNo = 'P190101'

-- Delete the newly added property
DELETE FROM Property WHERE PropertyNo = 'P190101'



/*
	NOTE: 
	It is the responsibility of the Transact-SQL programmer to issue COMMIT TRANSACTION 
	only at a point when all data referenced by the transaction is logically correct.

	It is the responsibility of the programmer to indicate that a rollback should occur if any of the statements fail,
	which will also depend on the requirements of the application.
*/


-- Rewriting the transaction above using a TRY...CATCH Block 
-- If any of the INSERTS FAIL, none of the changes are committed, i.e. they are ALL ROLLED BACK
-- This example includes an invalid property - PropertyNo P190024 already exists in the database

BEGIN TRANSACTION 
	BEGIN TRY
		-- Add Property 1
		INSERT INTO Property(PropertyNo, OwnerNo, [Type], Rooms, Rent, StreetAddress, City, Postcode, BranchNo, ManagedBy)
		VALUES ('P190024', 'CO19051', 'Bungalow', 3, 800, 'Craig-Y-Mor', 'Caswell Bay', 'SA3 3BS', 'B0001', NULL)

		-- Add Property 2
		INSERT INTO Property(PropertyNo, OwnerNo, [Type], Rooms, Rent, StreetAddress, City, Postcode, BranchNo, ManagedBy)
		VALUES ('P190102', 'CO19051', 'House', 3, 750, '24 Sealands Drive', 'Mumbles', 'SA3 4JU', 'B0001', NULL)

		COMMIT TRANSACTION ; -- commit once all queries executed and none have failed
	END TRY
	BEGIN CATCH
		-- An error occurred, rollback ANY changes made during this transaction
		ROLLBACK TRANSACTION ;

		PRINT('Rolling back, an error occurred!');
		THROW; -- Throw the error that triggered catch block to calling program
	END CATCH;


-- Test to see if it added both properties
-- You should get one result because a property with PropertyNo = P190024 already exists in the database
SELECT * FROM Property WHERE PropertyNo = 'P190024' OR PropertyNo = 'P190102'



-- Changing the transaction to insert two VALID properties
BEGIN TRANSACTION 
	BEGIN TRY
		-- Add Property 1
		INSERT INTO Property(PropertyNo, OwnerNo, [Type], Rooms, Rent, StreetAddress, City, Postcode, BranchNo, ManagedBy)
		VALUES ('P190102', 'CO19051', 'Bungalow', 3, 800, 'Craig-Y-Mor', 'Caswell Bay', 'SA3 3BS', 'B0001', NULL)

		-- Add Property 2
		INSERT INTO Property(PropertyNo, OwnerNo, [Type], Rooms, Rent, StreetAddress, City, Postcode, BranchNo, ManagedBy)
		VALUES ('P190103', 'CO19051', 'House', 3, 750, '24 Sealands Drive', 'Mumbles', 'SA3 4JU', 'B0001', NULL)

		COMMIT TRANSACTION ; -- commit once all queries executed
	END TRY
	BEGIN CATCH
		-- An error occurred, rollback changes
		ROLLBACK TRANSACTION ;

		PRINT('Rolling back, an error occurred!');
		THROW -- Throw the error that triggered catch block to calling program
	END CATCH;



-- Retrieve newly added properties
SELECT * FROM Property WHERE PropertyNo = 'P190102' OR PropertyNo = 'P190103'

-- If you wish to delete the newly added properties
DELETE FROM Property WHERE PropertyNo = 'P190102' OR PropertyNo = 'P190103'




/*
	Dream Home has a rule that prevents a member of staff managing more than 100 properties at the same time.
	We will use a transaction for this but set the limit to 8 properties.
	Staff member S29007 will be used in the example.
		Every time a new property is assigned to a staff member (S29007 in this case), check how many properties already managing
		If more than 8, rollback the transaction, Otherwise commit the transaction

*/

-- Start a new transaction called AddNewProperty
BEGIN TRANSACTION AddNewProperty
	-- Add the new property managed by S28007
	INSERT INTO Property (PropertyNo, OwnerNo, Type, ROOMS, Rent, StreetAddress, City, Postcode, BranchNo, ManagedBy) VALUES
	('P190105', 'CO19029', 'Flat', 3, 480, '8 Novar Drive', 'Glasgow', 'G12 9AX', 'B0006', 'S29007');

	-- Check how many properties staff is now managing
	-- If more than 8, not OK, abort the transaction and throw an error
	IF (SELECT COUNT(*) FROM Property p WHERE ManagedBy = 'S29007') > 8
	BEGIN
		ROLLBACK TRANSACTION AddNewProperty;
		THROW 51000, 'Rolling back, staff already doing too much!', 1; -- Throw a custom error message
	END
	ELSE
		-- Less than 8, OK to commit
		COMMIT TRANSACTION AddNewProperty;


-- Try running the following SQL
-- Did it find any matching properties?
-- It is unlikely that it did because the transaction was rolled back.

SELECT * FROM Property WHERE PropertyNo = 'P190105'




-- Another Example Using TRY ... CATCH ... Block

-- Add a new owner (CO19080) to the database
-- Add a new property (P190106) for the owner (CO19080) just added and set managed by to S28007

-- Start a new transaction called AddNewProperty
BEGIN TRANSACTION AddNewProperty
	BEGIN TRY -- add try to catch any errors
		-- Add new client record
		INSERT INTO [Owner] (OwnerNo, FirstName, LastName, StreetAddress, City, Postcode, PhoneNo, Email, Password) VALUES
		('CO19080', 'David', 'Wear', '34 Albert Road', 'Swansea', 'SA3 7OR', '01792555555', 'david@where.com', 'password')

		-- Add the new property for new client managed by S28007
		INSERT INTO Property (PropertyNo, OwnerNo, Type, ROOMS, Rent, StreetAddress, City, Postcode, BranchNo, ManagedBy) VALUES
		('P190106', 'CO19080', 'Flat', 3, 480, '8 Novar Drive', 'Glasgow', 'G12 9AX', 'B0006', 'S28007');

		-- Commit ALL changes, inserts successful
		COMMIT TRANSACTION AddNewProperty;
	END TRY
	BEGIN CATCH -- add catch to handle error
		-- Rollback ALL inserts, an error has occurred. Staff S28007 does not exist
		ROLLBACK TRANSACTION AddNewProperty;

		PRINT('Rolling back, an error occurred!');
		THROW;
	END CATCH;




-- The transaction example above is made up of the following actions:
--		Add a new property owner  should be no problem
--		Add a new property for the new owner in the last step managed by S28007
-- Oops! Staff member S29007 does not exist.
--		The 2nd SQL statement results in an error and causes the Catch block to execute  rollback the transaction.

-- To test - there should be no matching records found.
SELECT * FROM [Owner] WHERE OwnerNo = 'CO19080';
SELECT * FROM Property WHERE OwnerNo = 'CO19080';



-- The following shows the transaction working - I have changed the ManagedBy to a StaffNo that exists!
-- Start a new transaction called AddNewProperty
BEGIN TRANSACTION AddNewProperty
	BEGIN TRY -- add try to catch any errors
		-- Add new client record
		INSERT INTO [Owner] (OwnerNo, FirstName, LastName, StreetAddress, City, Postcode, PhoneNo, Email, Password) VALUES
		('CO19080', 'David', 'Wear', '34 Albert Road', 'Swansea', 'SA3 7OR', '01792555555', 'david@where.com', 'password')

		-- Add the new property for new client managed by S28007
		INSERT INTO Property (PropertyNo, OwnerNo, Type, ROOMS, Rent, StreetAddress, City, Postcode, BranchNo, ManagedBy) VALUES
		('P190106', 'CO19080', 'Flat', 3, 480, '8 Novar Drive', 'Glasgow', 'G12 9AX', 'B0006', 'S29007');

		-- Commit ALL changes, inserts successful
		COMMIT TRANSACTION AddNewProperty;
	END TRY
	BEGIN CATCH -- add catch to handle error
		-- Rollback ALL inserts, an error has occurred. Staff S28007 does not exist
		ROLLBACK TRANSACTION AddNewProperty;
		THROW 51000, 'Rolling back, an error occurred!', 1;
	END CATCH;


-- To test - there should be matching records found.
SELECT * FROM [Owner] WHERE OwnerNo = 'CO19080';
SELECT * FROM Property WHERE OwnerNo = 'CO19080';


-- If you wish to delete the records - so the staff is not doing too much :)
-- You can run the following queries
DELETE FROM Property WHERE PropertyNo = 'P190106'
DELETE FROM [Owner] WHERE OwnerNo = 'CO19080'