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'

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

删除触发器

1	`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');