触发器是当特定事件出现时自动执行的代码块.
触发器与过程的区别是:过程是由用户或应用程序显示调用的,而触发器是不能被直接调用的.
--创建触发器格式
create [or replace] trigger trigger_name
{before | after | instead of}
{insert | delete | update [of column[, column]...]}
[or {insert | delete | update [of column[, column]...]}]
on [schema.]table_or_view_name
[referencing [new as new_row_name] [old as old_row_name]]
[for each row]
[when (condition)]
[declare variable_declation]
begin
Executable_Statements;
[Exception
Exception_handlers;]
end;
--创建行级触发器
create or replace trigger trg_test
before insert or update of deptno on dept
for each row
when (new.deptno <> 60)
begin
:new.dname := '非法部门';
end;
/
select * from dept;
insert into dept values(50, 'aaa', 'ccc'); ->insert into dept values(50, '非法部门', 'ccc');
insert into dept values (60, 'bbb', 'bbb');
insert into dept values (70, 'ccc', 'ccc');
select * from dept;
create table test1
(
id number,
name varchar2(20)
);
create sequence seq_test1;
create or replace trigger trg_insert_test1
before insert or update of id on test1
for each row
begin
if inserting then
select seq_test1.nextval into :new.id from dual;
else
raise_application_error(-20020, '不允许更新 id 值!');
end if;
end;
/
insert into test1 values(0, 'first row');
insert into test1 values(0, 'second row');
insert into test1 values(0, 'third row');
update test_trg set id = 4 where id = 3;
select * from test_trg;
create or replace trigger trg_statement
after insert or update or delete on test1
begin
if updating then
dbms_output.put_line('test1 中的数据已更新');
elsif deleting then
dbms_output.put_line('test1 中的数据已删除');
elsif inserting then
dbms_output.put_line('数据已插入 test1');
end if;
end;
/
set serveroutput on;
select * from test_trg;
insert into test_trg values (0, 'four');
update test_trg set name = 'third' where id = 3;
delete from test_trg where id = 4;
select * from test_trg;
--创建模式触发器
create table dropped_obj
(
obj_name varchar2(30),
obj_type varchar2(20),
drop_date date
);
create or replace trigger log_drop_obj
after drop on schema
begin
insert into dropped_obj values(ora_dict_obj_name, ora_dict_obj_type, sysdate);
end;
/
create table for_drop(x char);
drop table for_drop;
select * from dropped_obj;
--启用和禁用触发器
格式:alter trigger trigger_name {enable | disable}
1. alter trigger log_drop_obj disable;
create table for_drop1(x char);
drop table for_drop1;
select * from dropped_obj;
2. alter trigger log_drop_obj enable;
create table for_drop1(x char);
drop table for_drop1;
select * from dropped_obj;
3. 启用或禁用在特定表上建立的所有触发器
格式:alter table table_name {enable | disable} all trigger;
--删除触发器
格式:drop trigger trigger_name;
示例:drop trigger trg_insert_test_trg;
--查看有关触发器数据字典信息
desc user_triggers; --查看触发器的结构信息
select trigger_name from user_triggers; --查看当前用户的所有触发器名称
select trigger_name from user_triggers where table_name = 'emp'; --查看当前用户 emp 表的触发器名称