MySQL InnoDB – Writing TRIGGER to INSERT into Tables

alter-tableinnodbinsertMySQLtrigger

Table1 "user_address"
Field "u_id"
Field "street_name"
Field "locality_name"

Table2 "street_master"
Field "s_id"
Field "s_name"

I am supposed to write a trigger which BEFORE INSERT ON user_address(table1) checks if NEW.street_name already exists in s_name in table2, and
if it does not exist then NEW.street_name is inserted in table2.

I don't know how to do it . It has to be done by trigger only.

Best Answer

Given your table structure:

create table user_address (
u_id integer auto_increment primary key,
street_name varchar(1000),
locality_name varchar(1000)
);

create table street_master
(
s_id integer auto_increment primary key,
s_name varchar(1000)
);

This will do the job:

DELIMITER $$
create trigger user_address_btrig
before insert on user_address
for each row
begin
DECLARE CNT INT;
SET CNT = ( select count(*) from street_master where s_name = new.street_name );

IF CNT=0
THEN
insert into street_master (s_name) values (NEW.street_name);
END IF;
end;
$$
DELIMITER ;

To test:

insert into user_address (street_name, locality_name) values ( 'test street', 'test locality' );

select * from street_master where s_name = 'test street';

I suggest you read the MySQL documentation on triggers and learn for yourself.

Related Question