CODEWITHSUNDEEP
mysqlsqlif-statementtriggerssql-insert
VKFLOW
VKFLOW

Reputation: 1

Creating a trigger but getting the unknown column error. Issues with if statement?

I'm trying to create a trigger that is fired after a row is inserted to update "m_name" to 'NOT GIVEN' if "m_name" IS NULL. But I get the error unknown column error. Also in my query, is the else statement necessary? I thought so since if m_name != null, keep m_name the same.Sorry pretty new to sql and any help is appreciated.

Image of my query

Upvotes: 0

Views: 252

Answers (2)

nbk
nbk

Reputation: 49375

You don't need it in your case.

But you have to use only SET without UPDATE But you need to refer to the column by using NEW before the column.

NEW is a Pseudo row, that has as the name says all the new data you want to enter

DELIMITER //
CREATE TRIGGER if_no_iilName 
AFTER INSERT ON menbers 
FOR EACH ROW
BEGIN
IF (NEW.m_name IS NULL) THEN 
    SET NEW.m_name = 'not given';
END IF;
END//
DELIMITER ;

Upvotes: 0

GMB
GMB

Reputation: 222482

I don't see the point for a trigger. A default value on the column does pretty much what you want:

create table members (
    mem_id integer not null primary key,
    f_name varchar(20) not null,
    m_name varchar(20) not null default 'NOT GIVEN',
    l_name varchar(20) not null,
    dob date
);

Then, you have two options when inserting.

Either you don't pass the column for insert:

insert into members(mem_id, f_name, l_name, dob) 
values (1, 'myName', 'lastName', current_date)

Or you can use default:

insert into members(mem_id, f_name, m_name, l_name, dob) 
values (1, 'myName', default, 'lastName', current_date);

If you really want to be able to provide an explicit null for insert, and assign a default value in that case, then a trigger might be necessary. I would recommend a before insert trigger, so you can set the default value directly before it is written:

delimiter //
create trigger members_no_m_name
before insert on members
for each row
begin
    if new.m_name is null
        then set new.m_name = 'NOT GIVEN';
    end if;
end//
delimiter ;

Upvotes: 1

Related Questions