As I could not find this book in any bookstore, I had to order it semi-blind from Amazon. And I was not disappointed in the number or depth of topics covered. The style is similar to that of an old time reference manual. Think, the man page for ksh. But, I can live with that. I have learned a lot from reading the man page for ksh. And bash. And sh. But not csh. I have to draw the line somewhere. But, I digress.
Things were going fine until I came to the part where the author stated that all Oracle professionals must understand how to use triggers to enforce complex R.I. Since Tom Kyte's writings had convinced me that this was probably not going to happen, I took special notice.
First, a bit about his example. He had two tables:
MEMBER
Name Null? Type
----------------------- ------- -----
MEMBER_ID NOT NULL NUMBER
MEMBER_TYPE NOT NULL NUMBER
ACCOUNT_NUMBER NOT NULL VARCHAR2(10)
CREDIT_CARD_NUMBER NOT NULL VARCHAR2(19)
CREDIT_CARD_TYPE NOT NULL NUMBER
CREATED_BY NOT NULL NUMBER
CREATION_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER
LAST_UPDATE_DATE NOT NULL DATE
and CONTACT:
Name Null? Type
----------------------- ------- -----
CONTACT_ID NOT NULL NUMBER
MEMBER_ID NOT NULL NUMBER
CONTACT_TYPE NOT NULL NUMBER
LAST_NAME NOT NULL VARCHAR2(20)
FIRST_NAME NOT NULL VARCHAR2(20)
MIDDLE_INITIAL VARCHAR2(1)
CREATED_BY NOT NULL NUMBER
CREATION_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER
LAST_UPDATE_DATE NOT NULL DATE
Where contact_id is the primary key of the CONTACT table, member_id is the primary key of the MEMBER table; contact.member_id refers back to member.member_id, and the constraint to be implemented is that there can be one or two contacts for a given member in the CONTACT table, but never more than two. And he offers up this trigger as a way to enforce this business rule:
CREATE OR REPLACE TRIGGER contact_t1
BEFORE INSERT ON contact
FOR EACH ROW
DECLARE
CURSOR c ( member_id_in NUMBER ) IS
SELECT null
FROM contact c
, member m
WHERE c.member_id = m.member_id
AND c.member_id = member_id_in
HAVING COUNT(*) > 1;
BEGIN
FOR i IN c (:new.member_id) LOOP
RAISE_APPLICATION_ERROR(-20001,'Already two signers.');
END LOOP;
END;
/
There are a few interesting points about the code. First of all, I was left wondering why this did not cause a mutating table error. A few minutes of searching Ask Tom revealed that a single row insert does not raise the error; Oracle is able to deduce the complete state of the table in the midst of the change.
The other interesting thing is the design of the trigger. All it does is try to open a cursor and fetch a row. If it is able to do so, then there already are two rows in the table, and the triggering statement is attempting to insert a third. So it doesn't bother to do anything with the row it implicitly fetched; if it can open the cursor an error has occurred.
So, to test things out, we add a new member, ID=1004, to the member table, and then start adding contacts to the contact table:
SQL> insert into contact values
2 (1010, 1004, 1003, 'Dodah', 'Day', ' ', 2,sysdate, 2, sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into contact values
2 (1011, 1004, 1003, 'Dodah', 'Day', ' ', 2,sysdate, 2, sysdate);
1 row created.
SQL> insert into contact values
2 (1012, 1004, 1003, 'Dodah', 'Day', ' ', 2,sysdate, 2, sysdate);
insert into contact values
*
ERROR at line 1:
ORA-20001: Already two signers.
ORA-06512: at "SCOTT.CONTACT_T1", line 11
ORA-04088: error during execution of trigger 'SCOTT.CONTACT_T1'
SQL>
which shows that the trigger works fine in a single user situation. So, we rollback our 2nd insert and repeat it:
And we start up a 2nd session and try
SQL> insert into contact values
2 (1011, 1004, 1003, 'Dodah', 'Day', ' ', 2,sysdate, 2, sysdate);
1 row created.
And we start up a 3rd session and try
SQL> insert into contact values
2 (1012, 1004, 1003, 'Dodah', 'Day', ' ', 2,sysdate, 2, sysdate);
1 row created.
And we then commit in sessions 2 and 1. When we are all done, we have not three, but four signers for this member.
SQL> insert into contact values
2 (1013, 1004, 1003, 'Dodah', 'Day', ' ', 2,sysdate, 2, sysdate);
1 row created.
SQL> Commit;
Commit complete.
SQL>
The problem, of course is that when sessions 2 and 3 tried to insert their row, the trigger was unable to "see" the uncommitted row in session 1. Therefore, the trigger quite properly allowed us to continue with the insert.
Now the question is, what is the best way (if any) to enforce this business rule? One thing we could try is to add a materialized view:
create materialized view member_ck
build immediate
refresh on commit
enable query rewrite
as
select c.member_id, count(*) kount
from contact c, member m
where c.member_id = m.member_id
group by c.member_id
/
alter table member_ck add constraint ck_kount check (kount <= 2);
If we do this, we find that we can repeat our opening situation, however, as soon as a commit would leave three or more members in the table we get
SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (SCOTT.CK_KOUNT) violated
Which will work, but is a little awkward, as the constraint is enforced at the transaction level rather than the statement level. Another approach is to add a LOCK TABLE statement to the trigger:
CREATE OR REPLACE TRIGGER contact_t1
BEFORE INSERT ON contact
FOR EACH ROW
DECLARE
CURSOR c ( member_id_in NUMBER ) IS
SELECT null
FROM contact c
, member m
WHERE c.member_id = m.member_id
AND c.member_id = member_id_in
HAVING COUNT(*) > 1;
BEGIN
LOCK TABLE contact IN EXCLUSIVE MODE;
FOR i IN c (:new.member_id) LOOP
RAISE_APPLICATION_ERROR(-20001,'Already two signers.');
END LOOP;
END;
/
This accomplishes all the R.I. requirements, at the expense of destroying concurrency. When a second session attempts to insert (or update, for that matter) a row, it hangs until the first session has committed or rolled back.
To return to the beginning of this post, it really is important to know how to enforce complex R.I. And I don't have any more answers than the next scribe when it comes to doing so. But I can spot a proposed answer which does not work.
