Creating Primary Key From Update Values Trigger

Question

I have a students table (studentKey(PK),stdFirstName,stdLastName,dateOfBirth,tuitionFees) and I am trying to create a primary key using dateOfBirth and studentKey witch suposedly will be auto incremented.

My query seems to work fine:

select CONCAT((select CONCAT((SELECT SUBSTRING(dateOfBirth, 9, 2) as temp_day FROM students),
(SELECT SUBSTRING(dateOfBirth, 6, 2) as temp_month FROM students),
(SELECT SUBSTRING(dateOfBirth, 3, 2) as temp_year FROM students)) as temp_date),
(SELECT CAST(studentKey AS char(5)) FROM students));

This is my trigger:

DELIMITER $$
CREATE TRIGGER STUDENTKEYGENERATOR
    BEFORE UPDATE on students for each row
BEGIN
declare temp_day char(2);
declare temp_month char(2);
declare temp_year char(2);
declare temp_date char(6);
declare temp_partial_pk char(4);
declare temp_pk char(10);
set temp_day = (SELECT SUBSTRING(new.dateOfBirth, 9, 2) FROM students);
set temp_month = (SELECT SUBSTRING(new.dateOfBirth, 6, 2) FROM students);
set temp_year = (SELECT SUBSTRING(new.dateOfBirth, 3, 2) FROM students);
set temp_date = CONCAT(temp_day,temp_month,temp_year);
set temp_partial_pk = (SELECT CAST(new.studentKey AS char(4)) FROM students);
set temp_pk = CONCAT(temp_date,temp_partial_pk);
set studentKey = CAST(temp_pk AS UNSIGNED);
UPDATE students_per_course
set students_studentKey = new.studentKey;
UPDATE assignments_per_student_per_course
set students_per_courses_students_studentKey = new.studentKey;         
END $$
DELIMITER ;

Answer 1

This is my Triggers Final Form. The problem I had initially was that I was inserting rows in to my table, but had the trigger "trigger" before an update xD. Know it seems to work fine on Insert. It works for updates as well if you change "BEFORE INSERT" to "BEFORE UPDATE".

DELIMITER $$
CREATE TRIGGER StudentKeyGeneratorOnInsert
    BEFORE INSERT on students for each row
BEGIN
declare temp_day char(2);
declare temp_month char(2);
declare temp_year char(2);
declare temp_date char(6);
declare temp_partial_pk char(4);
declare temp_pk char(10);
set temp_day = SUBSTRING(new.dateOfBirth, 9, 2);
set temp_month = SUBSTRING(new.dateOfBirth, 6, 2);
set temp_year = SUBSTRING(new.dateOfBirth, 3, 2);
set temp_date = CONCAT(temp_day,temp_month,temp_year);
set temp_partial_pk = CAST(new.studentKey AS char(4));
set temp_pk = CONCAT(temp_date,temp_partial_pk);
set new.studentKey = CAST(temp_pk AS UNSIGNED);
END $$
DELIMITER ;