TriggerCreation.sql

USE TravelAgencyDB;
GO

CREATE TRIGGER TR_Continents_UpdateModifiedDate
ON Continents
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE Continents
    SET ModifiedDate = GETDATE()
    FROM Continents c
    INNER JOIN inserted i ON c.ContinentID = i.ContinentID;
END;
GO

CREATE TRIGGER TR_Countries_UpdateModifiedDate
ON Countries
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE Countries
    SET ModifiedDate = GETDATE()
    FROM Countries c
    INNER JOIN inserted i ON c.CountryID = i.CountryID;
END;
GO

CREATE TRIGGER TR_Cities_UpdateModifiedDate
ON Cities
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE Cities
    SET ModifiedDate = GETDATE()
    FROM Cities c
    INNER JOIN inserted i ON c.CityID = i.CityID;
END;
GO

CREATE TRIGGER TR_TourTypes_UpdateModifiedDate
ON TourTypes
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE TourTypes
    SET ModifiedDate = GETDATE()
    FROM TourTypes t
    INNER JOIN inserted i ON t.TourTypeID = i.TourTypeID;
END;
GO

CREATE TRIGGER TR_ClientCategories_UpdateModifiedDate
ON ClientCategories
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE ClientCategories
    SET ModifiedDate = GETDATE()
    FROM ClientCategories c
    INNER JOIN inserted i ON c.CategoryID = i.CategoryID;
END;
GO

CREATE TRIGGER TR_AmenityTypes_UpdateModifiedDate
ON AmenityTypes
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE AmenityTypes
    SET ModifiedDate = GETDATE()
    FROM AmenityTypes a
    INNER JOIN inserted i ON a.AmenityTypeID = i.AmenityTypeID;
END;
GO

CREATE TRIGGER TR_InsurancePlans_UpdateModifiedDate
ON InsurancePlans
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE InsurancePlans
    SET ModifiedDate = GETDATE()
    FROM InsurancePlans ip
    INNER JOIN inserted i ON ip.InsurancePlanID = i.InsurancePlanID;
END;
GO

-- Business Logic Triggers

IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'TR_Reservations_UpdateParticipants')
    DROP TRIGGER TR_Reservations_UpdateParticipants;
GO

CREATE TRIGGER TR_Reservations_UpdateParticipants
ON Reservations
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Handle inserted and updated records
    IF EXISTS (SELECT 1 FROM inserted)
    BEGIN
        WITH CurrentReservations AS (
            SELECT ScheduleID, 
                   SUM(NumberOfParticipants) AS TotalParticipants
            FROM Reservations
            WHERE Status IN ('Pending', 'Confirmed')
            GROUP BY ScheduleID
        )
        UPDATE ts
        SET ts.CurrentParticipants = ISNULL(cr.TotalParticipants, 0)
        FROM TourSchedules ts
        LEFT JOIN CurrentReservations cr ON ts.ScheduleID = cr.ScheduleID
        WHERE ts.ScheduleID IN (SELECT ScheduleID FROM inserted);
    END

    -- Handle deleted records
    IF EXISTS (SELECT 1 FROM deleted)
    BEGIN
        WITH CurrentReservations AS (
            SELECT ScheduleID, 
                   SUM(NumberOfParticipants) AS TotalParticipants
            FROM Reservations
            WHERE Status IN ('Pending', 'Confirmed')
            GROUP BY ScheduleID
        )
        UPDATE ts
        SET ts.CurrentParticipants = ISNULL(cr.TotalParticipants, 0)
        FROM TourSchedules ts
        LEFT JOIN CurrentReservations cr ON ts.ScheduleID = cr.ScheduleID
        WHERE ts.ScheduleID IN (SELECT ScheduleID FROM deleted);
    END
END;
GO

IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'TR_Reservations_ValidateCapacity')
    DROP TRIGGER TR_Reservations_ValidateCapacity;
GO

-- Trigger to validate max participants when making a reservation
CREATE TRIGGER TR_Reservations_ValidateCapacity
ON Reservations
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    IF EXISTS (
        SELECT 1
        FROM inserted i
        JOIN TourSchedules ts ON i.ScheduleID = ts.ScheduleID
        JOIN Tours t ON ts.TourID = t.TourID
        WHERE ts.CurrentParticipants + i.NumberOfParticipants > t.MaxParticipants
        AND i.Status IN ('Pending', 'Confirmed')
    )
    BEGIN
        RAISERROR ('Exceeded maximum participants capacity for this trip.', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END;
GO