My problem is that I have a table called students and I want a trigger to trigger when a new student registers, the trigger sends me an email but within the same student table obvious in another field of the same table This is possible?
It generates the following error:
Thank you!
I recommend you do the stored procedure to insert and insert, you call the procedure and not the table. Something like this:
CREATE DEFINER=root@localhost PROCEDURE correo(
IN id_students INT (11),
IN name VARCHAR(30),
IN firts_lastname VARCHAR(30),
IN second_lastname VARCHAR(30))
BEGIN
declare correo varchar (100);
declare pass varchar (40);
set correo = concat(name,'.',first_lastname,'@ejemplo.com'));
set pass= ('12345seis');
insert into students
values(id_students,name,first_lastname, second_lastname,correo,pass);
END
You can read in the documentation the following (in English):
C.1 Restrictions on Stored Programs :: Restrictions for Stored Functions
...
- A stored function or trigger can not modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.
...
Therefore, the trigger can not perform INSERT on the same table that invoked it.
A couple of options:
Using trigger (trigger):
DELIMITER //
CREATE TRIGGER 'newemail' BEFORE INSERT ON 'students'
FOR EACH ROW
BEGIN
SET NEW.'newemail' := CONCAT(NEW.'name', '[email protected]');
END//
DELIMITER ;
See db-fiddle .
Using column generated (generated column): (MySQL> = 5.7.6 13.1.18.8 CREATE TABLE and Generated Columns ):
CREATE TABLE IF NOT EXISTS 'students' (
'id_student' BIGINT UNSIGNED NOT NULL PRIMARY KEY,
'name' VARCHAR(100) NOT NULL,
'first_lastname' VARCHAR(100) NOT NULL,
'second_lastname' VARCHAR(100) NOT NULL,
'newemail' VARCHAR(113) AS (CONCAT('name', '[email protected]')) NOT NULL
);
See db-fiddle .