-----------------------------触发器-----------------------
--定义方式:
create or replace trigger 触发器名称
before | after
insert | update [ of 列名] | delete -- insert or update or delete
on 表名
[for each row]
[declare]
begin
end;
---向emp中插入一条数据，打印一句话“新增一名员工”
create or replace trigger tri_add
after
delete -- insert or update or delete
on emp
begin
dbms_output.put_line('新增一名员工');
end;
insert into emp(empno) values(12);
---假如今天2017-06-08系统维护，禁止修改emp中的数据
create or replace trigger tri_emp
before
insert or update or delete
on emp
--for each row
declare
v_date_str varchar2(20);
begin
select to_char(sysdate,'yyyy-mm-dd') into v_date_str from dual;
if v_date_str='2017-06-08' then
-- 判断当前时间是否是2017-06-08
--如果是 错误的提示窗口
raise_application_error(-20002,'今天2017-06-08系统维护，禁止修改emp中的数据');
end if;
end;
insert into emp(empno) values(23);
select * from emp
update emp set ename='TOM' where empno=11
delete from emp where empno=11
create table t_test(
tid number(6) primary key,
tname varchar2(30)
)
create sequence seq_test;
select * from t_test
insert into t_test(tname) values('tom');
create or replace trigger tri_test
before
insert
on t_test
for each row --如果用到：new 或者:old 必须使用for each row
declare
begin
-- :new :old 伪记录
--对即将插入的数据设置tid
select seq_test.nextval into :new.tid from dual;
end;
:new :old
update
delete null
insert null
update emp set ename='TOM' where empno=11
--------------备份数据
修改员工的工资，把工资数据做备份
创建一个工资备份表
create table sal_backup(
sid number(7) primary key,
empno number(7),
sal0 number(7,2),--修改之前
sal1 number(7,2),--修改之后
sal_date date
)
create sequence seq_sal_backup
create or replace trigger tri_sal
before
update of sal --只针对修改sal
on emp
for each row
declare
--:new :old
begin
insert into sal_backup values(seq_sal_backup.nextval,:new.empno,:old.sal,:new.sal,sysdate);
end;
update emp set sal=1000 where empno=11
