CODEWITHSUNDEEP
mysql
Ricky Ruiz
Ricky Ruiz

Reputation: 26751

How to create a Trigger that can insert a foreign key value from table B to table A

CREATE DATABASE Ebooks;

CREATE TABLE Usuario (
     Email VARCHAR (320) PRIMARY KEY, 
     NombreUsuario VARCHAR (70), 
     Contrasena VARCHAR (20), 
     Nombre VARCHAR (30), 
     Apellido VARCHAR (30), 
     IdDireccion INT, 
     FechaNac DATE, 
     Sexo VARCHAR (1), 
     Celular BIGINT, 
     IdPreferencias INT, 
     IdTarjeta INT
);

CREATE TABLE Direccion (
     IdDireccion INT PRIMARY KEY AUTO_INCREMENT, 
     CalleyNumero VARCHAR (120), 
     Colonia VARCHAR (30), 
     CP INT, 
     Ciudad VARCHAR (30), 
     Estado VARCHAR (20), 
     Pais VARCHAR  (20)
);

I want to create an AFTER INSERT ON Direccion Trigger that allows me to take Direccion.IdDireccion value and insert it into Usuario.IdDireccion.

Upvotes: 0

Views: 1881

Answers (2)

outlyer
outlyer

Reputation: 3943

See 20.3.1 Trigger Syntax and Examples for reference.

You'll need something like:

CREATE TRIGGER ins_dir AFTER INSERT ON Direccion
FOR EACH ROW INSERT INTO Usuario SET IdDireccion=NEW.IdDireccion

Although that will insert a new row where all fields except IdDireccion are null, in particular the primary key for Usuario should be included in the INSERT statement too.

Update as per the below comment:

CREATE TRIGGER ins_dir AFTER INSERT ON Direccion
FOR EACH ROW UPDATE Usuario SET IdDireccion=NEW.IdDireccion WHERE Email=<THE EMAIL>

Upvotes: 0

Chisskarzz
Chisskarzz

Reputation: 171

You can use the keyword NEW after inserting the value on Direccion table, for example:

DELIMITER //
CREATE TRIGGER trg_insert_usuario AFTER INSERT ON Direccion
FOR EACH ROW
BEGIN
    INSERT INTO `Usuario` 
    VALUES('random', '1234', '1234', '6532', '12345', NEW.IdDireccion,
          '123', '12', '1234', '124', '1234');
END //
DELIMITER ;

Upvotes: 1

Related Questions