트리거

drop table ENROL;
drop table STUDENT;
drop table COURSE;

create table STUDENT (
  sno number(3) primary key,
  sname nvarchar2(4) constraint sname_always_exists not null,
  year number(1) default 1,
  dept nvarchar2(5),
  unique (dept, sname),
  constraint year_check check (year >=1 and year <=4 )
);

 insert into STUDENT values (100, '나수영', 4, '컴퓨터');
 insert into STUDENT values (200, '이찬수', 3, '전기');
 insert into STUDENT values (300, '정기태', 1, '컴퓨터');
 insert into STUDENT values (400, '송병길', 4, '컴퓨터');
 insert into STUDENT values (500, '박종화', 2, '산공');
 
create table COURSE (
  cno char(4),
  cname nvarchar2(10) not null,
  credit number(1) not null,
  dept nvarchar2(4) not null,
  professor nvarchar2(4),
  primary key(cno),
  unique (cno,professor)
);

insert into COURSE values ('C123', 'C프로그래밍',  3, '컴퓨터', '김성국');
insert into COURSE values ('C312', '자료구조',    3, '컴퓨터', '황수관');
insert into COURSE values ('C324', '화일구조',    3, '컴퓨터', '이규찬');
insert into COURSE values ('C413', '데이터베이스', 3, '컴퓨터', '이일로');
insert into COURSE values ('E412', '반도체',    3, '전자',   '홍봉진');
 
 
  create table ENROL (
  sno number(3) not null,
  cno char(4) not null,
  grade char,
  midterm integer,
  finterm integer,
  primary key (sno, cno),
  foreign key (sno) references STUDENT(sno)
 on delete cascade,
  foreign key (cno) references COURSE
 on delete cascade,
  check (grade in ('A', 'B', 'C', 'D', 'F'))
  );
 
insert into ENROL values (100, 'C413', 'A', 90, 95);
insert into ENROL values (100, 'E412', 'A', 95, 95);
insert into ENROL values (200, 'C123', 'B', 85, 80);
insert into ENROL values (300, 'C312', 'A', 90, 95);
insert into ENROL values (300, 'C324', 'C', 75, 75);
insert into ENROL values (300, 'C413', 'A', 95, 90);
insert into ENROL values (400, 'C312', 'A', 90, 95);
insert into ENROL values (400, 'C324', 'A', 95, 90);
insert into ENROL values (400, 'C413', 'B', 80, 85);
insert into ENROL values (400, 'E412', 'C', 65, 75);
insert into ENROL values (500, 'C312', 'B', 85, 80);

commit;

CREATE OR REPLACE FUNCTION to_str(year NUMBER)
RETURN nvarchar2
IS
str nvarchar2(10);
BEGIN
CASE year
WHEN 1 THEN str:= 'Fresh Man';
WHEN 2 THEN str:= 'Sophomore';
WHEN 3 THEN str:= 'Junior';
WHEN 4 THEN str:= 'Senior';
ELSE str:= '???';
END CASE;
RETURN str;
END;
/

select* from student;
-- 함수를 select 문에 같이 끼워 사용이 가능하다.
select sno,sname, to_str(year) as 학년,dept from student;
insert into student values (600, '홍길동' ,4, 'IT');
insert into student values (700, '쯔위' ,3, '컴퓨터');

--student table 에서 변경이 일어 났을때 자동으로 갱신이 될려면 어떻게 해야되는가
create table st_dept(
  dept nvarchar2(10),
  stnum number(3));
CREATE OR REPLACE PROCEDURE
refresh_st_dept
is
begin

delete st_dept; -- 먼저 안에있는 데이터 지우고
insert into st_dept -- 데이터 삽입
select dept,count(*) from student group by dept;
end;
/

CREATE OR REPLACE TRIGGER tg_refresh_st_dept
AFTER
INSERT OR DELETE OR UPDATE OF dept ON student
BEGIN
refresh_st_dept; --저장프로시저호출! execute 키워드불필요
END;
/
execute refresh_st_dept;
select *from st_dept;

update student set dept = 'IT' where dept = '컴퓨터';