[EXERC] trigger_exemplo2

# Cria a base de dados
CREATE DATABASE IF NOT EXISTS ProjetoFormula1;

# Seleciona a base a ser utilizadas nos comando abaixo
USE ProjetoFormula1;

# Cria a tabela Pilotos
CREATE TABLE IF NOT EXISTS Pilotos
(
    codigo INT(11),
    nome VARCHAR(40) NOT NULL,
    pais VARCHAR(20),
    idade INT(6),
    equipe VARCHAR(20) NOT NULL,
    motor VARCHAR(20),
    pontos INT(10),
    PRIMARY KEY (codigo)
);


CREATE TABLE IF NOT EXISTS Ranking
(
    codigo_piloto INT(11),
    GP INT(4) NOT NULL,
    classificacao1 INT(3) NOT NULL,
    classificacao2 INT(3) NOT NULL,
    classificacao3 INT(3) NOT NULL,
    podio INT(4) NOT NULL,
    numPolePosition INT(4) NOT NULL,
    voltaMaisRapida INT(4) NOT NULL,
    PRIMARY KEY (codigo_piloto),
    FOREIGN KEY (codigo_piloto) REFERENCES Pilotos (codigo)
);


# ---------- Inserindo dados ----------

INSERT INTO Pilotos
    (codigo, nome, pais, idade, equipe, motor, pontos)
VALUES
    (111, "Sebastian Vettel", "Alemanha", 25, "RedBull", "Renault", 256),
    (112, "Fernando Alonso", "Espanha", 28, "Ferrari", "Ferrari", 252),
    (113, "Mark Alan Webber", "Áustria", 26, "RedBull", "Renault", 242),
    (114, "Lewis Hamilton", "Inglaterra", 22, "McLaren", "Mercedes", 240),
    (115, "Jenson Button", "Inglaterra", 21, "McLaren", "Mercedes", 214),
    (116, "Felipe Massa", "Brasil", 27, "Ferrari", "Ferrari", 144),
    (117, "Nico Rosberg", "Alemanha", 24, "Mercedes", "Mercedes", 142),
    (118, "Robert Kubica", "Polônia", 21, "Renault", "Renault", 136),
    (119, "Michael Schumacher", "Alemanha", 23, "Mercedes", "Mercedes", 72),
    (120, "Rubens Barrichello", "Brasil", 29, "Williams", "Cosworth", 47),
    (121, "Adrian Sutil", "Alemanha", 24, "ForceIndia", "Mercedes", 47),
    (122, "Kamui Kobayashi", "Japão", 23, "Sauber", "Ferrari", 32),
    (123, "Vitaly Petrov", "Rússia", 22, "Renault", "Renault", 27),
    (124, "Nico Hulkenberg", "Alemanha", 20, "Williams", "Cosworth", 22),
    (125, "Vitantonio Liuzzi", "Itália", 25, "ForceIndia", "Mercedes", 21),
    (126, "Sebastian Buemi", "Suiça", 16, "ToroRosso", "Ferrari", 8),
    (127, "Pedro de la Rosa", "Espanha", 22, "Sauber", "Ferrari", 6),
    (128, "Nivk Heidfeld", "Alemanha", 22, "Sauber", "Ferrari", 6),
    (129, "Jaime Alguersuari", "Espanha", 27, "ToroRosso", "Ferrari", 5),
    (130, "Sakon Yamamoto", "Japão", 20, "Hispania", "Cosworth", 0),
    (131, "Lucas Tucci di Grassi", "Brasil", 25, "Virgin", "Cosworth", 0),
    (132, "Jarno Trulli", "Itália", 18, "Lotus", "Cosworth", 0),
    (133, "Heikki Kovalainen", "Finlândia", 19, "Lotus", "Cosworth", 0),
    (134, "Timo Glock", "Alemanha", 24, "Virgin", "Cosworth", 0),
    (135, "Christian Klien", "Austrália", 20, "Hispania", "Cosworth", 0),
    (136, "Bruno Senna", "Brasil", 21, "Hispania", "Cosworth", 0),
    (137, "Karun Chandhok", "Índia", 20, "Hispania", "Cosworth", 0);


INSERT INTO Ranking
    (codigo_piloto, GP, classificacao1, classificacao2, classificacao3, podio, numPolePosition, voltaMaisRapida)
VALUES
    (135, 49, 0, 0, 0, 0, 0, 0),
    (116, 154, 11, 11, 11, 33, 15, 14),
    (112, 179, 28, 26, 20, 74, 20, 19),
    (133, 91, 1, 2, 1, 4, 1, 2),
    (132, 252, 1, 4, 6, 11, 3, 1),
    (122, 42, 0, 0, 0, 0, 0, 0),
    (114, 92, 17, 16, 11, 44, 21, 11),
    (113, 178, 7, 10, 13, 30, 9, 13),
    (119, 289, 91, 43, 20, 154, 67, 76),
    (128, 183, 0, 8, 5, 13, 1, 2),
    (124, 21, 0, 0, 0, 0, 1, 0),
    (117, 110, 0, 1, 4, 5, 0, 2),
    (127, 87, 0, 1, 0, 1, 0, 1),
    (118, 76, 1, 5, 6, 12, 1, 1),
    (120, 323, 11, 29, 28, 38, 14, 17),
    (111, 83, 21, 10, 6, 37, 30, 9),
    (126, 55, 0, 0, 0, 0, 0, 0),
    (134, 74, 0, 2, 1, 3, 0, 1),
    (123, 40, 0, 0, 1, 1, 0, 1);


CREATE TABLE IF NOT EXISTS log
(
    dataLog DATETIME NOT NULL,
    obs TEXT NOT NULL,
    tabela VARCHAR(50) NOT NULL,
    atributo VARCHAR(50) NOT NULL
);


# --------------------------------

# Exemplo 1

# Caso o piloto adicionado na tabela “pilotos” tenha menos que 18 anos,
# devemos registrar uma informação na tabela “log” automaticamente.



# --------------------------------

# Exemplo 2

# Trigger disparado após uma atualização na tabela Pilotos,
# caso a equipe e o motor de um piloto sejam da mesma empresa,
# são somados três pontos ao seu total.