scott@ORCL> create or replace trigger send_email
2 after insert on emp
3 for each row
4 DECLARE
5 c utl_smtp.connection;
6 PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
7 BEGIN
8 utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
9 END;
10 begin
11 c := utl_smtp.open_connection('smtp-server.acme.com');
12 utl_smtp.helo(c, 'foo.com');
13 utl_smtp.mail(c, 'sender@foo.com');
14 utl_smtp.rcpt(c, 'recipient@foo.com');
15 utl_smtp.open_data(c);
16 send_header('From', '"Sender"
17 send_header('To', '"Recipient"
18 send_header('Subject', 'Newly Employee with Employee id'||:new.empno||' Joined Organization ');
19 utl_smtp.write_data(c, utl_tcp.CRLF || 'Newly Employee with Employee id'||:new.empno||' Joined Organization ');
20 utl_smtp.close_data(c);
21 utl_smtp.quit(c);
22 EXCEPTION
23 WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
24 BEGIN
25 utl_smtp.quit(c);
26 EXCEPTION
27 WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
28 NULL; -- When the SMTP server is down or unavailable, we don't
29 -- have a connection to the server. The quit call will
30 -- raise an exception that we can ignore.
31 END;
32 raise_application_error(-20000,
33 'Failed to send mail due to the following error: ' || sqlerrm);
34 end;
35 /
Trigger created.
Obviously, its syntactically correct- it compiles. And if you insert any record the trigger will flawlessly. But it is very wrong, the implementation has a huge mistake.What heppens if you insert 100 records into Emp table and then decided to Rollback. the SMTP Protocol wont praticipate in distributed Transaction with Oracle database.so sending the email will not rollback. You will have 100 emails send that 100 new employees joined the organization that never really happened. This is perhaps the most frequent errors made in use of triggers - Performing an operation that cannot be rolled back.
The first rule of trigger coding is, Do not perform any operation that cannot be rolled back. Consider what happens if your trigger fires—the code executes—but the transaction rolls back
The next implementation problem with triggers stems from the fact that developers don't understand all the nuisance of Concurrency controls and Isolation levels. One of the greatest oracle feature is that reads don't block writes and writes don't block reads. However that single feature when not fully understood by the developer turns into a liability when the developer turns to triggers. especially triggers that enforce some "rule"
Suppose we have a table like below that includes currency combination along with primary currency.
create table currency
(
country varchar2(5),
currency varchar2(5),
primary_ind varchar2(1)
);
insert into currency values ('US','USD','Y');
insert into currency values ('US','USN','N');
insert into currency values ('US','USS','N');
commit;
We need to enforce the rule that at most one currency can be primary for a given country. we have a before update trigger on that table for each row to check whether the country has primary currency.
scott@ORCL> create or replace trigger currency_trig
2 before update on currency
3 for each row
4 declare
5 pragma autonomous_transaction;
6 l_count number;
7 begin
8 select count(*)
9 into l_count
10 from currency
11 where primary_ind ='Y'
12 and country = :new.country;
13
14 if l_count > 1 then
15 raise_application_error (-20458,'More than one primary currency ');
16 end if;
17 end;
18 /
Trigger created.
Now, there are many things wrong with this trigger. But the first obvious clue that something seriously wrong with the trigger is Autonomous Transaction. without it an update would produce error.
scott@ORCL> update currency set primary_ind = 'Y' where country='US' and currency='USN';
update currency set primary_ind = 'Y' where country='US' and currency='USN'
*
ERROR at line 1:
ORA-04091: table SCOTT.CURRENCY is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.CURRENCY_TRIG", line 4
ORA-04088: error during execution of trigger 'SCOTT.CURRENCY_TRIG'
That is not really an error but more of a warning. Basically it is saying, “You are doing something so fundamentally wrong in your trigger that Oracle Database is just not going to permit you to do that.”
Autonomous Transaction That “feature” permits developers to query the table the trigger is firing on yet query it as if it were in another session/transaction altogether. The trigger will not see its own modifications to the table, and that is the huge flaw with this thinking: the trigger is attempting to validate the very modifications it cannot see. The trigger’s sole purpose is to validate the modifications to the data, but the trigger is reading the data before the modifications take place. It therefore cannot work!
scott@ORCL> select * from currency;
COUNT CURRE P
----- ----- -
US USD Y
US USN N
US USS N
scott@ORCL> update currency set primary_ind = 'Y' where country='US' and currency='USN';
1 row updated.
scott@ORCL> select * from currency;
COUNT CURRE P
----- ----- -
US USD Y
US USN Y
US USS N
Now we end up with two primary currency and that is the flaw with using triggers enforcing Entity Integrity.
Correct Answers :
The correct answer is to fix the data model, A table with primary currency and a table with other currency.
scott@ORCL> create table primary_currency
2 (
3 country varchar2(5),
4 currency varchar2(5),
5 constraint primary_currency_pk primary key(country)
6 );
Table created.
scott@ORCL> create table other_currency
2 (
3 country varchar2(5),
4 currency varchar2(5),
5 constraint other_currency_pk primary key(country,currency)
6 );
Table created.
We are done, now it is impossible to have two primary currency for a country and the primary key enforces that. Now at most one currency can be a primary currency and a country should have a primary currency. Again a simple foreign key constraint can do that.
scott@ORCL> alter table other_currency
2 add constraint must_have_primary_currency
3 foreign key (country) references primary_currency(country);
Table altered.
Now we are probably done with the rules needed for this. But the real world problem is that a country having a primary currency should not be allowed in other currency. That is if USD is primary currency in PRIMARY_CURRENCY then USD cannot appear in OTHER_CURRENCY table. This is like a "Anti-Foreign key" a feature that doesn't exists. we can implement as a database rule. basically we need to make sure that if we join PRIMARY_CURRENCY and OTHER_CURRENCY for each country and currency there always be a zero records in result sets. That can be achieved like below.
scott@ORCL> create materialized view log on PRIMARY_CURRENCY with rowid;
Materialized view log created.
scott@ORCL> create materialized view log on OTHER_CURRENCY with rowid;
Materialized view log created.
scott@ORCL> create materialized view currency_mv
2 refresh fast on commit
3 as
4 select p.rowid as p_rid, o.rowid as o_rid
5 from PRIMARY_CURRENCY p,
6 OTHER_CURRENCY o
7 where p.country = o.country
8 and p.currency = o.currency;
Materialized view created.
scott@ORCL> alter table currency_mv add constraint currency_mv_chk check (p_rid is null and o_rid is null);
Table altered.
So now we have a materialized view that will refresh on COMMIT and ensure that no data can be joined between the two tables. This materialized view will always be empty. Again, it is scalable (the only opportunity for any serialization would be at COMMIT time) and correct. The database is enforcing this constraint for us.
scott@ORCL> select * from primary_currency;
COUNT CURRE
----- -----
US USD
scott@ORCL> select * from other_currency;
COUNT CURRE
----- -----
US USN
US USS
scott@ORCL> insert into other_currency values ('US','USD');
1 row created.
scott@ORCL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.CURRENCY_MV_CHK) violated
Triggers should be viewed with a skeptic’s eye. If the trigger is in place to enforce entity integrity, be very suspicious of it. Think about multiuser conditions. Think about what happens when two or three people operate on similar data at, or at about, the same time. Run all the combinations in your head or on the whiteboard. Play with your schema, using multiple sessions. See what happens when you have concurrent access.
No comments:
Post a Comment