Triggers Mysql

a.sql

CREATE TABLE Students
	(sid CHAR(20), 
	 name CHAR(20) NOT NULL, 
	 login CHAR(10),
	 age INTEGER,
	 gpa REAL Default 0,
   Constraint pk Primary Key (sid),
    Constraint u1 Unique (login),
Constraint gpaMax check (gpa <= 4.0) );  


drop trigger studentage;
 
 DELIMITER //
 
Create Trigger StudentAge
before Insert  On Students
For Each Row
Begin
	IF (new.age > 90) THEN 
		set new.`name` =concat (new.`name`,'..');
     END IF;
End //

drop trigger studentage_u;
 
 DELIMITER //
 
Create Trigger StudentAge_u
before update  On Students
For Each Row
Begin
		set new.age = new.age/2+old.age/2;
End //


-- drop trigger studentage_u;
 
 DELIMITER //
 
Create Trigger StudentAge_u2
before update  On Students
For Each Row
Begin
		IF new.age!=old.age+1 then
			SIGNAL SQLSTATE '45000'
			SET MESSAGE_TEXT = 'Age can only increased by one';
        end if;
End //



CREATE TABLE Employee
	(id CHAR(20), 
	 name CHAR(20) NOT NULL, 
	 hireDate date,
	 salary double,
	 bonus double,
	 Constraint pk Primary Key (id),
    Constraint u1 Unique (name)
);

 DELIMITER //

Create Trigger EmpDate
Before Insert On Employee
For Each Row
Begin
Declare 
       temp date;	
       DECLARE current_day Date;

        Select now() into current_day ;
        IF (new.hireDate is null) Then
              set new.hireDate = current_day;
       End IF;  	
End;
//

 DELIMITER //

Create Trigger EmpBonus_2
Before Update On Employee
For Each Row
Begin
      set new.bonus = new.salary * 0.03;
End;
//
 

 DELIMITER //

Create Trigger EmpBonus_1
Before Insert On Employee
For Each Row
Begin
      set new.bonus = new.salary * 0.03;
End;
//
 

create table R (cnt int);


 DELIMITER //
Create Trigger Emp_count
After Insert Or Update of salary Or Delete On Employee
For Each Statement
Begin
      delete from R;
      insert into R(cnt) Select count(*) from employee where salary > 100,000;
End;
//