I'm stuck when creating my trigger in mysql (on phpmyadmin), and that's why I come to ask for your help

I have 2 tables : Assemblage and Bicyclette.

Tables Diagram:

Assemblage was built like this :

CREATE TABLE `Assemblage` (  
 `ID_Assemblage` int(11) NOT NULL AUTO_INCREMENT,  
 `Nom_Assemblage` varchar(255) NOT NULL,  
 `Grandeur_Assemblage` enum('Dames','Hommes','Garçons','Filles','Adultes','Jeunes') NOT NULL,  
 `Cadre_Assemblage` varchar(10) NOT NULL,  
 `Guidon_Assemblage` varchar(10) NOT NULL,  
 `Freins_Assemblage` varchar(10) DEFAULT NULL,  
 `Selle_Assemblage` varchar(10) NOT NULL,  
 `DerailleurAvant_Assemblage` varchar(10) DEFAULT NULL,  
 `DerailleurArriere_Assemblage` varchar(10) DEFAULT NULL,  
 `RoueAvant_Assemblage` varchar(10) NOT NULL,  
 `RoueArriere_Assemblage` varchar(10) NOT NULL,  
 `Reflecteurs_Assemblage` varchar(10) DEFAULT NULL,  
 `Pedalier_Assemblage` varchar(10) NOT NULL,  
 `Ordinateur_Assemblage` varchar(10) DEFAULT NULL,  
 `Panier_Assemblage` varchar(10) DEFAULT NULL,  
 PRIMARY KEY (`ID_Assemblage`)  
)

And here Bicyclette :

CREATE TABLE `Bicyclette` (  
 `ID_Bicyclette` int(11) NOT NULL AUTO_INCREMENT,  
 `ID_Assemblage` int(11) NOT NULL,  
 `Prix_Bicyclette` float NOT NULL,  
 `Categorie_Bicyclette` enum('VTT','Vélo de course','Classique','BMX') NOT NULL,  
 `DateIntroduction_Bicyclette` date NOT NULL,  
 `DateFin_Bicyclette` date NOT NULL,  
`Nom_Bicyclette` varchar(255) DEFAULT NULL,    
 `Grandeur_Bicyclette` 
 enum('Dames','Hommes','Garçons','Filles','Adultes','Jeunes') DEFAULT NULL,   
 PRIMARY KEY (`ID_Bicyclette`),  
 KEY `ID_Assemblage` (`ID_Assemblage`),  
 CONSTRAINT `ID_Assemblage` FOREIGN KEY (`ID_Assemblage`) REFERENCES `Assemblage` (`ID_Assemblage`) ON DELETE CASCADE ON UPDATE CASCADE
)

Trigger Action :

I would like that when a new row is inserted into Bicyclette with as values :

the foreign key referring to a row of Assemblage
the values of the all the others attributes except 'Nom_Bicyclette' and 'Grandeur_Bicyclette' which will be null.

that a Trigger inserts 'Nom_Bicyclette' and 'Grandeur_Bicyclette' with the corresponding data from Assemblage thanks to the Foreign Key ID_Assemblage

Here is an example of inserting data into Bicyclette:

INSERT INTO Bicyclette VALUES (102, 547.8, "VTT", 01/01/2022, 01/01/2023)

Where 102 is the assembly model in the table Assemblage. So i would like my Trigger to perform this action (for this example):

DECLARE @name VARCHAR  
DECLARE @size VARCHAR  

@name = SELECT Nom_Assemblage FROM Assemblage WHERE ID_Assemblage = 102  
@size = SELECT Grandeur_Assemblage FROM Assemblage WHERE ID_Assemblage = 102  

UPDATE Bicyclette SET Nom_Bicyclette=@name, Grandeur_Bicyclette=@size 
WHERE ID_Bicyclette = INSERTED.ID_Bicyclette

Here is a diagram to better visualize the desired effect : Schema for trigger

Thank you in advance for your help!

(It may seem special, but I have to have the fields 'Nom_Bicyclette' and 'Grandeur_Bicyclette' which correspond to the Foreign Key ID_Assemblage in my Bicyclette Table.)

CodePudding user response：

create trigger Bicyclette_copy_attributes 
before insert on Bicyclette
for each row begin
  declare name varchar(255);
  declare size varchar(255);

  select Nom_Assemblage, Grandeur_Assemblage into name, size from Assemblage
  where ID_Assemblage = NEW.ID_Assemblage;

  set NEW.Nom_Bicyclette = name;

  set NEW.Grandeur_Bicyclette = size;
end

In MySQL, there are differences in the syntax from other brands of SQL database:

DECLARE variables do not use the @ sigil.
Use a BEFORE trigger if you need to change columns in the inserted row. If you write an AFTER trigger, it is too late, because the row has already been inserted.
Change columns with SET NEW.<column>, not UPDATE. The NEW keyword references the row that is about to be inserted.
All statements in the body of the trigger need to be terminated with a semicolon (;).

Also read https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html if you are using the MySQL client to execute the create trigger, so you understand how to set the delimiter.