触发器的作用
数据确认,实施复杂的安全性检查,数据的备份和同步,对于违反规定数据库操作进行监控
触发器创建语法
创建前置触发器,在执行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');
标签:comment,sysdate,触发器,column,memo,date,简单,oracle,public From: https://www.cnblogs.com/q202105271618/p/16426480.html