Trigger
常见有两种:行(Row Trigger)和语句(Statement Trigger)
还有：Instead of Trigger和Event trigger。
例子1-Row Trigger：
CREATE OR REPLACE TRIGGER client AFTERINSERT ON tt1 FOR EACH row
BEGIN
dbms_application_info.set_client_info(userenv('client_info')+1 );
END;
例子2-Statement Trigger
CREATE OR REPLACE TRIGGER client_1 AFTERINSERT ON tt1
BEGIN
dbms_application_info.set_client_info(userenv('client_info')-1 );
END;
ORA-04091错误
Tom Kyte有一篇文章很好的解释了ORA-04091。
http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html
部分摘抄如下：
Suppose wehave a table that includes country currency combinations with a primarycurrency. The following is sample data:
CountryCurrency Primary_Currency
US USD Y
US USN N
US USS N
We need toenforce the rule that at most one currency can be primary for a given country.We have a BEFORE UPDATE trigger on the above table for each row (usingautonomous transaction to avoid the mutating error) to check whether thecountry has any primary currency.
That was allI needed to read. I knew they had a serious bug on their hands when Iread—paraphrasing:
At most one currency can be primary (we have a constraint that crosses rows in the table).
We have a . . . trigger.
We are using an autonomous transaction to avoid the mutating table error.
The trigger would have looked something like this:
SQL< create or replace
2 trigger currencies_trigger
3 before update on currencies
4 for each row
5 declare
6 PRAGMA AUTONOMOUS_TRANSACTION;
7 l_cnt number;
8 begin
9 select count(*)
10 into l_cnt
11 from currencies
12 where primary_currency='Y'
13 and country = :new.country;
14 if ( l_cnt < 1 )
15 then
16 raise_application_error
17 (-20000, 'only one allowed');
18 end if;
19 end;
20 /