-- DATABASE SYSTEMS 2020
-- LECTURE week 9 QUERIES
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- TRIGGERS
-- ~~~~~~~~~~~~~~~~~~~~~
-- NOTE: You do not need the GO statements, but SQL Server will show red squiggly lines if they are not added as shown

-- A Simple Trigger
-- Create a Trigger that raises a warning error message to the client if data is changed or added to the Branch table.
-- The trigger simply displays the message at the client. It has a severity level rating of 5 and it originated from Location 1

CREATE TRIGGER BranchTableModified ON Branch
-- TRIGGER EVENT
AFTER INSERT, UPDATE, DELETE
AS 
-- TRIGGER ACTION
RAISERROR ('Notify IT: Branch data modified', 5, 1);



-- TEST the trigger using the following SQL statement which doesnt actually alter the table data, as it sets the city to itself: 
UPDATE Branch SET City = City WHERE BranchNo = 'B0001';


-- TO DELETE the trigger, use: 
DROP TRIGGER BranchTableModified;

GO


-- Dream Home has a business rule that prevents a member of staff from managing more than 100 properties at the same time.
-- Create a DML Trigger to enforce this business rule on the database


-- PLEASE NOTE: We currently do not have 100 properties in our database so I am testing with 8 (EIGHT) properties
-- Also two example solutions have been provided in lectiure slides 
-- ~~~~~~~~~~~~~~~~~
CREATE TRIGGER StaffManagingTooMuch ON Property
-- TRIGGER EVENT
-- Event is an UPDATE or INSERT on the Property Table.
--		Don't need to consider DELETE as this will not increase the number of properties being managed.
AFTER INSERT, UPDATE
AS
-- DECLARE a variable to create a formatted string
DECLARE @Msg VARCHAR(100)

-- DECLARE a variable to retrieve the data in the ManagedBy column in the new row inserted/updated
DECLARE @ManagedBy CHAR(6)
SELECT @ManagedBy = ManagedBy FROM inserted;

-- TRIGGER CONDITION: 
-- Check to see how many properties staff is now managing
DECLARE @PropertyCount INT
SELECT @PropertyCount = COUNT(PropertyRef) FROM Property WHERE ManagedBy = @ManagedBy

IF (@PropertyCount) > 8
BEGIN
	-- SET Message Variable Value 
	SELECT @Msg = 'Staff ' + @ManagedBy + ' already managing 8 properties' FROM inserted

	-- TRIGGER ACTION
	-- Raise an error message
	RAISERROR(@Msg, 16, 1);
	ROLLBACK TRANSACTION; -- Reverses the INSERT Statement
END;


GO




-- Shows how many properties each member of staff is currently managing
SELECT ManagedBy, Count(PropertyRef) NumberProperties
FROM Property
GROUP BY ManagedBy
ORDER BY NumberProperties DESC


SELECT * FROM Owner

-- TEST the trigger using the following query
INSERT INTO Property (PropertyRef, OwnerID, Type, StreetAddress, City, Postcode, Sleeps, NoBedrooms, NoBathrooms, Rating, Photo, MinStayNights, WeeklyDiscount, RegisteredBranchNo, ManagedBy, Status) VALUES ('P180106', 'CO19053', 'Flat', '30 Golf Road', 'Swansea', 'SA1 8HB', 4, 2, 1, NULL, NULL, 2, 0.25, 'B0001', 'S28022', 'Verified');


-- Test to see if new property to add was rolkled back
SELECT * FROM Property WHERE PropertyRef = 'P180106'

/*
	The Solution
	The Trigger Event is an UPDATE or INSERT on the Property Table.
		Dont need to consider DELETE as this will not increase the number of properties being managed.

	Checks the inserted table for the new staff assigned to manage the newly added/updated property

	If the Trigger detects that the staff is managing too many properties:
		A message is displayed
		The INSERT Transaction is roll backed (aborted)  we will discuss this in more detail in a later session
*/

-- To DELETE THE TRIGGER
DROP TRIGGER StaffManagingTooMuch

GO

-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- Dream Home suspects someone is fiddling with manager bonuses before payday. 
-- They have decided to set up an audit trail to find out what is going on.
-- A DML Trigger is useful for setting up audit trails


-- Step 1
-- Create a table to store audit trail records.
-- There is no need to add constraints to this table.

