see
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:42170695313022#42220587905650 <code>
*any* constraint can be disabled.
*any* constraint can be violated.
*any* one.
(correctly coded, mulit-user) Triggers are as "safe" as any constraint to protect data.
If you are worried about triggers being disabled, you must use methods to prevent that from happening.
If you are worried about constraints being disabled, you must use the same methods to prevent that from happening.
Neither is harder to get around than the other.
But your trigger suffers from the fact that in a multi-user environment, it quite simply will "not work".
I insert A and B
You insert A and B
the triggers fire in our respective sessions, but you cannot see me, and I cannot see you.
Therefore, neither of us believes the other has overlapping data.
And data integrity is gone.
Here is an excerpt from my 2nd edition of Expert One on One that I'm working on that describes the multi-user issue and shows what technique *I* would use (a stored procedure, no modifications directly to the table itself, don't GRANT. The logic could be placed into a trigger if you want, but I wouldn't)
<quote>
One of the side-effects of Oracle's 'non-blocking' approach is that if you actually want to ensure that no more than one user has access to a row at once, then you, the developer, need to do a little work yourself. Consider the following example. A developer was demonstrating to me a resource-scheduling program (for conference rooms, projectors, etc.) that he had just developed and was in the process of deploying. The application implemented a business rule to prevent the allocation of a resource to more than one person, for any given period of time. That is, the application contained code that specifically checked that no other user had previously allocated the time slot (as least the developer thought it did). This code queried the schedules table and, if no rows existed that overlapped that time slot, inserted the new row. So, the developer was basically concerned with two tables:
create table resources ( resource_name varchar2(25) primary key, ... );
create table schedules
( resource_name references resources,
start_time date not null,
end_time date not null,
check (start_time < end_time ),
primary key(resource_name,start_time)
);
And, before making, say, a room reservation, the application would query:
select count(*)
from schedules
where resource_name = :room_name
and (start_time <= :new_end_time)
AND (end_time >= :new_start_time)
It looked simple and bullet-proof (to the developer anyway); if the count came back zero, the room was yours. If it came back non-zero, you could not reserve it for that period. Once I knew what his logic was, I set up a very simple test to show him the error that would occur when the application went live. An error that would be incredibly hard to track down and diagnose after the fact - one would be convinced it must be a database bug.
All I did was get someone else to use the terminal next to him. They both navigated to the same screen and, on the count of three, each hit the Go button and tried to reserve the same room for about the same time - one from 3pm to 4pm and the other from 3:30pm to 4:00pm. Both people got the reservation - the logic, which worked perfectly in isolation, failed in a multi-user environment. The problem in this case was caused in part by Oracle's non-blocking reads. Neither session ever blocked the other session. Both sessions simply ran the above query and then performed the logic to schedule the room. They could both run the query to look for a reservation, even if the other session had already started to modify the schedules table (the change wouldn't be visible to the other session until commit, by which time it was too late). Since they were never attempting to modify the same row in the schedules table, they would never block each other and, thus, the business rule could not enforce what it was intended to enforce.
The developer needed a method of enforcing the business rule in a multi-user environment, a way to ensure that exactly one person at a time made a reservation on a given resource. In this case, the solution was to impose a little serialization of his own. What we will do is lock the parent row in the RESOURCES table prior to making a modification in the SCHEDULES table. That way, all modifications to the SCHEDULES table for a given RESOURCE_NAME value will be done one at a time. That is, to reserve a block of time for the resource named "X", we will lock the single row in the RESOURCES table for "X" and then modify the SCHEDULES table. So, in addition to performing the count(*) above, the developer must first:
select * from resources where resource_name = :room_name FOR UPDATE;
What we did here was to lock the resource (the room) to be scheduled immediately before scheduling it, in other words before we query the Schedules table for that resource. By locking the resource we are trying to schedule, we have ensured that no one else is modifying the schedule for this resource simultaneously. They must wait until we commit our transaction - at which point, they would be able to see our schedule. The chance of overlapping schedules is removed. The developer must understand that, in the multi-user environment, they must at times employ techniques similar to those used in multi-threaded programming. The FOR UPDATE clause is working like a semaphore in this case. It serializes access to the resources tables for that particular row - ensuring no two people can schedule it simultaneously. My suggestion was to make this logic a "transaction API", that is, bundle all of the logic into a stored procedure and only permit applications to modify the data via this API, the code could look like this:
create or replace procedure schedule_resource
( p_resource_name in varchar2,
p_start_time in date,
p_end_time in date
)
as
l_resource_name resources.resource_name%type;
l_cnt number;
begin
We start by locking the single row in the resource table for the resource you want to schedule. If anyone else has this row locked, we will block and wait for it:
select resource_name into l_resource_name
from resources
where resource_name = p_resource_name
FOR UPDATE;
Now, we are the only ones inserting into this schedules table FOR THIS resource name, it is safe to look at this table now:
select count(*)
into l_cnt
from schedules
where resource_name = p_resource_name
and (start_time <= p_end_time)
and (end_time >= p_start_time);
if ( l_cnt <> 0 )
then
raise_application_error
(-20001, 'Room is already booked!' );
end if;
Now if we get here in the code without raising an error, we can safely insert rows for our resource into the schedule table without any overlaps:
insert into schedules
( resource_name, start_time, end_time )
values
( p_resource_name, p_start_time, p_end_time );
end schedule_resources;
This is still highly concurrent as there are potentially thousands of resources to be reserved - what we have done is ensure that only one person modifies a resource at any time. This is a rare case where the manual locking of data you are not going to actually update is called for. You need to be able to recognize where you need to do this and, perhaps as importantly, where not to (I have an example of when not to below). Additionally, this does not lock the resource from other people reading the data as it might in other databases, hence this will scale very well.
Issues such as the above have massive implications when attempting to port an application from database to database (I return to this theme a little later in the chapter), and this trips people up time and time again. For example, if you are experienced in other databases, where writers block readers and vice versa then you may have grown reliant on that fact to protect you from data integrity issues. The lack of concurrency is one way to protect yourself from this - that is how it works in many non-Oracle databases. In Oracle, concurrency rules supreme and you must be aware that, as a result, things will happen differently (or suffer the consequences).
</quote>
Now, if you have "no master table", you'll need to use the LOCK TABLE command in order to serialize inserts/modifications into this table.