Test triggers on Region in Northwind

test_triggers_auto.sql

/* ================================
   AVALIAÇÃO PARTE 2 — northwind.Region
   Requisitos esperados:
   - Tabela `Region` existe
   - Colunas VARCHAR(50): `ptnome`, `ennome`
   - Triggers BEFORE INSERT e BEFORE UPDATE que:
       * PT -> EN automático quando só PT é fornecido
       * EN -> PT automático quando só EN é fornecido
       * No-op quando ambos são fornecidos
       * Validação: apenas 7 cores (PT/EN), case-insensitive
===================================*/

-- Ajusta se necessário
USE northwind;

SET SESSION sql_safe_updates = 0;

-- ------------------------------
-- Secção 1: Verificações estruturais
-- ------------------------------
SELECT 'CHECK: tabela Region existe' AS check_name,
       CASE WHEN EXISTS (
         SELECT 1
           FROM information_schema.tables
          WHERE table_schema = DATABASE()
            AND table_name = 'Region'
       ) THEN 'PASS' ELSE 'FAIL' END AS result;

SELECT 'CHECK: coluna ptnome VARCHAR(50)' AS check_name,
       CASE WHEN EXISTS (
         SELECT 1
           FROM information_schema.columns
          WHERE table_schema = DATABASE()
            AND table_name = 'Region'
            AND column_name = 'ptnome'
            AND data_type = 'varchar'
            AND CHARACTER_MAXIMUM_LENGTH = 50
       ) THEN 'PASS' ELSE 'FAIL' END AS result;

SELECT 'CHECK: coluna ennome VARCHAR(50)' AS check_name,
       CASE WHEN EXISTS (
         SELECT 1
           FROM information_schema.columns
          WHERE table_schema = DATABASE()
            AND table_name = 'Region'
            AND column_name = 'ennome'
            AND data_type = 'varchar'
            AND CHARACTER_MAXIMUM_LENGTH = 50
       ) THEN 'PASS' ELSE 'FAIL' END AS result;

-- ------------------------------
-- Secção 2: Verificação de triggers
-- ------------------------------
SELECT 'CHECK: trigger BEFORE INSERT em Region' AS check_name,
       CASE WHEN EXISTS (
         SELECT 1
           FROM information_schema.triggers
          WHERE trigger_schema = DATABASE()
            AND event_object_table = 'Region'
            AND action_timing = 'BEFORE'
            AND event_manipulation = 'INSERT'
       ) THEN 'PASS' ELSE 'FAIL' END AS result;

SELECT 'CHECK: trigger BEFORE UPDATE em Region' AS check_name,
       CASE WHEN EXISTS (
         SELECT 1
           FROM information_schema.triggers
          WHERE trigger_schema = DATABASE()
            AND event_object_table = 'Region'
            AND action_timing = 'BEFORE'
            AND event_manipulation = 'UPDATE'
       ) THEN 'PASS' ELSE 'FAIL' END AS result;

-- ------------------------------
-- Secção 3: Testes funcionais (em transação)
--   NOTA: Se existirem rows com IDs 10..15, não serão
--         alteradas permanentemente (rollback no fim).
-- ------------------------------
START TRANSACTION;

-- Limpa a faixa de teste dentro da transação (será revertido)
DELETE FROM `Region` WHERE RegionID BETWEEN 10 AND 15;

-- 3.1 Inserções que devem traduzir automaticamente
INSERT INTO `Region` (RegionID, RegionDescription, ptnome) VALUES (10, 'Color test 1', 'Verde');
INSERT INTO `Region` (RegionID, RegionDescription, ptnome) VALUES (11, 'Color test 2', 'Anil');
INSERT INTO `Region` (RegionID, RegionDescription, ennome) VALUES (12, 'Color test 3', 'Blue');
INSERT INTO `Region` (RegionID, RegionDescription, ennome) VALUES (13, 'Color test 4', 'Indigo');

-- 3.2 Inserção no-op (ambos dados)
INSERT INTO `Region` (RegionID, RegionDescription, ptnome, ennome)
VALUES (14, 'Color test 5', 'Vermelho', 'Red');

