mysql实例命在哪里填写_mysql 程序编写实例

DELIMITER $$

drop trigger if exists `updata_depart_lead_name`;

-- 新建触发器 --

-- 功能：在更新部门的领导编号是，自动填写部门领导名字 --

create trigger `updata_depart_lead_name` before update on `department`

for each row BEGIN

set @lead_name = '';

if new.lead_id is null or new.lead_id = '' then

set @lead_name = null;

ELSE

select name into @lead_name from staff where id = new.lead_id;

end IF;

set new.lead_name = @lead_name;

END

$$

————————————————————————————————————————————————————————————————————————————————

delimiter $$

drop trigger if exists `insert_depart_lead_name`;

-- 新建触发器 --

-- 功能：在新增部门时，自动填写部门领导的名字 --

create trigger `insert_depart_lead_name` BEFORE insert on `department`

for each row BEGIN

set @lead_name = '';

if new.lead_id is null or new.lead_id = '' THEN

set @lead_name = null;

ELSE

select name into @lead_name from staff where id = new.lead_id;

end if;

set new.lead_name = @lead_name;

end

$$

—————————————————————————————————————————————————————————————————————————————————————

delimiter $$

drop trigger if EXISTS `remove_depart_lead_on_delete_staff`;

-- 在删除staff时，清空department中领导为该staff的领导信息 --

create trigger `remove_depart_lead_on_delete_staff` after DELETE on `staff`

for each row BEGIN

update department set lead_id = null, lead_name = null where lead_id = old.id;

end

$$

————————————————————————————————————————————————————————————————————————————————————————

delimiter $$

drop trigger if exists `trigger_cursor`;

create trigger `trigger_cursor` after insert on `staff`

for each row BEGIN

declare staff_id int ;

DECLARE done int default false;

DECLARE cur_test CURSOR FOR

select id from staff ;

declare CONTINUE HANDLER FOR not FOUND set done = true;

open cur_test;

read_loop:LOOP

fetch cur_test into staff_id;

if done then

LEAVE read_loop;

ELSE

insert into message values(null, '1');

end if;

end loop;

END

$$