Posted on 2007-07-14 22:35
leekiang 閱讀(333)
評(píng)論(0) 編輯 收藏 所屬分類:
oracle
create or replace trigger auth_secure
before insert or update or delete
on auths //對(duì)整表更新前觸發(fā)
begin
if(to_char(sysdate,'DY')='SUN'
RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表auths');
end if;
end
RAISE_APPLICATION_ERROR的錯(cuò)誤代碼在-20000到-20999之間，這樣就不會(huì)與 ORACLE 的任何錯(cuò)誤代碼發(fā)生沖突
禁止ddl操作
create or replace trigger ddl_deny
before create or alter or drop or truncate on database//系統(tǒng)級(jí)別的觸發(fā)器
declare
v_errmsg varchar2(100):= 'You have no permission to this operation';
begin
if ora_sysevent = 'CREATE' then
raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || v_errmsg);
elsif ora_sysevent = 'ALTER' then
raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || v_errmsg);
elsif ora_sysevent = 'DROP' then
raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || v_errmsg);
elsif ora_sysevent = 'TRUNCATE' then
raise_application_error(-20001, ora_dict_obj_owner || '.' || ora_dict_obj_name || ' ' || v_errmsg);
end if;
exception
when no_data_found then
null;
end;
又一例:
create or replace trigger his_trig_u
after update ON MYDATA
FOR EACH ROW
declare
temp_count number;
BEGIN
select count(*)
into temp_count
from DATAHIS t
where t.num = :new.num
and t.time = :new.time;
if temp_count = 0 then
insert into DATAHIS
(uid, num, value, time)
values
(data_sql.nextval,
:new.num,
:new.value,
:new.time);
end if;
END;
begin前面出現(xiàn)的new，old不加冒號(hào)，begin和end之間出現(xiàn)的new和old都要在前面加上":"
用觸發(fā)器完成動(dòng)態(tài)數(shù)據(jù)的操作
在涉及如何實(shí)現(xiàn)動(dòng)態(tài)庫存的問題時(shí)，可用觸發(fā)器解決。倉庫有驗(yàn)收、出庫、調(diào)撥、報(bào)廢、退料、讓售等這些數(shù)據(jù)必須與以前的庫存相加減，才能完成動(dòng)態(tài)庫存操作。本文僅以驗(yàn)收單觸發(fā)器為例，其它的結(jié)構(gòu)雷同。它們涉及到兩個(gè)基表：bi_ysd(驗(yàn)收單)，Bj_kcb(當(dāng)前庫存表)，前者的表結(jié)構(gòu)（rq(日期)，ysdh(驗(yàn)收單號(hào))， bjbm(備件編碼)，yssl(驗(yàn)收數(shù)量)，ysdj(驗(yàn)收單價(jià))），后者的表結(jié)構(gòu)為（bjbm（備件編碼）,dqkcl（當(dāng)前庫存量）,dqkcje（當(dāng)前庫存金額））觸發(fā)器如下：
create or replace trigger trig_ysd
after insert or update or delete on bj_ysd
for each row
declare rq1 varchar2(8);rq2 varchar2(8);
/*限于篇幅，yssl1,yssl2,ysdj1,ysdj2,bjbm1,bjbm2,ii聲明略*/
if inserting or updating then
rq1:=:new.rq;bjbm1:=:new.bjbm;yssl1:=:new.yssl;
ysdj1:=:new.ysdj;
select count(*) into ii from bj_dqkcb
where bjbm=bjbm1;
if ii=0 then
insert into bj_dqkcb(bjbm,dqkcl,dqkcje)
value(bjbm1,yssl1,ysdj1);
else
update bj_dqkcb
set dqkcl=dqkcl+yssl1;
dqkcje=dqkcje+yssl1*ysdj1;
end if
end if
if deleting or updating then
rq2:=:old.rq;
bjbm2:=:old.bjbm;
yssl2:=:old.yssl;
ysdj2:=:old.ysdj;
update bj_dqkcb
set dqkcb=dqkcl-yssl2;
dqkcje=dqkcje-yssl2*ysdj2
end if;
end ;