极度郁闷,有一段时间没登陆51了,今天回来竟然把提示问题给弄丢了,猜了半个小时才猜出来。。。。

数据库之触发器

上一篇 / 下一篇  2010-01-07 08:22:08 / 个人分类:Oracle

触发器是当特定事件出现时自动执行的代码块.
触发器与过程的区别是:过程是由用户或应用程序显示调用的,而触发器是不能被直接调用的.

--创建触发器格式

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 表的触发器名称

TAG: 数据库 触发器

 

评分:0

我来说两句

Open Toolbar