DDL.sql

-- Use master database to create a new database
USE master;
GO
--CREATE DATABASE ResidentialBusiness;
--GO
--=================================================Get the SQL Server data_path=========================
DECLARE @Data_path nvarchar(256);
SET @Data_Path =(SELECT SUBSTRING(Physical_Name, 1, CHARINDEX(N'master.mdf',LOWER(Physical_Name))-1)
FROM master.sys.master_files
where Database_id=1 and file_id=1);
EXEC ('Create Database ResidentialBusiness
ON PRIMARY (Name= ResidentialBusiness_Data, FileName=''' + @Data_Path + 'ResidentialBusiness.mdf'', Size=25MB, MAXSIZE=100MB,FILEGROWTH=5%)
LOG ON (Name= ResidentialBusiness_Log, FileName=''' +@Data_Path + 'ResidentialBusiness_Log.ldf'', Size=2MB, MAXSIZE=25MB,FILEGROWTH=1%)'
);
GO

USE ResidentialBusiness;
GO
CREATE SCHEMA rb;
GO

--===================================================Table Create Section======================
USE ResidentialBusiness;
GO
-- Create Owners table
CREATE TABLE rb.Owners
(
  OwnerID INT PRIMARY KEY,
  OwnerName VARCHAR(50),
  ContactNumber NVARCHAR(20),
  Email NVARCHAR(50) UNIQUE
);
GO

USE ResidentialBusiness;
GO
-- Create Properties table
CREATE TABLE rb.Properties
(
  PropertyID INT PRIMARY KEY,
  PropertyName NVARCHAR(100),
  [Location] NVARCHAR(50),
  Price MONEY,
  [Description] VARCHAR(100),
  SquareFootage INT
);
GO

USE ResidentialBusiness;
GO
-- Create LeasesFromOwner table
CREATE TABLE rb.LeasesFromOwner
(
  LeaseFromOwnerID INT PRIMARY KEY,
  OwnerID INT FOREIGN KEY REFERENCES rb.Owners(OwnerID),
  PropertyID INT FOREIGN KEY REFERENCES rb.Properties(PropertyID),
  StartDate DATE,
  EndDate DATE ,
  Rent MONEY CHECK(Rent >= 0),
  CHECK(EndDate > StartDate)
);
GO

USE ResidentialBusiness;
GO
-- Create Apartments table
CREATE TABLE rb.Apartments
(
  ApartmentID INT PRIMARY KEY,
  PropertyID INT FOREIGN KEY REFERENCES rb.Properties(PropertyID),
  FloorNumber INT,
  UnitNumber NVARCHAR(50)
);
GO

USE ResidentialBusiness;
GO
-- Create Residents table
CREATE TABLE rb.Residents
(
  ResidentID INT PRIMARY KEY,
  ResidentName NVARCHAR(50),
  ContactNumber NVARCHAR(50),
  Email NVARCHAR(50) UNIQUE
);
GO

USE ResidentialBusiness;
GO
-- Create LeasesToResidents table
CREATE TABLE rb.LeasesToResidents
(
  LeaseToResidentID INT PRIMARY KEY,
  ApartmentID INT FOREIGN KEY REFERENCES rb.Apartments(ApartmentID),
  ResidentID INT FOREIGN KEY REFERENCES rb.Residents(ResidentID),
  StartDate DATE,
  EndDate DATE ,
  Rent MONEY CHECK(Rent >= 0),
  CHECK(EndDate > StartDate)
);
GO

USE ResidentialBusiness;
GO
-- Create MaintenanceRequests table
CREATE TABLE rb.MaintenanceRequests
(
  RequestID INT PRIMARY KEY,
  ApartmentID INT FOREIGN KEY REFERENCES rb.Apartments(ApartmentID),
  ResidentID INT FOREIGN KEY REFERENCES rb.Residents(ResidentID),
  [Status] VARCHAR(30),
  Descriptions VARCHAR(100) -- Increased length for descriptions
);
GO

USE ResidentialBusiness;
GO
-- Create Employees table
CREATE TABLE rb.Employees
(
  EmployeeID INT PRIMARY KEY,
  [Name] NVARCHAR(50),
  Position NVARCHAR(50),
  ContactNumber NVARCHAR(50),
  Email NVARCHAR(50) UNIQUE,
  HireDate DATE,
  Salary MONEY
);
GO

USE ResidentialBusiness;
GO
-- Create Buy table
CREATE TABLE rb.Buy
(
  PaymentID INT PRIMARY KEY,
  LeaseFromOwnerID INT FOREIGN KEY REFERENCES rb.LeasesFromOwner(LeaseFromOwnerID),
  OwnerID INT FOREIGN KEY REFERENCES rb.Owners(OwnerID),
  PropertyID INT FOREIGN KEY REFERENCES rb.Properties(PropertyID),
  PaymentDate DATE,
  PaymentMethod NVARCHAR(50),
  Amount MONEY CHECK(Amount >= 0),
  EmployeeSalary MONEY CHECK(EmployeeSalary >= 0),
  TotalExpenditure AS (Amount + ISNULL(EmployeeSalary, 0))
);
GO

USE ResidentialBusiness;
GO
-- Create Sales table
CREATE TABLE rb.Sales
(
  ResidencePaymentID INT PRIMARY KEY,
  LeaseToResidentID INT FOREIGN KEY REFERENCES rb.LeasesToResidents(LeaseToResidentID),
  ApartmentID INT FOREIGN KEY REFERENCES rb.Apartments(ApartmentID),
  ResidentID INT FOREIGN KEY REFERENCES rb.Residents(ResidentID),
  PaymentDate DATE,
  PaymentMethod NVARCHAR(50),
  Amount MONEY
);
GO

USE ResidentialBusiness;
GO
-- Create ProfitMargin table
CREATE TABLE rb.ProfitMargin
(
  ProfitMarginID INT,
  [Month] INT,
  [Year] INT,
  GrossProfit MONEY,
  Expenses MONEY,
  NetProfit AS (GrossProfit - Expenses)
);
GO


--====================================================Alter Section=======================
-- Rename Table and Fix Columns
--EXEC sp_rename 'ResidencePayment', 'ResidentPayments';
--EXEC sp_rename 'RevenueMaximization', 'RevenueSummary';
--
----Uses Alter
--ALTER TABLE RevenueSummary
--RENAME COLUMN TotalExcenses TO TotalExpenses;
--
--ALTER TABLE Expenditure
--RENAME COLUMN TotalExpendirute TO TotalExpenditure;


--------=============================================Create Index======================
-- Create a clustered index on MaintenanceRequests table
USE ResidentialBusiness;
GO
CREATE CLUSTERED INDEX IX_ProfitMargin_ProfitMarginID 
ON rb.ProfitMargin  (ProfitMarginID) 
GO

-- Create a non-clustered index on Employees table
USE ResidentialBusiness;
GO
CREATE NONCLUSTERED INDEX IX_Employees_Email
ON rb.Employees (Email);
GO


---===============================================View Section========================
USE ResidentialBusiness;
GO
-- Create the view with SCHEMABINDING and ENCRYPTION
CREATE VIEW rb.vw_OwnerDetails
WITH SCHEMABINDING, ENCRYPTION
AS
SELECT 
    O.OwnerID,
    O.OwnerName,
    O.ContactNumber,
    O.Email,
    P.PropertyID,
    P.PropertyName,
    P.Location,
    P.Price
FROM 
    rb.Owners O
JOIN 
    rb.LeasesFromOwner LFO ON O.OwnerID = LFO.OwnerID
JOIN 
    rb.Properties P ON LFO.PropertyID = P.PropertyID;
GO

---===============================================Stored Procedure========================
USE ResidentialBusiness;
GO
CREATE PROCEDURE rb.sp_CRUD_Owners
  @Operation NVARCHAR(15),
  @OwnerID INT = NULL,
  @OwnerName NVARCHAR(50) = NULL,
  @ContactNumber NVARCHAR(20) = NULL,
  @Email NVARCHAR(50) = NULL,
  @StatusMessage NVARCHAR(100) OUTPUT
AS
BEGIN
  DECLARE @RecordCount INT;

  BEGIN TRANSACTION;
  BEGIN TRY
    IF @Operation = 'Insert'
    BEGIN
      INSERT INTO rb.Owners (OwnerID, OwnerName, ContactNumber, Email)
      VALUES (@OwnerID, @OwnerName, @ContactNumber, @Email);
      
      SET @StatusMessage = 'Successfully inserted data.';
    END
    ELSE IF @Operation = 'Update'
    BEGIN
      UPDATE rb.Owners
      SET OwnerName = @OwnerName, ContactNumber = @ContactNumber, Email = @Email
      WHERE OwnerID = @OwnerID;
      
      SET @StatusMessage = 'Successfully updated data.';
    END
    ELSE IF @Operation = 'Delete'
    BEGIN
      DELETE FROM rb.Owners
      WHERE OwnerID = @OwnerID;
      
      SET @StatusMessage = 'Successfully deleted data.';
    END
    ELSE IF @Operation = 'Read'
    BEGIN
      SELECT * FROM rb.Owners
      WHERE OwnerID = @OwnerID;
      
      SET @StatusMessage = 'Successfully retrieved data.';
    END
    ELSE
    BEGIN
      SET @StatusMessage = 'Invalid Operation specified.';
    END

    SELECT @RecordCount = COUNT(*) FROM rb.Owners;
    COMMIT;
  END TRY
  BEGIN CATCH
    ROLLBACK;
    SET @StatusMessage = 'Something went wrong!';
    SELECT @RecordCount = COUNT(*) FROM rb.Owners;
  END CATCH;

  RETURN @RecordCount;
END;
GO

DECLARE @StatusMessage NVARCHAR(100), @Return_Value INT;
EXEC @Return_Value = rb.sp_CRUD_Owners 'Insert', 101, 'Hasan Ahmed', '01710000000', 'hasan@example.com', @StatusMessage OUTPUT;
SELECT @StatusMessage AS StatusMessage;
SELECT 'Total No. of Record: ' + CAST(@Return_Value AS VARCHAR(20)) AS [Total No. of Records];
GO

DECLARE @StatusMessage NVARCHAR(100), @Return_Value INT;
EXEC @Return_Value = rb.sp_CRUD_Owners 'Update', 101, 'Hasan Ali', '01720000000', 'hasan.ali@example.com', @StatusMessage OUTPUT;
SELECT @StatusMessage AS StatusMessage;
SELECT 'Total No. of Record: ' + CAST(@Return_Value AS VARCHAR(20)) AS [Total No. of Records];
GO

DECLARE @StatusMessage NVARCHAR(100), @Return_Value INT;
EXEC @Return_Value = rb.sp_CRUD_Owners 'Delete', 101, NULL, NULL, NULL, @StatusMessage OUTPUT;
SELECT @StatusMessage AS StatusMessage;
SELECT 'Total No. of Record: ' + CAST(@Return_Value AS VARCHAR(20)) AS [Total No. of Records];
GO


--================================================Drop Section=========================
use master
go 
drop database ResidentialBusiness
go 


use ResidentialBusiness
go 
drop table rb.Employees



----=============================================Trigger Section===========================
----=============================LogTable==================
USE ResidentialBusiness;
GO
CREATE TABLE rb.Owners_Log
(
  LogID INT PRIMARY KEY IDENTITY,
  OwnerID INT,
  OwnerName NVARCHAR(50),
  ContactNumber NVARCHAR(20),
  Email NVARCHAR(50),
  InsertedAs DATETIME DEFAULT GETDATE()
);
GO

----====================Insert_InsteadOf===================
USE ResidentialBusiness;
GO
CREATE TRIGGER rb.trg_Insert_InsteadOf_Owners
ON rb.Owners
INSTEAD OF INSERT
AS
BEGIN
  DECLARE @OwnerID INT, @OwnerName NVARCHAR(50), @ContactNumber NVARCHAR(20), @Email NVARCHAR(50);

  SELECT 
    @OwnerID = OwnerID,
    @OwnerName = OwnerName,
    @ContactNumber = ContactNumber,
    @Email = Email
  FROM inserted;

  BEGIN TRANSACTION;
  BEGIN TRY
    -- Check condition, e.g., OwnerID must be greater than 0
    IF @OwnerID <= 0
    BEGIN
      RAISERROR('OwnerID must be greater than 0.', 16, 1);
      ROLLBACK TRANSACTION;
      RETURN;
    END

    -- Insert into Owners table
    INSERT INTO rb.Owners (OwnerID, OwnerName, ContactNumber, Email)
    VALUES (@OwnerID, @OwnerName, @ContactNumber, @Email);

    -- Insert log entry into Owners_Log table
    INSERT INTO rb.Owners_Log (OwnerID, OwnerName, ContactNumber, Email)
    VALUES (@OwnerID, @OwnerName, @ContactNumber, @Email);

    COMMIT TRANSACTION;
    PRINT 'Insert Instead of Trigger executed.';
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Error occurred in Instead of Trigger.';
  END CATCH;
END;
GO

----===================================Insert_After===================
USE ResidentialBusiness;
GO
CREATE TRIGGER rb.trg_Insert_After_Owners
ON rb.Owners
AFTER INSERT
AS
BEGIN
  DECLARE @OwnerID INT, @OwnerName NVARCHAR(50), @ContactNumber NVARCHAR(20), @Email NVARCHAR(50);

  SELECT 
    @OwnerID = OwnerID,
    @OwnerName = OwnerName,
    @ContactNumber = ContactNumber,
    @Email = Email
  FROM inserted;

  BEGIN TRANSACTION;
  BEGIN TRY
    -- Insert log entry into Owners_Log table
    INSERT INTO rb.Owners_Log (OwnerID, OwnerName, ContactNumber, Email)
    VALUES (@OwnerID, @OwnerName, @ContactNumber, @Email);

    COMMIT TRANSACTION;
    PRINT 'Insert After Trigger executed.';
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Error occurred in After Trigger.';
  END CATCH;
END;
GO


-- Insert into Owners table to test the triggers
INSERT INTO rb.Owners (OwnerID, OwnerName, ContactNumber, Email)
VALUES (101, 'Rafiq Ahmed', '01710000001', 'rafiq@example.com');
GO

-- Check the logs in Owners_Log
SELECT * FROM rb.Owners_Log;
GO

----==================================Update_InsteadOf===================
USE ResidentialBusiness;
GO
CREATE TRIGGER rb.trg_Update_InsteadOf_Owners
ON rb.Owners
INSTEAD OF UPDATE
AS
BEGIN
  DECLARE @OwnerID INT, @OwnerName NVARCHAR(50), @ContactNumber NVARCHAR(20), @Email NVARCHAR(50);

  SELECT 
    @OwnerID = OwnerID,
    @OwnerName = OwnerName,
    @ContactNumber = ContactNumber,
    @Email = Email
  FROM inserted;

  BEGIN TRANSACTION;
  BEGIN TRY
    -- Update Owners table
    UPDATE rb.Owners
    SET OwnerName = @OwnerName, ContactNumber = @ContactNumber, Email = @Email
    WHERE OwnerID = @OwnerID;

    -- Insert log entry into Owners_Log table
    INSERT INTO rb.Owners_Log (OwnerID, OwnerName, ContactNumber, Email, InsertedAs)
    VALUES (@OwnerID, @OwnerName, @ContactNumber, @Email, GETDATE());

    COMMIT TRANSACTION;
    PRINT 'Update Instead of Trigger executed.';
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Error occurred in Instead of Update Trigger.';
  END CATCH;
END;
GO


----=========================================Update_After======================
USE ResidentialBusiness;
GO
CREATE TRIGGER rb.trg_Update_After_Owners
ON rb.Owners
AFTER UPDATE
AS
BEGIN
  DECLARE @OwnerID INT, @OwnerName NVARCHAR(50), @ContactNumber NVARCHAR(20), @Email NVARCHAR(50);

  SELECT 
    @OwnerID = OwnerID,
    @OwnerName = OwnerName,
    @ContactNumber = ContactNumber,
    @Email = Email
  FROM inserted;

  BEGIN TRANSACTION;
  BEGIN TRY
    -- Insert log entry into Owners_Log table
    INSERT INTO rb.Owners_Log (OwnerID, OwnerName, ContactNumber, Email, InsertedAs)
    VALUES (@OwnerID, @OwnerName, @ContactNumber, @Email, GETDATE());

    COMMIT TRANSACTION;
    PRINT 'Update After Trigger executed.';
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Error occurred in After Update Trigger.';
  END CATCH;
END;
GO


----======================Delete_InsteadOf======================
USE ResidentialBusiness;
GO
CREATE TRIGGER rb.trg_Delete_InsteadOf_Owners
ON rb.Owners
INSTEAD OF DELETE
AS
BEGIN
  DECLARE @OwnerID INT, @OwnerName NVARCHAR(50), @ContactNumber NVARCHAR(20), @Email NVARCHAR(50);

  SELECT 
    @OwnerID = OwnerID,
    @OwnerName = OwnerName,
    @ContactNumber = ContactNumber,
    @Email = Email
  FROM deleted;

  BEGIN TRANSACTION;
  BEGIN TRY
    -- Delete from Owners table
    DELETE FROM rb.Owners
    WHERE OwnerID = @OwnerID;

    -- Insert log entry into Owners_Log table
    INSERT INTO rb.Owners_Log (OwnerID, OwnerName, ContactNumber, Email, InsertedAs)
    VALUES (@OwnerID, @OwnerName, @ContactNumber, @Email, GETDATE());

    COMMIT TRANSACTION;
    PRINT 'Delete Instead of Trigger executed.';
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Error occurred in Instead of Delete Trigger.';
  END CATCH;
END;
GO


----======================Delete_After======================
USE ResidentialBusiness;
GO
CREATE TRIGGER rb.trg_Delete_After_Owners
ON rb.Owners
AFTER DELETE
AS
BEGIN
  DECLARE @OwnerID INT, @OwnerName NVARCHAR(50), @ContactNumber NVARCHAR(20), @Email NVARCHAR(50);

  SELECT 
    @OwnerID = OwnerID,
    @OwnerName = OwnerName,
    @ContactNumber = ContactNumber,
    @Email = Email
  FROM deleted;

  BEGIN TRANSACTION;
  BEGIN TRY
    -- Insert log entry into Owners_Log table
    INSERT INTO rb.Owners_Log (OwnerID, OwnerName, ContactNumber, Email, InsertedAs)
    VALUES (@OwnerID, @OwnerName, @ContactNumber, @Email, GETDATE());

    COMMIT TRANSACTION;
    PRINT 'Delete After Trigger executed.';
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Error occurred in After Delete Trigger.';
  END CATCH;
END;
GO

USE School;
GO

-- Create Insert After Trigger for Students Table
CREATE TRIGGER trg_Insert_After_Students
ON Students
AFTER INSERT
AS
BEGIN
  DECLARE @StudentID INT, @Name NVARCHAR(50), @Age NVARCHAR(10);

  SELECT 
    @StudentID = StudentID,
    @Name = Name,
    @Age = Age
  FROM inserted;

  BEGIN TRANSACTION;
  BEGIN TRY
    -- Insert log entry into Students_Log table (Assume this table is created for logging)
    INSERT INTO Students_Log (StudentID, Name, Age, Operation, OperationDate)
    VALUES (@StudentID, @Name, @Age, 'INSERT', GETDATE());

    COMMIT TRANSACTION;
    PRINT 'Insert After Trigger executed for Students.';
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Error occurred in After Insert Trigger for Students.';
  END CATCH;
END;
GO

-- Create Update After Trigger for Students Table
CREATE TRIGGER trg_Update_After_Students
ON Students
AFTER UPDATE
AS
BEGIN
  DECLARE @StudentID INT, @Name NVARCHAR(50), @Age NVARCHAR(10);

  SELECT 
    @StudentID = StudentID,
    @Name = Name,
    @Age = Age
  FROM inserted;

  BEGIN TRANSACTION;
  BEGIN TRY
    -- Insert log entry into Students_Log table
    INSERT INTO Students_Log (StudentID, Name, Age, Operation, OperationDate)
    VALUES (@StudentID, @Name, @Age, 'UPDATE', GETDATE());

    COMMIT TRANSACTION;
    PRINT 'Update After Trigger executed for Students.';
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Error occurred in After Update Trigger for Students.';
  END CATCH;
END;
GO

-- Create Delete After Trigger for Students Table
CREATE TRIGGER trg_Delete_After_Students
ON Students
AFTER DELETE
AS
BEGIN
  DECLARE @StudentID INT, @Name NVARCHAR(50), @Age NVARCHAR(10);

  SELECT 
    @StudentID = StudentID,
    @Name = Name,
    @Age = Age
  FROM deleted;

  BEGIN TRANSACTION;
  BEGIN TRY
    -- Insert log entry into Students_Log table
    INSERT INTO Students_Log (StudentID, Name, Age, Operation, OperationDate)
    VALUES (@StudentID, @Name, @Age, 'DELETE', GETDATE());

    COMMIT TRANSACTION;
    PRINT 'Delete After Trigger executed for Students.';
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Error occurred in After Delete Trigger for Students.';
  END CATCH;
END;
GO

-- Create Insert After Trigger for Enrollments Table
CREATE TRIGGER trg_Insert_After_Enrollments
ON Enrollments
AFTER INSERT
AS
BEGIN
  DECLARE @EnrollmentID INT, @StudentID INT, @Address NVARCHAR(50), @CourseName NVARCHAR(50), @ContactNumber NVARCHAR(50);

  SELECT 
    @EnrollmentID = EnrollmentID,
    @StudentID = StudentID,
    @Address = Address,
    @CourseName = CourseName,
    @ContactNumber = ContactNumber
  FROM inserted;

  BEGIN TRANSACTION;
  BEGIN TRY
    -- Insert log entry into Enrollments_Log table (Assume this table is created for logging)
    INSERT INTO Enrollments_Log (EnrollmentID, StudentID, Address, CourseName, ContactNumber, Operation, OperationDate)
    VALUES (@EnrollmentID, @StudentID, @Address, @CourseName, @ContactNumber, 'INSERT', GETDATE());

    COMMIT TRANSACTION;
    PRINT 'Insert After Trigger executed for Enrollments.';
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Error occurred in After Insert Trigger for Enrollments.';
  END CATCH;
END;
GO

-- Create Update After Trigger for Enrollments Table
CREATE TRIGGER trg_Update_After_Enrollments
ON Enrollments
AFTER UPDATE
AS
BEGIN
  DECLARE @EnrollmentID INT, @StudentID INT, @Address NVARCHAR(50), @CourseName NVARCHAR(50), @ContactNumber NVARCHAR(50);

  SELECT 
    @EnrollmentID = EnrollmentID,
    @StudentID = StudentID,
    @Address = Address,
    @CourseName = CourseName,
    @ContactNumber = ContactNumber
  FROM inserted;

  BEGIN TRANSACTION;
  BEGIN TRY
    -- Insert log entry into Enrollments_Log table
    INSERT INTO Enrollments_Log (EnrollmentID, StudentID, Address, CourseName, ContactNumber, Operation, OperationDate)
    VALUES (@EnrollmentID, @StudentID, @Address, @CourseName, @ContactNumber, 'UPDATE', GETDATE());

    COMMIT TRANSACTION;
    PRINT 'Update After Trigger executed for Enrollments.';
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Error occurred in After Update Trigger for Enrollments.';
  END CATCH;
END;
GO

-- Create Delete After Trigger for Enrollments Table
CREATE TRIGGER trg_Delete_After_Enrollments
ON Enrollments
AFTER DELETE
AS
BEGIN
  DECLARE @EnrollmentID INT, @StudentID INT, @Address NVARCHAR(50), @CourseName NVARCHAR(50), @ContactNumber NVARCHAR(50);

  SELECT 
    @EnrollmentID = EnrollmentID,
    @StudentID = StudentID,
    @Address = Address,
    @CourseName = CourseName,
    @ContactNumber = ContactNumber
  FROM deleted;

  BEGIN TRANSACTION;
  BEGIN TRY
    -- Insert log entry into Enrollments_Log table
    INSERT INTO Enrollments_Log (EnrollmentID, StudentID, Address, CourseName, ContactNumber, Operation, OperationDate)
    VALUES (@EnrollmentID, @StudentID, @Address, @CourseName, @ContactNumber, 'DELETE', GETDATE());

    COMMIT TRANSACTION;
    PRINT 'Delete After Trigger executed for Enrollments.';
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Error occurred in After Delete Trigger for Enrollments.';
  END CATCH;
END;
GO