| Re: Trigger not seeing uncommitted values [message #1280] |
Tue, 16 April 2002 13:30 |
Jack Haster
Messages: 4
Registered: April 2002
|
Junior Member |
|
|
The code is at the end of this reply. The trigger will work fine *if* the calling application commits after every insert. The trigger will not see any new values until after a commit therefore if the calling application inserts, say 10 twice in the same transaction, the trigger will fail to stop the second insert.
I am curious as to a solution since this seems problematic with any trigger called by a multi-DML transaction... it will not be able to perform logic on rows not yet committed.
CREATE OR REPLACE TRIGGER mytrigger
BEFORE INSERT OR UPDATE
ON mytable
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
num_rows NUMBER(10);
value_exists EXCEPTION;
PRAGMA EXCEPTION_INIT( value_exists, -20001);
BEGIN
SELECT count(columnc) INTO num_rows FROM mytable
WHERE columnc=:new.columnc;
IF num_rows > 0 THEN
RAISE value_exists;
END IF;
EXCEPTION
WHEN value_exists THEN
RAISE_APPLICATION_ERROR( -20001, 'already exists');
END;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
