DELIMITER //
CREATE TRIGGER tr_elective_insert BEFORE INSERT ON elective
FOR EACH ROW
BEGIN
IF(new.score IS NOT NULL&&new.score<0)
THEN
SET new.score=0;
elseif(new.score IS NOT NULL&&new.score>100)
THEN
SET new.score=100;
END IF;
END;//
DELIMITER ;
7-33
create database 后勤管理;
use 后勤管理;
create table stu
(sno char(8) primary key,
sname varchar(10),
sgender char(1),
sclass varchar(20)
);
create table dorm
(
dno smallint,
bno tinyint,
sno char(8),
primary key(dno,bno)
);
INSERT INTO stu VALUES('10101001','张永峰','男','电子商务101');
INSERT INTO stu VALUES('10101002','何小丽','女','电子商务101');
INSERT INTO stu VALUES('10102001','王斌','男','网络技术101');
INSERT INTO stu VALUES('11101001','刘淑芳','女','电子商务111');
INSERT INTO dorm VALUES('1001',4,'10101001');
INSERT INTO dorm VALUES('2001',2,'10101002');
INSERT INTO dorm VALUES('1001',1,'10102001');
INSERT INTO dorm VALUES('1010',3,'11101001');
DELIMITER //
CREATE TRIGGER tr_1 BEFORE DELETE ON stu FOR EACH ROW
BEGIN
IF(EXISTS(SELECT * FROM dorm WHERE sno=old.sno))
THEN
DELETE FROM dorm WHERE sno=old.sno;
END IF;
END;//
DELIMITER ;
