Can you create the sequence and then just run a test on it outside the
trigger. In other words, just try this:
SELECT uniqueNum_s.nextval FROM dual;
Does this work?
Here's an example of a trigger that I use for updates/inserts. On insert, it
"auto-increments" the id field. On update, it checks to make sure someone
isn't trying to change the primary key and raises an error if they do.
-- Start of DDL script for TRG_CFS_BREAKFASTDATAID
-- Generated 21-May-04 7:31:40 am
-- from t817a-FLPWEB:1
-- Trigger TRG_CFS_BREAKFASTDATAID
CREATE OR REPLACE TRIGGER flpweb.trg_cfs_breakfastdataid
BEFORE INSERT OR UPDATE
ON flpweb.cfs_breakfastdata
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
iCounter cfs_breakfastdata.breakfastdataid%TYPE;
cannot_change_counter EXCEPTION;
BEGIN
IF INSERTING THEN
Select SEQ_cfs_breakfastdataid.NEXTVAL INTO iCounter FROM Dual;
:new.breakfastdataid := iCounter;
END IF;
IF UPDATING THEN
IF NOT (:new.breakfastdataid = :old.breakfastdataid) THEN
RAISE cannot_change_counter;
END IF;
END IF;
EXCEPTION
WHEN cannot_change_counter THEN
raise_application_error(-20000, 'Cannot Change Counter Value');
END;
/
-- End of DDL script for TRG_CFS_BREAKFASTDATAID
----- Original Message -----
From: "daniel kessler"
> well I still can't get it to work. I am trying to auto-increment the "id"
field in the table "hhp_calendar", using:
>
> I get an error stating that dpch: uniqueNum_s is invalid and can't be
initialized.
> Any thoughts getting this to work or can anyone recommend a more
appropriate forum?
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
