Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    83

    Unanswered: Triggers/Sequence Not Working from Web Form

    In Toad I created a sequence and a trigger so my rows in my oracle table will have a unique id.

    My problem is the trigger only works when I manually type data in within Toad. When I submit my web form and it inserts the data in the database, the ID is blank.

    I am also not sure why my ID field has an aqua background.

    I've used the wizard to create my triggers and instances. I'm not good at the pure SQL code...

    Thanks!

  2. #2
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    can you post the code of the trigger.

    Also, when you say web form do you mean a html webdb/portal type form or a froms generated type form

  3. #3
    Join Date
    Jan 2004
    Posts
    83
    I use ColdFusion...so it is a web form that when someone submits, the processing page has some SQL that inserts into the database.

    CREATE OR REPLACE TRIGGER rec
    BEFORE INSERT ON REC_CRP
    DECLARE
    tmpVar NUMBER;
    /************************************************** ****************************
    NAME: rec
    PURPOSE: To perform work as each row is inserted or updated.

    REVISIONS:
    Ver Date Author Description
    --------- ---------- --------------- ------------------------------------
    1.0 4/5/2004 1. Created this trigger.

    PARAMETERS:
    INPUT:
    OUTPUT:
    CALLS:
    ASSUMPTIONS:
    LIMITATIONS:
    ALGORITHM:
    NOTES:

    Here is the complete list of automatically available Auto Replace Keywords:
    Object Name: rec or rec
    Sysdate: 4/5/2004
    Date/Time: 4/5/2004 10:00:26 AM
    Date: 4/5/2004
    Time: 10:00:26 AM
    Username: (set in TOAD Options, Procedure Editor)
    Table Name: %TableName% (set in the Create New Procedure dialog)
    Trigger Options: BEFORE INSERT ON REC_CRP
    (set in the Create New Procedure dialog)
    ************************************************** ****************************/
    BEGIN
    tmpVar := 0;

    Select MySeq.NextVal into tmpVar from dual;
    :NEW.SequenceColumn := tmpVar;
    :NEW.CreatedDate := Sysdate;
    :NEW.CreatedUser := User;

    EXCEPTION
    WHEN OTHERS THEN
    -- Consider logging the error and then re-raise
    RAISE;
    END rec;




    /

  4. #4
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    I assume your table is called REC_CRP and the column is called SequenceColumn? if not thats your problem as the code of the trigger is fine

  5. #5
    Join Date
    Jan 2004
    Posts
    83
    the column is called "ID".

    If I type information in directly through Toad...ID will increment. When I'm not in Toad, ID will be blank.

  6. #6
    Join Date
    Jan 2004
    Posts
    83
    The other thing I don't understand is why is it that even though I've created a Trigger...it is not visible on any of the Trigger tabs...almost like it isn't there.

    When I create it, it asks me to save it. Is there a special place the file should be saved?

    Thanks!!!!

  7. #7
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    it sounds like the trigger is not there. Unfotunately I don't know toad but the code would need to be compiled into the database. It sounds like toad is running its own trigger external to the database and hasn't compiled a trigger into the database.

  8. #8
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    When you've typed your trigger into the prcoedure editor, compile it using the little green arrow in the upper left tool bar (or press F9).

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •