we need to create tables on demand through a script. these tables need unique id, for which we created trigger (like auto_increment). this trigger works only in SQL*plus but the same code is not accepted from php3.
enviroment : php3/oci8/oracle 8.1.6
error :
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/25 PLS-00103: Encountered the symbol "" when expecting one of the
following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> cursor
form current
trigger :
CREATE OR REPLACE TRIGGER trg_test
BEFORE INSERT ON tbl_test
FOR EACH ROW
DECLARE LastMax NUMBER;
Rows NUMBER;
BEGIN
IF INSERTING THEN
LastMax := 0;
SELECT count(*) INTO Rows FROM tbl_test;
IF Rows > 0 THEN SELECT MAX(ID) INTO LastMax FROM tbl_test;
END IF;
:NEW.ID := LastMax + 1;
END IF;
EXCEPTION WHEN OTHERS THEN RAISE;
END;
enviroment : php3/oci8/oracle 8.1.6
error :
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/25 PLS-00103: Encountered the symbol "" when expecting one of the
following:
begin function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> cursor
form current
trigger :
CREATE OR REPLACE TRIGGER trg_test
BEFORE INSERT ON tbl_test
FOR EACH ROW
DECLARE LastMax NUMBER;
Rows NUMBER;
BEGIN
IF INSERTING THEN
LastMax := 0;
SELECT count(*) INTO Rows FROM tbl_test;
IF Rows > 0 THEN SELECT MAX(ID) INTO LastMax FROM tbl_test;
END IF;
:NEW.ID := LastMax + 1;
END IF;
EXCEPTION WHEN OTHERS THEN RAISE;
END;