I do not have DBA status so, I have to ask for privileges when needed in developing an Oracle Back End with MS ACCESS 2010 front end.

I now have CREATE TRIGGER privilege. I realize I have to ask for the Create Sequence privilege as well.

To allow the user to edit tables from MS access, the oracle table must have a primary Key. This is called ID.
I understand that I need to CREATE SEQUENCE to populate the ID column with the nextValue in the Sequence.
(Sorry I don't have access to LiveSQL (yet, very restrictive here). So I will have to put code in this note.)

Below is my intended code.

4 Questions:
1. What other privileges might I need to Insert the ID NextVal in a newly added Row?
2. What is "dual" (below in Trigger Code)?
3. Is the Exception Clause necessary with this simple trigger?
4. Since, I have 12 tables that need a trigger, would I create 12 unique Sequences and 12 unique Triggers.


Here is my code (so far)
Any guidance would be appreciated.

Joseph Giallombardo

Step 1
CREATE SEQUENCE MyTable_seq
START WITH NNN (some value TBD)
INCREMENT BY 1
NOCACHE
NOCYCLE;


Step 2
CREATE OR REPLACE TRIGGER TriggerName_1_of_12
BEFORE INSERT
ON schema.MyTable
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
tmpVar NUMBER;

BEGIN
tmpVar := 0;
SELECT SeqICreate.NEXTVAL INTO tmpVar FROM dual; (What is dual?)
:NEW.MyTableIDField := tmpVar;

EXCEPTION (do I need this exception)
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END TriggerName_1_of_12

CREATE OR REPLACE TRIGGER TriggerName_1_of_12
BEFORE INSERT
ON schema.MyTable 
FOR EACH ROW
BEGIN
  :NEW.MyTableIDField := SeqICreate.NEXTVAL ;
END TriggerName_1_of_12