触发器的学习
可以在五种地方建立触发器:
1、Data Manipulation Language (DML) statements
2、Data Definition Language (DDL) statements
3、Database events
(Database event triggers fire whenever the database starts up or is shut down, whenever a user logs on or off, and whenever an Oracle error occurs)
4、INSTEAD OF
INSTEAD OF triggers control operations on views, not tables
INSTEAD OF triggers are essentially alternatives to DML triggers. They fire when inserts, updates, and deletes are about to occur; your code specifies what to do in place of these DML operations
5、Suspended statements
Statements experiencing space problems (lack of tablespace or quota) can enter a suspended mode until the space problem is fixed.
一个简单触发器的定义:
create or replace trigger insert_update_test_table
before insert or update on test_table
referencing OLD AS the_old_delegate NEW AS the_new_delegate
for each row
begin
IF UPDATING ('name')THEN
raise_application_error(-20003,:the_old_delegate.name);
END IF;
if INSERTING then --DELETING\UPDATING\INSERTING
raise_application_error(-20001,'正在执行插入操作!');
end if;
if :the_new_delegate.name ='zxy' then
RAISE_APPLICATION_ERROR (
-20000,
'错误!');
end if;
end insert_update_test_table;
:new.name表示被插入的字段的代表,可以把它当作变量。
referencing OLD AS the_old_delegate NEW AS the_new_delegate 这句话的意思,就是:在程序中,old用the_old_delegate代替,NEW 用the_new_delegate代替
DELETING\UPDATING\INSERTING可以当作对象使用,表示当用户删除、更新(更新某个字段)、插入数据的行为,true是在做这个事情。