我以前做的一個(gè)例子,你參考下吧
樂(lè)平ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場(chǎng)景,ssl證書未來(lái)市場(chǎng)廣闊!成為創(chuàng)新互聯(lián)的ssl證書銷售渠道,可以享受市場(chǎng)價(jià)格4-6折優(yōu)惠!如果有意向歡迎電話聯(lián)系或者加微信:13518219792(備注:SSL證書合作)期待與您的合作!
第八章 觸發(fā)器(Trigger)
--格式:
create or replace trigger 名稱
[after|before][delete|update|insert]
[on 表 | on schema]
[referencing new as 別名 old as 別名]
[for each row]
declare
....
begin
...
exception
....
end;
--創(chuàng)建一個(gè)delete類型行級(jí)觸發(fā)器
--刪除的一行數(shù)據(jù)保存在:old
create or replace trigger emp_delete_row_trigger
after delete
on emp
referencing new as n old as o
for each row
begin
dbms_output.put_line('emp_delete_row_trigger called.');
dbms_output.put_line('刪除員工:' || :o.empno || ' ' || :o.ename);
end;
delete from emp where empno=7499;
delete from emp;
--創(chuàng)建一個(gè)insert類型行級(jí)觸發(fā)器
--插入的一行新數(shù)據(jù)保存在:new
create or replace trigger emp_insert_row_trigger
after insert
on emp
for each row
begin
dbms_output.put_line('emp_insert_row_trigger called.');
dbms_output.put_line('添加員工:' || :new.empno || ' ' || :new.ename);
end;
insert into emp(empno,ename) values(1,'empxxx');
--創(chuàng)建一個(gè)update類型行級(jí)觸發(fā)器
--修改前的數(shù)據(jù)保存在:old
--修改后的數(shù)據(jù)保存在:new
create or replace trigger emp_update_row_trigger
after update
on emp
for each row
begin
dbms_output.put_line('emp_update_row_trigger called.');
dbms_output.put_line('修改前:' || :old.empno || ' ' || :old.ename);
dbms_output.put_line('修改后:' || :new.empno || ' ' || :new.ename);
end;
update emp
set ename='xxxx'
where empno=7499;
--語(yǔ)句級(jí)觸發(fā)器(update,delete,insert)
create or replace trigger delete_stmt_trigger
after delete
on emp
begin
dbms_output.put_line('delete_stmt_trigger called.');
end;
delete from emp;
--判斷觸發(fā)器類型
----------------------------------------------------------
--每進(jìn)行一次交易,就要調(diào)用觸發(fā)器,自動(dòng)扣除或增加賬戶金額
----------------------------------------------------------
create table account
(
customerName varchar2(30) primary key,
cardID varchar2(8),
currentMoney number
);
insert into account values('Daivd','10010001',5000);
insert into account values('Jason','10010002',3000);
create table trans
(
transDate date,
cardID varchar2(8),
transType varchar2(10),
transMoney number
);
insert into trans
values(sysdate,'10010001','取款',1000);
create or replace trigger trans_trigger
before insert
on trans
for each row
declare
v_currentMoney account.currentMoney%type;
begin
--判斷類型
if :new.transType='取款' then
--取款
select currentMoney into v_currentMoney
from account
where cardID=:new.cardID;
if v_currentMoney :new.transMoney then
raise_application_error(-20001,'余額不足');
end if;
update account
set currentMoney=currentMoney-:new.transMoney
where cardID=:new.cardID;
else
--存款
update account
set currentMoney=currentMoney+:new.transMoney
where cardID=:new.cardID;
end if;
exception
when no_data_found then
raise_application_error(-20002,'無(wú)效的帳戶');
end;
--模式(schema)級(jí)觸發(fā)器
create or replace trigger schema_trigger
before drop
on schema
begin
dbms_output.put_line('schema_trigger called');
dbms_output.put_line(ora_dict_obj_name);
dbms_output.put_line(ora_dict_obj_type);
if ora_dict_obj_name='ACCOUNT' then
raise_application_error(-20003,'ACCOUNT表不能被刪除');
end if;
end;
drop table account;
--ora_dict_obj_name 操作對(duì)象名稱
--ora_dict_obj_type 操作對(duì)象類型
--啟用觸發(fā)器
alter trigger schema_trigger enable;
--禁用觸發(fā)器
alter trigger schema_trigger disable;
創(chuàng)建兩個(gè)表:
create?table?a
(stdid?int,
stdname?varchar2(10));
create?table?b
(stdid?int,
stdname?varchar2(10));
創(chuàng)建觸發(fā)器:
CREATE?OR?REPLACE?TRIGGER?tr_insert?
after?insert
ON?a
FOR?EACH?ROW?
BEGIN
INSERT?INTO?b(stdid,stdname)
VALUES(:new.stdid,:new.stdname);
END;
驗(yàn)證,在a表中插入數(shù)據(jù):
insert?into?a?values?(1,'a');
commit;
驗(yàn)證b表結(jié)果:
一般在sqlplus或者其他第三方oracle工具中,按照語(yǔ)法及需求寫好代碼,直接執(zhí)行創(chuàng)建過(guò)程即可。
一般語(yǔ)法如下:
CREATE?[OR?REPLACE]?TRIGGER?trigger_name
{BEFORE?|?AFTER?}
{INSERT?|?DELETE?|?UPDATE?[OF?column?[,?column?…]]}
[OR?{INSERT?|?DELETE?|?UPDATE?[OF?column?[,?column?…]]}...]
ON?[schema.]table_name?|?[schema.]view_name?
[REFERENCING?{OLD?[AS]?old?|?NEW?[AS]?new|?PARENT?as?parent}]
[FOR?EACH?ROW?]
[WHEN?condition]
PL/SQL_BLOCK?|?CALL?procedure_name;
這個(gè)只能遺憾的告訴你,不能實(shí)現(xiàn)
因?yàn)橛|發(fā)器中不能運(yùn)行 ddl語(yǔ)句和commit,rollback語(yǔ)句
所謂的ddl語(yǔ)句就是用語(yǔ)定義和管理數(shù)據(jù)庫(kù)中的對(duì)象,如Create,Alter,Drop,truncate等,DDL操作是隱性提交的!
其實(shí)剛才寫了一個(gè),創(chuàng)建的時(shí)候沒(méi)問(wèn)題,但是往a里插入數(shù)據(jù)時(shí)報(bào)錯(cuò)
create or replace trigger t_add_col
after insert on a
for each row
begin
execute immediate 'alter table b add '||:new.fieldname||' varchar2(30)';
end;
錯(cuò)誤就是ora-04092,你可以看一下
網(wǎng)站欄目:oracle怎么寫觸發(fā)器 oracle的觸發(fā)器怎么寫
網(wǎng)站網(wǎng)址:http://www.muchs.cn/article18/hggigp.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供Google、網(wǎng)站制作、網(wǎng)站收錄、網(wǎng)站導(dǎo)航、ChatGPT、品牌網(wǎng)站設(shè)計(jì)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來(lái)源: 創(chuàng)新互聯(lián)