0

Hello I want to write a trigger to update the age of the clients according to the birth date and the current date, the trigger should fire whenever a new appointment for a client is inserted into the database, and so I have this code:

drop table if exists client;
drop table if exists appointment;
drop trigger update_age;

create table client
(
    client_VAT varchar(255),
    client_name varchar(255),
    client_birth_date date,
    client_street varchar(255),
    client_city varchar(255),
    client_ZIP varchar(255),
    client_gender char(1),
    client_age tinyint,
    primary key(client_VAT)
);

insert into client values ('234928190', 'Drogon Targaryen', '2007-05-02', 'Castelo de São Jorge', 'Lisboa', '1100-129 Lisboa','M', 9);/*12*/
insert into client values ('208036709', 'Viserion Targaryen', '2007-05-02', 'Castelo de São Jorge', 'Lisboa', '1100-129 Lisboa','M', 9);/*12*/
insert into client values ('256362157', 'Rhaegal Targaryen', '2007-05-02', 'Castelo de São Jorge', 'Lisboa', '1100-129 Lisboa','M', 9);/*12*/
insert into client values ('269122966', 'Daenerys Targaryen', '1990-11-11', 'Castelo de São Jorge', 'Lisboa', '1100-129 Lisboa','F', 9);/*29*/
insert into client values ('278457541', 'Phoebe Buffay', '1966-02-16', 'Rua da Junqueira', 'Lisboa', '1349-007 Lisboa','F', 9);/*53*/
insert into client values ('264097947', 'Joey Tribbiani', '1968-07-29', 'Rua Luís de Camões', 'Lisboa', '1349-009 Lisboa','M', 9);/*51*/

create table appointment
(
    doctor_VAT varchar(255),
    date_timestamp datetime, 
    appointment_description text,
    client_VAT varchar(255),
    primary key(doctor_VAT),
    foreign key(client_VAT) references client(client_VAT)
);



create trigger update_age before insert on appointment
for each row 
begin
    declare date datetime;
    declare dob datetime;
    declare age int;

    select now() into date;

    select client_birth_date into dob
    from client;

    select datediff(dob, date) into age;

    update client
    set client_age=age;
end;

insert into appointment values ('257906673', '2019-10-23 16:30:00', 'Marcação de consulta para extração de dente do siso.', '287041433');

And when I run it it gives me the error of the title: "ER_TOO_MANY_ROWS: Result consisted of more than one row", I have searched online and I think that the problem is probably in the select into part from the client returning more than one row, but I want all the rows to be updated, what should I do?

JoNeedsCHelp
  • 1
  • 2
  • https://stackoverflow.com/questions/5252802/how-to-use-update-trigger-to-update-another-table this might be what you need – Kryesec Dec 01 '19 at 06:48
  • @Kryesec mysql does not have an inserted into psuedo table (the link is for sql server).MYSQL 'Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger.' https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html – P.Salmon Dec 01 '19 at 09:01
  • `select client_birth_date into dob from client;` will select all rows. I think there is a `where client_VAT =...` missing. – h.m.i.13 Dec 03 '19 at 17:05
  • After reading your question again I have some questions: you want to update **all** client records? Why do you want to store the age, you could calculate it if you need it. – h.m.i.13 Dec 04 '19 at 06:22

0 Answers0