先上个小例子(触发器的功能是:保证table_name表中 mobile字段不为空时是不能重复的 )
表结构
create table User_Info (
ID NUMBER(10) not null,
MOBILE VARCHAR2(20),
ORTHER VARCHAR2(20),
constraint PK_User_Info primary key (ID)
);
触发器
CREATE OR REPLACE TRIGGER table_mobile_unique
BEFORE INSERT OR UPDATE OF mobile ON table_name
FOR EACH ROW
WHEN ( NEW.mobile IS NOT null AND (OLD.mobile is NULL OR NEW.mobile <> OLD.mobile))
DECLARE
om_count NUMBER ;
BEGIN
select count (id)
INTO om_count
from table_name
where mobile = :NEW.mobile and id <> :NEW.id;
IF om_count >= 1 THEN
raise_application_error(- 20000 , ' 手机号码已注册 ' );
END IF ;
END ;
在oracle中怎样查找trigger:
select * from all_triggers
where table_name =upper('User_Info ')
在oracle中怎样删除trigger:
ALTER TRIGGER 名称 DISABLE;
drop trigger 名称;
更多的学习到oracle官网