--1.创建一个禁止修改emp表中数据的触发器:
create or replace trigger t1
before update or delete or insert
on emp for each row
begin
RAISE no_data_found;
end;
--2.创建一个行级触发器,将从emp表中删除的记录输入到ret_emp表中(只有表结构的空表):
create table ret_emp as select * from emp where 1=0
select * from ret_emp
select * from emp for update
delete from emp where empno=7750
create or replace trigger t1
before delete on emp
for each row
begin
insert into ret_emp (
deptno,
ename,
empno,
job,
mgr,
hiredate,
sal,
comm
) values (
:old.deptno,
:old.ename,
:old.empno,
:old.job,
:old.mgr,
:old.hiredate,
:old.sal,
:old.comm
);
end;
--3.创建一个触发器,不允许用户在周六周日操作emp表:
create or replace trigger t1
before update or delete or insert
on emp for each row
begin
if to_char (sysdate,'d') in (6,7)
then
RAISE no_data_found;
end if;
end;
--4.编写触发器,在每天12点以后,不允许修改雇员工资和奖金:
create or replace trigger t1
before update of sal, comm on emp
for each row
begin
if extract(hour from systimestamp) >= 12 then
raise_application_error(-20001, 'qwe');
end if;
end;
update emp set sal=sal-1 where empno=7369
--5.创建触发器,对emp表中的员工工资只能上涨不能下降:
create or replace trigger t1
before update of sal on emp
for each row
begin
if :new.sal<:old.sal then
raise_application_error(-20001, 'qwe');
end if;
end;
--6.在表emp中,字段ename标识了当前的员工的姓名。要求新增数据时确保该字段值都为大写形式:
create or replace trigger t1
before insert or update on emp
for each row
begin
:new.ename := upper(:new.ename);
end;
select * from emp
update emp set ename='mar' where empno=7369
--7.给dept添加行级触发器:当修改dept的部门编号时,员工表的部门编号也发生变化;当删除部门时,员工表中该部门的员工被删除:
create or replace trigger t2
before update of deptno on dept
for each row
begin
update emp
set deptno = :new.deptno
where deptno = :old.deptno;
end;
--8.创建一个触发器,修改员工的工资。要求10号部门不能超过5000,20号部门不能超过10000,30号部门随意修改:
create or replace trigger t2
before update of sal on emp
for each row
begin
if :new.deptno = 10 and :new.sal > 5000 then
raise_application_error(-20001, 'qwe');
elsif :new.deptno = 20 and :new.sal > 10000 then
raise_application_error(-20002, 'asd');
end if;
end;
--9.创建一个触发器,当删除teacher表中一个记录时,自动删除course表中该老师所上课程的记录:
create or replace trigger t2
before delete on teacher
for each row
begin
delete from course where tno=:old.tno;
end;
--10.在sc表上创建一个触发器,向sc表中插入数据时,如果课程为‘Oracle’,则显示‘该课程已经考试结束,不能添加成绩’:
create or replace trigger t3
before insert on sc
for each row
declare
v_cno course.cno%type;
begin
select cno into v_cno
from course
where cname = 'Oracle';
if :new.cno = v_cno then
raise_application_error(-202020, '该课程已经考试结束,不能添加成绩');
end if;
end;
select * from teacher
insert into sc(sno,cno,score)values('s1','c004',90)
--11.在teacher表上创建一个触发器,禁止删除已任课教师的记录:
create or replace trigger t2
before delete on teacher
for each row
declare
v_exists number;
begin
select count(*)
into v_exists
from course
where tno = :old.tno;
if v_exists > 0 then
raise_application_error(-202020, '禁止删除已任课教师的记录');
end if;
end;
delete from teacher