Re: using a sequence inside an insert trigger (reply, long)

From: Andrew McAllister <mcallister_at_grad.missouri.edu>
Date: 1995/04/18
Message-ID: <mcallister.72.00125107_at_grad.missouri.edu>

In article <3n19qh$6uf_at_ixnews1.ix.netcom.com> eaglesCo_at_ix.netcom.com (Anna Horton) writes:
>From: eaglesCo_at_ix.netcom.com (Anna Horton)
>Subject: using a sequence inside an insert trigger
>Date: 18 Apr 1995 21:09:37 GMT
 

>OK, I'm at the end of my rope. I am using access as the front end ( and
> no I can't change the project is too far along ). I am trying to mimic
>the action of the automatic counter field using SEQUENCE and an insert
>trigger on the table. PL/SQL does not appear to allow the use of a
>sequence in the body of the PL/SQL. Is a stored procedure and some hack
>on the access side of the equation the only way to do this?

No, you can use a sequence in an insert trigger. I have two methods.
Method 1: Pre-Fetch the sequence value from Oracle by executing a pass through query. Put the sequence value into its proper space on the Access form then save the record. This actually speeds up record insertion from Access's point of view. (If you don't prefetch, the insert slows down because: Access sends the record off to be inserted then has to search the entire table looking for the record it just inserted. The only thing Access has to work with to re-find the record is the fields you filled in on the form. If two records in the table are identical in every way except primary key, then access gives you a runtime error! Cool Huh?)

Method2: Set a before insert trigger in Oracle to get the nextval in the sequence before the record is inserted.

Code for Method 1 has two parts.

Access Part of code for method 1.

'*** Function assumes that there is a pass through query pre defined and it's 
'*** name is pass_through_query. Also assumes that a sequence
'*** exists on the server named awardsum_seq

Function get_awardsum_nextval ()
Dim MyDB As Database
Dim Myset As Recordset
Dim CRLF As String
Dim I As Integer
Dim MyQuery As QueryDef

   CRLF = Chr(13) & Chr(10)    

   On Error GoTo Get_AwardSum_NextVal_Error

   Set MyDB = DBEngine.Workspaces(0).Databases(0)    

   '*** Get Pointer to Pass_Through_Query    For I = 0 To MyDB.QueryDefs.Count - 1

        If MyDB.QueryDefs(I).Name = "Pass_Through_Query" Then
            Set MyQuery = MyDB.QueryDefs(I)
            Exit For
        End If

   Next I

   '*** Make sure Access is expecting a return value from query    MyQuery.ReturnsRecords = True
   '*** Set the query sql statement to get the nextval from a sequence    MyQuery.Sql = "Select gm.awardsum_seq.nextval FROM dual"    '*** Open a record set based on the pass_through_query    Set Myset = MyDB.OpenRecordset("Pass_Through_Query", DB_OPEN_SNAPSHOT)    '*** should only return one value. Add error checking here as well    get_awardsum_nextval = Myset!Nextval

Exit Function

Get_AwardSum_NextVal_Error:

   WarningMsg ("Could not get Unique Key While Inserting" & CRLF & Str(Err) & " " & Error)    get_awardsum_nextval = Null
End Function

Oracle Server Part of Code Method 1:
CREATE OR REPLACE TRIGGER AwardSum_insert BEFORE
INSERT
ON AwardSum
FOR EACH ROW
DECLARE
seq_temp NUMBER;
BEGIN
/* check to see if client already has a key value if so then */ /* dont get a new one */
IF :new.awardsumkey IS NULL THEN

     select AwardSum_seq.nextval into seq_temp from dual;
     :new.awardSumkey := seq_temp;

END IF;
END; The above trigger assumes a primary key named awardsumkey is type number. And that a sequence named awardsum_seq exists.

Method 2:
Doesn't use the access basic code above, but does use the trigger above.

The cool thing about all of this is that you can pre-fetch a sequence for the primary key when you need one for improving form speed. Or you can just let oracle and access hash it out together.

This code Copyright 1995 by the Curators of the University of Missouri. All rights reserved. Permission granted to use this code without documenting its source if used by a NOT FOR PROFIT entity. For profit users must include the above copyright to document the code's source.

Andy

Andrew McAllister -- mcallister_at_grad.missouri.edu Office of Research, University of Missouri-Columbia The views above are my own, and NOT those of my employer. Received on Tue Apr 18 1995 - 00:00:00 CEST