-- 3.3 Validação de erro numa inserção inválida
-- Usamos procedimento temporário para capturar erro (sem abortar a transação)
DROP PROCEDURE IF EXISTS _testa_insercao_invalida;
DELIMITER $$
CREATE PROCEDURE _testa_insercao_invalida(OUT msg VARCHAR(64))
BEGIN
  DECLARE had_error INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET had_error = 1;
  INSERT INTO `Region` (RegionID, RegionDescription, ptnome) VALUES (15, 'Color test 6', 'Castanho'); -- inválido
  IF had_error = 1 THEN
    SET msg = 'PASS';  -- Esperamos erro
  ELSE
    SET msg = 'FAIL';  -- Não houve erro, falhou a validação
  END IF;
END$$
DELIMITER ;

CALL _testa_insercao_invalida(@ins_invalida_res);
SELECT 'TEST: inserção inválida rejeitada' AS test_name, @ins_invalida_res AS result;
DROP PROCEDURE IF EXISTS _testa_insercao_invalida;

-- 3.4 Verificações pós-inserção (traduções automáticas)
SELECT 'TEST: 10 PT->EN' AS test_name,
       CASE WHEN (SELECT LOWER(ennome) FROM `Region` WHERE RegionID = 10) = 'green'
            THEN 'PASS' ELSE 'FAIL' END AS result;

SELECT 'TEST: 11 PT->EN' AS test_name,
       CASE WHEN (SELECT LOWER(ennome) FROM `Region` WHERE RegionID = 11) = 'indigo'
            THEN 'PASS' ELSE 'FAIL' END AS result;

SELECT 'TEST: 12 EN->PT' AS test_name,
       CASE WHEN (SELECT LOWER(ptnome) FROM `Region` WHERE RegionID = 12) = 'azul'
            THEN 'PASS' ELSE 'FAIL' END AS result;

SELECT 'TEST: 13 EN->PT' AS test_name,
       CASE WHEN (SELECT LOWER(ptnome) FROM `Region` WHERE RegionID = 13) = 'anil'
            THEN 'PASS' ELSE 'FAIL' END AS result;

-- 3.5 UPDATE: traduzir quando só um lado é dado
UPDATE `Region` SET ptnome = 'Amarelo', ennome = NULL WHERE RegionID = 10;
UPDATE `Region` SET ennome = 'Orange',  ptnome = NULL WHERE RegionID = 11;

SELECT 'TEST: UPDATE 10 PT->EN' AS test_name,
       CASE WHEN (SELECT LOWER(ennome) FROM `Region` WHERE RegionID = 10) = 'yellow'
            THEN 'PASS' ELSE 'FAIL' END AS result;

SELECT 'TEST: UPDATE 11 EN->PT' AS test_name,
       CASE WHEN (SELECT LOWER(ptnome) FROM `Region` WHERE RegionID = 11) = 'laranja'
            THEN 'PASS' ELSE 'FAIL' END AS result;

-- 3.6 UPDATE no-op (ambos coerentes)
UPDATE `Region` SET ptnome = 'Azul', ennome = 'Blue' WHERE RegionID = 12;
SELECT 'TEST: UPDATE no-op (12 permanece Azul/Blue)' AS test_name,
       CASE WHEN (SELECT CONCAT(ptnome,'/',ennome) FROM `Region` WHERE RegionID = 12) = 'Azul/Blue'
            THEN 'PASS' ELSE 'FAIL' END AS result;

-- 3.7 UPDATE inválido (deve rejeitar)
DROP PROCEDURE IF EXISTS _testa_update_invalido;
DELIMITER $$
CREATE PROCEDURE _testa_update_invalido(OUT msg VARCHAR(64))
BEGIN
  DECLARE had_error INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET had_error = 1;
  UPDATE `Region` SET ptnome = 'CorInexistente', ennome = NULL WHERE RegionID = 13;
  IF had_error = 1 THEN
    SET msg = 'PASS';  -- Esperamos erro
  ELSE
    SET msg = 'FAIL';  -- Não houve erro, falhou a validação
  END IF;
END$$
DELIMITER ;

CALL _testa_update_invalido(@upd_invalido_res);
SELECT 'TEST: update inválido rejeitado' AS test_name, @upd_invalido_res AS result;
DROP PROCEDURE IF EXISTS _testa_update_invalido;

-- 3.8 Resumo final: mostra os registos de teste (serão revertidos)
SELECT RegionID, RegionDescription, ptnome, ennome
  FROM `Region`
 WHERE RegionID BETWEEN 10 AND 15
 ORDER BY RegionID;

-- Não deixar qualquer alteração
ROLLBACK;