CREATE TABLE PropertyRentalPriceAudit(
	RentalPriceIDBefore		INT,
	RentalPriceIDAfter		INT,
	PropertyRefBefore		CHAR(7),
	PropertyRefAfter		CHAR(7),
	StartDateBefore			DATE,
	StartDateAfter			DATE,
	EndDateBefore			DATE,
	EndDateAfter			DATE,
	WeekDayPriceBefore		DECIMAL(6,2),
	WeekDayPriceAfter		DECIMAL(6,2),
	SaturdayPriceBefore		DECIMAL(6,2),
	SaturdayPriceAfter		DECIMAL(6,2),
	SundayPriceBefore		DECIMAL(6,2),
	SundayPriceAfter		DECIMAL(6,2),
	ChangeDateTime			DATETIME,
	UserResponsible			VARCHAR(50),
	-- Make the transaction type a CALCULATE field
	-- Set the transaction type based on whether the inserted and/or deleted tables got updated
	TransactionType			AS CASE
			WHEN RentalPriceIDBefore IS NOT NULL AND RentalPriceIDAfter IS NOT NULL THEN 'UPDATE'
			WHEN RentalPriceIDBefore IS NOT NULL AND RentalPriceIDAfter IS NULL THEN 'DELETE'
			ELSE 'INSERT'
	END
);

GO

-- Step 2
-- Create an AFTER trigger to keep an audit of all changes and additions to the PropertyRentalPrice table.
-- As you need to track all changes, you need the:
--		deleted table: to track UPDATEs and DELETEs
--		inserted table: to track UPDATEs and INSERTs

CREATE TRIGGER PropertyRentalPriceTrackChanges ON PropertyRentalPrice
-- TRIGGER EVENT
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
	-- DECLARE Variables
	DECLARE @RentalPriceIDBefore INT, @RentalPriceIDAfter INT, @PropertyRefBefore CHAR(7), @PropertyRefAfter CHAR(7),
	@StartDateBefore DATE, 	@StartDateAfter	 DATE,	@EndDateBefore DATE, @EndDateAfter DATE, @WeekDayPriceBefore DECIMAL(6,2),
	@WeekDayPriceAfter DECIMAL(6,2), @SaturdayPriceBefore DECIMAL(6,2), @SaturdayPriceAfter DECIMAL(6,2), @SundayPriceBefore DECIMAL(6,2),
	@SundayPriceAfter DECIMAL(6,2),	@TransactionType VARCHAR(10) = NULL

	-- No Trigger Condition needed

	-- TRIGGER ACTION 
	-- Populate variables from inserted and deleted tables
	SELECT	@RentalPriceIDBefore = RentalPriceID, @StartDateBefore = StartDate, @EndDateBefore = EndDate, 
			@WeekDayPriceBefore = WeekdayPrice, @SaturdayPriceBefore = SaturdayPrice, @SundayPriceBefore = SundayPrice, @PropertyRefBefore = PropertyRef
	FROM deleted

	SELECT	@RentalPriceIDAfter = RentalPriceID, @StartDateAfter = StartDate, @EndDateAfter = EndDate, 
			@WeekDayPriceAfter = WeekdayPrice, @SaturdayPriceAfter = SaturdayPrice, @SundayPriceAfter = SundayPrice, @PropertyRefAfter = PropertyRef
	FROM inserted

	-- insert changes in audit table
	INSERT INTO PropertyRentalPriceAudit
	SELECT @RentalPriceIDBefore,@RentalPriceIDAfter, @PropertyRefBefore, @PropertyRefAfter, @StartDateBefore, @StartDateAfter, @EndDateBefore, @EndDateBefore,
			@WeekDayPriceBefore, @WeekDayPriceAfter, @SaturdayPriceBefore, @SaturdayPriceAfter, @SundayPriceBefore, @SundayPriceAfter, GETDATE(), SYSTEM_USER
	
END;

GO




-- Select some property Prices
SELECT p.StreetAddress, p.City, p.Postcode, prp.*
FROM Property p
JOIN PropertyRentalPrice prp ON p.PropertyRef = prp.PropertyRef
WHERE Postcode LIKE 'SA%'
ORDER BY PropertyRef, StartDate, EndDate


-- TEST the trigger using the following queries
-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
-- For each INSERT. UPDATE, DELETE, you should see two different messages for No of rows affected - one from the insert, the other from the trigger.

-- Perform an update 
UPDATE PropertyRentalPrice SET WeekdayPrice = 30, SaturdayPrice = 40, SundayPrice = 35 WHERE PropertyRef = 'P180019' AND RentalPriceID = 19;

-- Perform an insert
INSERT INTO PropertyRentalPrice (PropertyRef, StartDate, EndDate, WeekdayPrice, SaturdayPrice, SundayPrice) 
VALUES ('P180019', '09-Feb-2020', '15-Feb-2020', 35, 40, 40);

-- Perform a delete
-- SCOPE_IDENTITY() gives you the last IDENTITY value inserted - https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql
DELETE FROM PropertyRentalPrice WHERE RentalPriceID = SCOPE_IDENTITY();

-- Check what has been added to the audit table
SELECT * FROM PropertyRentalPriceAudit;



-- To DELETE THE TRIGGER if you wish
DROP TRIGGER PropertyRentalPriceTrackChanges;

DROP TABLE PropertyRentalPriceAudit
GO