demo05.sql

来自「Expert Oracle Database Architecture 9i a」· SQL 代码 · 共 25 行

create table demo ( x int primary key );
create or replace trigger demo_bifer
before insert on demo
for each row
declare
    l_lock_id   number;
    resource_busy   exception;
    pragma exception_init( resource_busy, -54 );
begin
    l_lock_id :=
       dbms_utility.get_hash_value( to_char( :new.x ), 0, 1024 );
    if ( dbms_lock.request
             (  id                => l_lock_id,
                lockmode          => dbms_lock.x_mode,
                timeout           => 0,
                release_on_commit => TRUE ) <> 0 )
    then
        raise resource_busy;
    end if;
end;
/
insert into demo values ( 1 );
PROMPT in another session issue insert into demo values ( 1 );;