MySQL Program Authoring Example

DELIMITER $$

Drop trigger if exists ' updata_depart_lead_name ';
--New trigger--
--function: In the Update department leader number is, automatically fill in the Department leader 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 the staff where id = new.lead_id;
End IF;

Set new.lead_name = @lead_name;
END

$$

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

Delimiter $$

Drop trigger if exists ' insert_depart_lead_name ';
--New trigger--
--function: When the new department, automatically fill in the name of the department leader-
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 the 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 ';
--when deleting the staff, clear the leadership information in department
Create TRIGGER ' remove_depart_lead_on_delete_staff ' after delete in ' 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 in ' staff '
For each row BEGIN
declare staff_id int;
DECLARE done int default false;
DECLARE Cur_test CURSOR for
Select ID from the staff;
Declare CONTINUE HANDLER for don't FOUND set done = true;
Open cur_test;
Read_loop:loop
Fetch cur_test into staff_id;
If do then
LEAVE Read_loop;
ELSE
INSERT into message values (NULL, ' 1 ');
End If;
End Loop;
END
$$

MySQL Program Authoring Example