MySQL trigger - Trigger table name interpreted as field

Question

I am having an issue with a trigger I have put in a database I am building. It is the only trigger in the database. Here are the two tables being used.

Client Table

create table client (
    clientNum INT(5) not null auto_increment,
    clientName TEXT(30) not null,
    clientEmail VARCHAR(64) not null,
    clientGender CHAR(1) not null,
    clientDOB DATE not null,
    clientAddress TEXT(50),
    clientPhone VARCHAR(12) not null,
    hasInsurance CHAR(1) not null,
    clientBalanceOwed DECIMAL(10,2),
    clientLastDateVisited DATE,
    clientNextVisitDate DATE,
    primary key (clientNum));

Insurance Table

create table insurance(
    insuranceNum INT(5) not null auto_increment,
    cardNum INT(16),
    policyNum INT(6),
    policyHolder TEXT(30),
    clientNum INT(5),
    primary key (insuranceNum),
    foreign key (clientNum) references client(clientNum));

The idea for the following trigger is to only create an insurance row when a client is added to the database that has the 'hasInsurance' field set to 'y'. Then, once that client has been added, create a new insurance row with the clientNum set to the clientNum that was just added.

The Trigger

delimiter $$
create trigger New_Insurance_Row after insert on client
for each row
begin
if(client.hasInsurance = 'y') then
insert into insurance (clientNum) values (NEW.clientNum);
end if;
end$$

Everything up to this point works as intended, until you try to insert a new client into the table and call the trigger. Once I try and add the following line of code:

The Insert Statement

insert into client(clientName, clientEmail, clientGender, clientDOB, 
clientAddress,
clientPhone, hasInsurance, clientBalanceOwed, clientLastDateVisited, 
clientNextVisitDate)
values
('Darcy Watts','[email protected]','m','1996-5-9','Belfast, Charlottetown 
PEI','123-222-3333','y','400.77','2017-8-12','2019-9-6');

When I try and run this I am met with this error:

#1109 - Unknown table 'client' in field list

So from what I've learned over the last few hours is that this error usually happens when you put the '`' (backtick) on a variable or table name, MySQL thinks that entry is part of a field list or something along that line. So I changed the trigger to just be 'client' by itself and I still got an error. Dropped the old database and everything. One more thing, the insert statement does work by itself if the trigger has not been entered yet.

Any help would be appreciated! Thanks!

sticky bit · Accepted Answer · 2019-01-30 01:34:45Z

1

I guess your hasInsurance should be from the new record.

...
if(new.hasInsurance = 'y') then
insert into insurance (clientNum) values (NEW.clientNum);
end if;
...

--

DB Fiddle (provided by GMB)

edited Jan 30, 2019 at 1:34

answered Jan 30, 2019 at 0:50

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GMB Over a year ago

Yep, that's the problem indeed... Feel free to include this db fiddle.

Max Macdonald Over a year ago

Alright I'll give that a try and see if it works. EDIT: It worked! Thanks a bunch I was stuck on this for way too long.

Collectives™ on Stack Overflow

MySQL trigger - Trigger table name interpreted as field

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related