建表的代码写好了,帮忙看下触发器怎么写create table student--学生
(
stuNo number(3),
stuName varchar2(15),
stuAge number(3),
classNo number(3)
);
INSERT INTO STUDENT ( STUNO, STUNAME, STUAGE, CLASSNO ) VALUES (
1, '张三', 18, 1);
INSERT INTO STUDENT ( STUNO, STUNAME, STUAGE, CLASSNO ) VALUES (
2, '李四', 20, 2);
INSERT INTO STUDENT ( STUNO, STUNAME, STUAGE, CLASSNO ) VALUES (
3, '王五', 22, 3);create table classes--班
(
classNo number(3),
ProName varchar2(15),--专业名
deptName varchar2(15)--系名
);
INSERT INTO CLASSES ( CLASSNO, PRONAME, DEPTNAME ) VALUES (
1, '计算机', '物理系');
INSERT INTO CLASSES ( CLASSNO, PRONAME, DEPTNAME ) VALUES (
2, '数学', '数学系');
INSERT INTO CLASSES ( CLASSNO, PRONAME, DEPTNAME ) VALUES (
3, '历史', '历史系');create table department--系
(
deptNo number(3),
deptName varchar2(15)--系名
)
INSERT INTO DEPARTMENT ( DEPTNO, DEPTNAME ) VALUES (
1, '数学系');
INSERT INTO DEPARTMENT ( DEPTNO, DEPTNAME ) VALUES (
2, '物理系');
INSERT INTO DEPARTMENT ( DEPTNO, DEPTNAME ) VALUES (
3, '历史系');
where classes='要删的班级' and not exists (select * from student where classno=classes.classno)
where classno='要删的班级' and not exists (select * from student where classno=classes.classno)当要删的班级没学生的时候直接删掉,
有学生的时候不满足第二个条件,不会删掉
CREATE OR REPLACE TRIGGER TRG_DEL_STUDENT
AFTER DELETE ON STUDENT FOR EACH ROW
DECLARE
pragma autonomous_transaction;
i INT;
BEGIN
SELECT COUNT(*) INTO i FROM student WHERE stuno <>:old.stuno AND classno=:old.classno;
IF i=0 THEN
DELETE FROM classes WHERE CLASSNO=:old.CLASSNO;
END IF;
COMMIT;
END;
/
where not exists (select 1 from student b where a.classno=b.classno )