--Ajay Singh


CREATE TRIGGER trg_mem_balance ON DETAILRENTAL
AFTER INSERT,DELETE,UPDATE
AS 
BEGIN

DECLARE @Prior_LateFee DECIMAL
DECLARE @New_LateFee DECIMAL
DECLARE @Update_Amount DECIMAL
DECLARE @Rent_Num INT
DECLARE @Detail_DaysLate DATE


SELECT @Prior_LateFee = DAY(@Detail_DaysLate) * Detail_DailyLateFee
FROM DELETED
		SELECT *
		FROM DELETED
		IF @Prior_LateFee IS NULL
			BEGIN
			SET @Prior_LateFee = 0
			END



SELECT @New_LateFee = DAY(@Detail_DaysLate) * Detail_DailyLateFee
FROM INSERTED
		SELECT *
		FROM INSERTED
		IF @New_LateFee IS NULL
			BEGIN
			SET @New_LateFee = 0
			END


IF(EXISTS (SELECT * FROM inserted))
	BEGIN
		--LOGIC TO HANDLE RECORDS IN INSERTED
		DECLARE INSERTED_CURSOR CURSOR FOR
		SELECT Rent_Num, SUM(@New_LateFee - @Prior_LateFee) AS [TOTAL]
		FROM	inserted
		GROUP BY Rent_Num

		OPEN	INSERTED_CURSOR
		FETCH	NEXT FROM INSERTED_CURSOR
				INTO @Rent_Num, @Update_Amount
		WHILE(@@FETCH_STATUS = 0)
		BEGIN
			UPDATE MEMBERSHIP
		SET Mem_Balance = Mem_Balance - @Update_Amount
		WHERE Mem_Num IN (SELECT Mem_Num
						  FROM RENTAL
						  WHERE @Rent_Num = Rent_Num)
		END
		CLOSE INSERTED_CURSOR
		DEALLOCATE INSERTED_CURSOR
	END

IF(EXISTS (SELECT * FROM DELETED))
	BEGIN
		--LOGIC TO HANDLE RECORDS IN Deleted
		DECLARE DELETED_CURSOR CURSOR FOR
		SELECT Rent_Num, SUM(@New_LateFee - @Prior_LateFee) AS [TOTAL]
		FROM	DELETED
		GROUP BY Rent_Num

		OPEN	DELETED_CURSOR
		FETCH	NEXT FROM DELETED_CURSOR
				INTO @Rent_Num, @Update_Amount
		WHILE(@@FETCH_STATUS = 0)
		BEGIN
			UPDATE MEMBERSHIP
		SET Mem_Balance = Mem_Balance + @Update_Amount
		WHERE Mem_Num IN (SELECT Mem_Num
						  FROM RENTAL
						  WHERE @Rent_Num = Rent_Num)
		END
		CLOSE DELETED_CURSOR
		DEALLOCATE DELETED_CURSOR
		END
	END
	GO