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
1) Sequences
I would keep you sequence simply as:
CREATE SEQUENCE MyTable_seq START WITH NNN
Its very rare that you need "nocache"
2) Trigger
Same deal, you can code that whole thing as:
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
and you're done. You still need to the same privileges (create trigger, create sequence)
Note that in the current version of Oracle (12) you don't need triggers or sequence at all, you can just use:
create table T ( mycol NUMBER GENERATED ALWAYS AS IDENTITY)
and we'll take care of it for you.
Typically it's a good thing to only have just those privs you need, but in a development environment it's is typical to have (for example):
CREATE TABLE
CREATE TYPE
CREATE CLUSTER
CREATE TRIGGER
CREATE PROCEDURE
CREATE SEQUENCE
CREATE VIEW