oracle触发器简单使用

触发器的作用

数据确认，实施复杂的安全性检查，数据的备份和同步，对于违反规定数据库操作进行监控

 触发器创建语法

 

创建前置触发器，在执行insert操作时，自动修改创建时间和更新时间
create or replace trigger tri_public_memo
before 
   insert
   on public_memo
     for each row
  begin 
    select sysdate into :NEW.created_date from dual;
    select sysdate into :NEW.updated_date from dual;
  end;

　　

创建前置触发器，在update操作时，只修改更新时间
create or replace trigger tri_public_memo_up
before 
   update
   on public_memo
     for each row
  begin 
    select sysdate into :NEW.updated_date from dual;
  end;

　　

insert into public_memo(ids,title,contents,address,longitude,latitude,status)
values (sys_guid(),'4','23','浦东大道003',12.1234,13.2345,'4');

update public_memo ta set ta.title='5',ta.address='浦东大道004'
where ta.ids='E10A6C4EF00641B6B971627BEC823A2B'

　　

 

 

查询public_memo表下的所有触发器
select trigger_name from all_triggers where table_name='PUBLIC_MEMO'; 

　　

删除触发器

DROP TRIGGER trigger_name;

建表语句

create table public_memo(
   ids varchar2(32) not null,
   title varchar2(255) not null,
   contents clob not null,
   address varchar(255) not null,
   longitude number(13,10) not null,
   latitude number(13,10) not null,
   created_date date,
   updated_date date,
   status varchar2(4) not null
)
comment on table   public_memo is '备忘录';
  
comment on column public_memo.ids is '主键id';
  
comment on column public_memo.title is '标题';
  
comment on column public_memo.contents is '内容';
  
comment on column public_memo.address is '地址';
  
comment on column public_memo.longitude is '经度';
  
comment on column public_memo.latitudeis '纬度';
  
comment on column public_memo.created_date is '创建时间';
  
comment on column public_memo.updated_date is '修改时间';
  
comment on column public_memo.status is '状态';

insert into public_memo(ids,title,contents,address,longitude,latitude，created_date,updated_date,status)
values (sys_guid(),'1','23','浦东大道',12.1234,13.2345,sysdate,sysdate,'1');
  
insert into public_memo(ids,title,contents,address,longitude,latitude，created_date,updated_date,status)
values (sys_guid(),'2','24','浦东大道001',12.1234,13.2345,sysdate,sysdate,'2');
  
  
insert into public_memo(ids,title,contents,address,longitude,latitude，created_date,updated_date,status)
values (sys_guid(),'3','25','浦东大道002',12.1234,13.2345,sysdate,sysdate,'3');