TRIGGER does not work in MYSQL

Question

I’m trying to make a TRIGGER that inserts data into a table as soon as data is entered into another table.

Original Table:

    CREATE TABLE tbGuia ( 
  codigo_guia INT NOT NULL AUTO_INCREMENT,
  descricao_guia VARCHAR(100) NOT NULL,
  data_vencimento_guia DATE NOT NULL,
  ambito_guia VARCHAR(45) NOT NULL,
  status_guia VARCHAR(45) DEFAULT "Pendente",
  codigo_cliente INT NOT NULL,
  CONSTRAINT pk_tbGuia PRIMARY KEY (codigo_guia),
  CONSTRAINT fk_tbGuia_client
    FOREIGN KEY (codigo_cliente)
    REFERENCES tbCliente (codigo_cliente)     
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Table I wish to populate with TRIGGER:

CREATE TABLE tbClienteGuia (
  codigo_cliente INT NOT NULL,
  codigo_guia INT NOT NULL,
  data_visualizacao DATE NOT NULL DEFAULT "0000-00-00",
  data_postagem DATE NOT NULL,
  CONSTRAINT pk_TbClienteGuia PRIMARY KEY (codigo_cliente, codigo_guia, data_visualizacao, data_postagem),
  CONSTRAINT fk_tbClienteGuia 
    FOREIGN KEY (codigo_cliente)
    REFERENCES tbCliente (codigo_cliente)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT fk_tbGuiaCliente
    FOREIGN KEY (codigo_guia)
    REFERENCES tbGuia (codigo_guia)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

A Trigger:

DELIMITER $$
CREATE TRIGGER vincular_guia_cliente AFTER INSERT ON tbguia
FOR EACH ROW
BEGIN
    INSERT INTO tbclienteguia (codigo_cliente, codigo_guia, data_visualizacao, data_postagem)
    VALUES (NEW.codigo_cliente, NEW.codigo_guia, "0000-00-00", CURDATE());
END$$
DELIMITER ;

Any suggestions? OBS: no error, just do not trigger Rigger when I insert in tbGuia.

Answer 1

You need to ensure that when you run Rigger, reference data is available.

so much for:

REFERENCES tbCliente (codigo_cliente)

Where the client code needs to be guaranteed to be registered.

How much to:

REFERENCES tbGuia (codigo_guia)

Where the guide code needs to be registered.

If these two contrainsts are not guaranteed Rigger will fail in execution.