Triggers

gistfile1.txt

DELIMITER $$
DROP TRIGGER IF EXISTS hotel_au_trg$$
CREATE TRIGGER hotel_au_trg AFTER UPDATE ON hotels
        FOR EACH ROW
        BEGIN
        IF (OLD.name != NEW.name OR (OLD.name IS NULL AND NEW.name IS NOT NULL)) THEN
        REPLACE INTO audits values ('',NEW.id,'name',NOW(),NULL,NULL);
        END IF;
        IF (OLD.latitude != NEW.latitude OR (OLD.latitude IS NULL AND NEW.latitude IS NOT NULL)) THEN
        REPLACE INTO audits values ('',NEW.id,'latitude',NOW(),NULL,NULL);
        END IF;
        IF (OLD.longitude != NEW.longitude OR (OLD.longitude IS NULL AND NEW.longitude IS NOT NULL)) THEN
        REPLACE INTO audits values ('',NEW.id,'longitude',NOW(),NULL,NULL);
        END IF;
        IF (OLD.rating != NEW.rating OR (OLD.rating IS NULL AND NEW.rating IS NOT NULL)) THEN
        REPLACE INTO audits values ('',NEW.id,'rating',NOW(),NULL,NULL);
        END IF;
        IF (OLD.town_id != NEW.town_id OR (OLD.town_id IS NULL AND NEW.town_id IS NOT NULL)) THEN
        REPLACE INTO audits values ('',NEW.id,'town_id',NOW(),NULL,NULL);
        END IF;
        IF (OLD.star_hotel != NEW.star_hotel OR (OLD.star_hotel IS NULL AND NEW.star_hotel IS NOT NULL)) THEN
        REPLACE INTO audits values ('',NEW.id,'star_hotel',NOW(),NULL,NULL);
        END IF;
        END $$

DROP TRIGGER IF EXISTS hotelFacility_ai_trg$$
CREATE TRIGGER hotelFacility_ai_trg AFTER INSERT ON hotel_facilities
        FOR EACH ROW
        BEGIN
        IF NEW.otb = 1 THEN
        REPLACE INTO audits values ('',NEW.hotel_id,'facilities',NOW(),NULL,NULL);
        END IF;
        END $$

DROP TRIGGER IF EXISTS hotelFacility_ad_trg$$
CREATE TRIGGER hotelFacility_ad_trg AFTER DELETE ON hotel_facilities
        FOR EACH ROW
        BEGIN
        REPLACE INTO audits values ('',OLD.hotel_id,'facilities',NOW(),NULL,NULL);
        END $$

DROP TRIGGER IF EXISTS hotelDesc_au_trg$$
CREATE TRIGGER hotelDesc_au_trg AFTER UPDATE ON hotel_descriptions
        FOR EACH ROW
        BEGIN
        IF NEW.version > 1 THEN
        REPLACE INTO audits values ('',NEW.hotel_id,'description',NOW(),NULL,NULL);
        END IF;
        END $$

DROP TRIGGER IF EXISTS hotelMedia_ai_trg$$
CREATE TRIGGER hotelMedia_ai_trg AFTER INSERT ON hotel_media
        FOR EACH ROW
        BEGIN
        IF NEW.supplier_hotel_medium_id IS NULL THEN
        REPLACE INTO audits values ('',NEW.hotel_id,'images',NOW(),NULL,NULL);
        END IF;
        END $$
        
DROP TRIGGER IF EXISTS hotelMedia_ad_trg$$
CREATE TRIGGER hotelMedia_ad_trg AFTER DELETE ON hotel_media
        FOR EACH ROW
        BEGIN
        REPLACE INTO audits values ('',OLD.hotel_id,'images',NOW(),NULL,NULL);
        END $$
DELIMITER ;