running trigger problem
Posted by:
ll ss
Date: November 02, 2011 01:59PM
Hello,
I was trying to run the following trigger under DOS window and Mysql Workbench.
It seems working fine at Mysql workbench, but not under DOS wnidow.
The main problem under DOS window, it doesn't come up with "MYSQL>" prompt after the end of trgger and before "select ..." statement.
I would like to know where the problem is.
Thanks,
Kai
*************below is the sql script********************
drop database if exists try_kai;
CREATE DATABASE IF NOT EXISTS try_kai;
Use try_kai;
CREATE TABLE IF NOT EXISTS kai_1
(
id int NOT NULL PRIMARY KEY,
dx1 CHAR(100),
dx2 CHAR(100),
dx3 CHAR(100)
);
CREATE TABLE IF NOT EXISTS kai_2
(
id int NOT NULL PRIMARY KEY,
dx_BR char(1),
dx_OV char(1),
dx_lung char(1),
dx_skin char(1),
dx_liver char(1),
dx_eye char(1)
);
delimiter $$
CREATE TRIGGER dx_insert AFTER insert ON kai_1
FOR EACH ROW
BEGIN
declare id int(10);
declare dx_BR char(1) default '';
declare dx_OV char(1) default '';
declare dx_lung char(1) default '';
declare dx_skin char(1) default '';
declare dx_liver char(1) default '';
declare dx_eye char(1) default '';
if ucase(new.dx1) ='BR' THEN set dx_BR = 'y';
elseif ucase(new.dx2) ='BR' THEN set dx_BR = 'y';
elseif ucase(new.dx3) ='BR' THEN set dx_BR = 'y';
else set dx_BR = 'n';
END if ;
if ucase(new.dx1) ='OV' THEN set dx_OV = 'y';
elseif ucase(new.dx2) ='OV' THEN set dx_OV = 'y';
elseif ucase(new.dx3) ='OV' THEN set dx_OV = 'y';
else set dx_OV = 'n';
END if;
if ucase(new.dx1) ='LUNG' THEN set dx_lung = 'y';
elseif ucase(new.dx2) ='LUNG' THEN set dx_lung = 'y';
elseif ucase(new.dx3) ='LUNG' THEN set dx_lung = 'y';
else set dx_lung = 'n';
END if;
if ucase(new.dx1) ='SKIN' THEN set dx_skin = 'y';
elseif ucase(new.dx2) ='SKIN' THEN set dx_skin = 'y';
elseif ucase(new.dx3) ='SKIN' THEN set dx_skin = 'y';
else set dx_skin = 'n';
END if;
if ucase(new.dx1) ='LIVER' THEN set dx_liver = 'y';
elseif ucase(new.dx2) ='LIVER' THEN set dx_liver = 'y';
elseif ucase(new.dx3) ='LIVER' THEN set dx_liver = 'y';
ELSE set dx_liver = 'n';
END if;
if ucase(new.dx1) ='EYE' THEN set dx_eye = 'y';
elseif ucase(new.dx2) ='EYE' THEN set dx_eye = 'y';
elseif ucase(new.dx3) ='EYE' THEN set dx_eye = 'y';
ELSE set dx_eye = 'n';
END if;
if new.id <> '' then set id =new.id;
END if;
insert into kai_2 (id, dx_BR, dx_OV, dx_lung, dx_skin, dx_liver, dx_eye) values (id, dx_BR,
dx_OV, dx_lung, dx_skin, dx_liver, dx_eye);
END
$$ delimiter ;
insert into kai_1 (id, dx1, dx2, dx3) values (3159, 'bR', 'ov', 'eye' );
insert into kai_1 (id, dx1, dx2, dx3) values (2376, 'skin', 'ov', 'lung' );
insert into kai_1 (id, dx1, dx2, dx3) values (8745, 'liver', '', 'br' );
insert into kai_1 (id, dx1, dx2, dx3) values (687, '', '', '' );
select * from kai_1;
select * from kai_2;
****************************end of my script**********************************