Error 1109: unknown table ‘account’ in filed list

Question

When I inserted the values in new table, I had met a problem in there. The trigger will be shown below:

delimiter $$
create trigger validation_check before insert on exercise.account for each row
begin
  if (account.account_type <> 'normal' or account.account_type <> 'administrator'  or account.account_type <> 'guest') 
and (account.priority > 100 and account.priority < 0) 
  then signal SQLSTATE '45000'
  set message_text  = 'The type of account which you gave was invalid or the priority cannot be inserted!';
End if;
end $$

I tried and succeeded but when insert I meet an error 1109. I’m not sure which I wrong in this.

insert into exercise.account (accountID, account_type, priority) values 
("315","administrator","100"),
("422","normal","50"),
("22","guest","20");

Answer 1

There are some issues with the given trigger.

1.You are using before insert trigger and you have to validate the values which will be inserted.

That can be done by using new.account_type and new.priority .

2.The error about the unknown column account_type is because you need another pair of parenthesis and you need the keyword new.

((account.account_type <> 'normal' or account.account_type <> 'administrator'  or account.account_type <> 'guest') 
and (account.priority > 100 and account.priority < 0) )

3.All those or conditions can be written as not in , and here account.priority > 100 and account.priority < 0 I think you need to use or instead of and. A number cant be greater than 100 and lower than 0 at the same time.

((new.account_type not in ('normal','administrator','guest'))  and (new.priority > 100 or new.priority < 0)) 

Some data examples which can be found on this fiddle as well

CREATE TABLE account(
accountID INT,
account_type varchar(20),
priority int  );

Trigger

create trigger validation_check before insert on account 
 for each row
   begin
      if ((new.account_type not in ('normal','administrator','guest')) and (new.priority > 100 or new.priority < 0)) 
        then signal SQLSTATE '45000'
        set message_text  = 'The type of account which you gave was invalid or the priority cannot be inserted!';
      end if;
   end

The following data will be inserted without any error

insert into account (accountID, account_type, priority) values 
(315,'administrator',100),
(422,'normal',50),
(22,'guest',20);

Below insert will give

The type of account which you gave was invalid or the priority cannot be inserted!

error.

insert into account (accountID, account_type, priority) values 
(1,'test1',300);