DROP TRIGGER IF EXISTS trigger_cant_block_admin;
delimiter |
CREATE TRIGGER trigger_cant_block_admin
BEFORE UPDATE
	ON `yfmbujkh8wx8xrk6`.employees FOR EACH ROW

BEGIN
	IF (NEW.function = 'Administrador') THEN
		CALL raise_application_error(20001, 'Não é possivel bloquear administradores');
	ELSE
		SET NEW.isBlocked = '1';
	END IF;
END;
|
delimiter ;

DROP TRIGGER IF EXISTS trigger_cant_add_compliment;
delimiter |
CREATE TRIGGER trigger_cant_add_compliment
BEFORE INSERT
	ON `yfmbujkh8wx8xrk6`.compliments FOR EACH ROW

BEGIN
    SET statement = (SELECT count(*) FROM `yfmbujkh8wx8xrk6`.comments WHERE 'id' = NEW.id);

    IF (statement = 0) THEN
		CALL raise_application_error(20002, 'Não é possivel adicionar elogios sem adicionar um comentário');
	END IF;
END;
|
delimiter ;

DROP TRIGGER IF EXISTS trigger_cant_add_complaint;
delimiter |
CREATE TRIGGER trigger_cant_add_complaint
BEFORE INSERT
	ON `yfmbujkh8wx8xrk6`.complaints FOR EACH ROW

BEGIN
    SET statement = (SELECT count(*) FROM `yfmbujkh8wx8xrk6`.comments WHERE 'id' = NEW.id);

    IF (statement = 0) THEN
		CALL raise_application_error(20003, 'Não é possivel adicionar reclamacões sem adicionar um comentário');
	END IF;
END;
|
delimiter ;


DROP TRIGGER IF EXISTS trigger_cant_add_photo;
delimiter |
CREATE TRIGGER trigger_cant_add_photo
BEFORE INSERT
	ON `yfmbujkh8wx8xrk6`.photos FOR EACH ROW

BEGIN
    SET statement = (SELECT count(*) FROM `yfmbujkh8wx8xrk6`.complaints WHERE 'id' = NEW.id_complaint);

    IF (statement = 0) THEN
		CALL raise_application_error(20004, 'Não é possivel adicionar fotos sem ter um comentário');
	END IF;
END;
|
delimiter ;


DROP TRIGGER IF EXISTS trigger_cant_add_notificacao;
delimiter |
CREATE TRIGGER trigger_cant_add_notificacao
BEFORE INSERT
	ON `yfmbujkh8wx8xrk6`.notifications FOR EACH ROW

BEGIN
    SET statement = (SELECT count(*) FROM `yfmbujkh8wx8xrk6`.complaints WHERE 'id' = NEW.id_complaint);

    IF (statement = 0) THEN
		CALL raise_application_error(20005, 'Não é possivel adicionar notificações sem ter um comentário');
	END IF;
END;
|
delimiter ;