Table lock for insert triggers?
We have a trigger that ensures number of rows should not exceed a certain count. The trigger is as follows:
CREATE OR REPLACE TRIGGER test AFTER INSERT ON offcparm
DECLARE
dbcount INTEGER;
BEGIN
SELECT COUNT(*) INTO dbcount FROM offcparm;
IF dbcount > 1 THEN
raise_application_error(-20000, 'Only 1 row supported');
END IF;
END;
/
Now, this works fine, except for cases where I do it through 2 sql sessions.
eg, open 2 sqlplus sessions
In session 1 do: insert into offcparm values ('1', '1');
In session 2 do: insert into offcparm values ('2', '2');
issue commit in session 1
issue commit in session 2
How can this be fixed? Please help!