觸發器
是特定事件出現的時候
功能
開始
create trigger biufer_employees_department_id
before insert or update
of department_id
on employees
referencing old as old_value
new as new_value
for each row
when (new_value
:mission_pct :=
觸發器的組成部分
create trigger biufer_employees_department_id
命名習慣
biufer(before insert update for each row)
employees 表名
department_id 列名
表或視圖上的DML語句
數據庫關閉或啟動
before insert or update
of department_id
on employees
referencing old as old_value
new as new_value
for each row
說明
when (new_value
限制不是必須的
其中的new_value是代表更新之後的值
是觸發器的主體
:mission_pct :=
end;
主體很簡單
insert into employees(employee_id
last_name
values(
select commission_pct from employees where employee_id=
觸發器不會通知用戶
觸發器類型
是在表上或者某些情況下的視圖上執行的特定語句或者語句組上的觸發器
例子
需要對在表上進行DML操作的用戶進行安全檢查
Create table foo(a number);
Create trigger biud_foo
Before insert or update or delete
On foo
If user not in (
Raise_application_error(
End if;
即使SYS
[試驗]
對修改表的時間
create table employees_copy as select *from hr
create table employees_log(
who varchar
when date);
Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Begin
Insert into employees_log(
Who
Values( user
End;
/
update employees_copy set salary= salary*
select *from employess_log;
即是INSERT/UPDATE/DELETE中的哪一個觸發了觸發器?
可以在觸發器中使用INSERTING / UPDATING / DELETING 條件謂詞
if inserting then
elsif updating then
elsif deleting then
end if;
if updating(
end if;
[試驗]
alter table employees_log
add (action varchar
Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Declare
L_action employees_log
Begin
if inserting then
l_action:=
elsif updating then
l_action:=
elsif deleting then
l_action:=
else
raise_application_error(
Insert into employees_log(
Who
Values( user
End;
/
insert into employees_copy( employee_id
values(
select *from employees_log
From:http://tw.wingwit.com/Article/program/Oracle/201311/17764.html