mysql 程式編寫執行個體

標籤：end   test   new   etc   資訊   else   read   select   更新   

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
$$

 

mysql 程式編寫執行個體