Created
April 7, 2021 20:25
-
-
Save khalefa-phd/9c7b39ac4e7c034b193b32fce6011298 to your computer and use it in GitHub Desktop.
Triggers Mysql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; | |
// | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment