10g vs 9i or 8i Trigger Question

From: <reidt2_at_yahoo.com>
Date: 13 Jun 2005 11:34:39 -0700
Message-ID: <1118687678.961226.188500@g14g2000cwa.googlegroups.com>

I performed the following under Oracle 10g:

CREATE TABLE TESTER

( COL1                   VARCHAR2(8)    NOT NULL,
  COL2                   VARCHAR2(8)    NULL)

CREATE UNIQUE INDEX PK_TESTER ON TESTER
(COL1) INSERT INTO TESTER (COL1) VALUES('1'); INSERT INTO TESTER (COL1) VALUES('2'); INSERT INTO TESTER (COL1) VALUES('3'); UPDATE TESTER SET COL2 = 'B' WHERE COL1 = '2'; SELECT * FROM TESTER COMMIT; CREATE OR REPLACE TRIGGER TESTER_BEFORE_DELETE BEFORE delete ON TESTER
FOR EACH ROW
 WHEN (old.COL2 like 'B%')
DECLARE interface_error EXCEPTION;
begin
RAISE interface_error;
exception

	when interface_error then
	RAISE_APPLICATION_ERROR (-20001,'Deletion not allowed-already

interfaced!');
end;
/

DELETE FROM TESTER WHERE COL1 = '2' Row was deleted without exception message. This triggered worked properly under 9i and 8i.

I replaced it with the below trigger and I get the expected exception.

CREATE OR REPLACE TRIGGER TESTER_BEFORE_DELETE BEFORE delete or update ON TESTER
FOR EACH ROW
DECLARE interface_error EXCEPTION;
begin

	IF (:old.COL2 like 'B%')
	   THEN RAISE interface_error;
	END IF;

exception
when interface_error then
RAISE_APPLICATION_ERROR (-20001,'Update / Delete not allowed-record already interfaced!');
end;
/

DELETE FROM TESTER WHERE COL1 = '2' error message returned.

Did something change between 8i/9i and 10g with respect to the "WHEN" clause?

Thanks for your help!
Tim Reid Received on Mon Jun 13 2005 - 13:34:39 CDT