trigger design
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: trigger design

  1. #1
    Join Date
    Aug 2002
    Posts
    4

    Question

    I have a trigger that does half of what I would like it to do.
    For every record that is inserted, it gets the pidm correctly.
    Now, if a record has a pidm, I would like to set the tran_number to 1 or increment the tran_number for that pidm. With that added, the trigger won't compile. I'm getting ora-600 internal errors. Here is what I got so far...

    create or replace trigger tzgprnt_insert_pidm
    before insert on tzgprnt
    for each row

    declare
    pidm number;
    tran_number number;

    begin

    select distinct gwsntid_pidm into pidm
    from gwsntid
    where gwsntid_netid = :new.tzgprnt_netid;
    :new.tzgprnt_pidm := pidm;

    exception
    when no_data_found then
    tzgprnt_pidm := null;

    if pidm not null then
    select max(tzgprnt_tran_number) +1 into tran_number
    from tzgprnt
    where tzgprnt_pidm = pidm;
    :new.tzgprnt_tran_number := tran_number;

    exception
    when no_data_found then
    :new.tzgprnt_tran_number := 1;
    end if;

    end;

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    create or replace trigger tzgprnt_insert_pidm
    before insert on tzgprnt
    for each row
    declare
    pidm number;
    tran_number number;
    begin
    begin
    select distinct gwsntid_pidm into pidm
    from gwsntid
    where gwsntid_netid = :new.tzgprnt_netid;
    :new.tzgprnt_pidm := pidm;
    exception
    when no_data_found then
    tzgprnt_pidm := null;
    end; -- u have to close every begin block
    if pidm not null then
    begin
    select max(tzgprnt_tran_number) +1 into tran_number
    from tzgprnt
    where tzgprnt_pidm = pidm;
    :new.tzgprnt_tran_number := tran_number;
    exception
    when no_data_found then
    :new.tzgprnt_tran_number := 1;
    end;
    end if;
    end;
    /

  3. #3
    Join Date
    Aug 2002
    Posts
    4
    Shestakov--

    thanks... it still didn't work.
    i started getting:

    Warning: Trigger created with compilation errors.

    Errors for TRIGGER BANINST2.TT_TZGTEST_INSERT_PIDM:

    LINE/COL ERROR
    -------- --------------------------------------------------------------
    19/13 PLS-00103: Encountered the symbol "NULL" when expecting one of the following:
    in like between
    The symbol "like" was substituted for "NULL" tocontinue.

    so i changed "if pidm not null then" to "if pidm > 0 then"
    and started getting this error:

    CREATE OR REPLACE TRIGGER baninst2.tt_tzgtest_insert_pidm
    *
    ERROR at line 1:
    ORA-00600: internal error code, arguments: [16221], [], [], [], [], [], [], []

    No errors.

    It might be time to go a different route, but surely this can be done.

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    OK!

    create or replace trigger tzgprnt_insert_pidm
    before insert on tzgprnt
    for each row
    declare
    pidm number;
    tran_number number;
    begin
    begin
    select distinct gwsntid_pidm into pidm
    from gwsntid
    where gwsntid_netid = :new.tzgprnt_netid;
    :new.tzgprnt_pidm := pidm;
    exception
    when no_data_found then
    tzgprnt_pidm := null;
    end; -- u have to close every begin block
    if pidm IS not null then -- add IS --> this is oracle syntax.
    begin
    select max(tzgprnt_tran_number) +1 into tran_number
    from tzgprnt
    where tzgprnt_pidm = pidm;
    :new.tzgprnt_tran_number := tran_number;
    exception
    when no_data_found then
    :new.tzgprnt_tran_number := 1;
    end;
    end if;
    end;
    /

  5. #5
    Join Date
    Aug 2002
    Posts
    4
    still no luck... with that change, i still get the ORA-00600 error.

    thanks
    -doug-

  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    --
    -- second version of ur trigger (much more simple)
    --
    create or replace trigger tzgprnt_insert_pidm
    before insert on tzgprnt
    for each row
    begin
    --
    -- first select
    --
    select max(gwsntid_pidm) into :new.tzgprnt_pidm
    from gwsntid
    where gwsntid_netid = :new.tzgprnt_netid;
    --
    -- second select
    --
    select nvl(max(tzgprnt_tran_number),0) + 1
    into :new.tzgprnt_tran_number
    from tzgprnt
    where tzgprnt_pidm = pidm;
    :new.tzgprnt_tran_number := tran_number;
    end;
    /

    Why it possible :
    Function max always return 1 row (with null or not).
    U can use this in ur select.

    Example :

    create table abc (a number);

    select nvl(max(a),0) + 1 from abc;

    SQL> create table abc (a number);

    Table created.

    SQL> select * from abc;

    no rows selected

    SQL> select max(a) from abc;

    MAX(A)
    ----------

    SQL> select nvl(max(a),0) + 1 from abc;

    NVL(MAX(A),0)+1
    ---------------
    1


  7. #7
    Join Date
    Aug 2002
    Posts
    4
    thanks... still getting ORA-00600 error after putting
    in the nvl function.

    -doug-

  8. #8
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Probably u haven't problem with trigger code.
    I have come problems with tables or database instance,
    because code like this works perfectly in our applications.

Posting Permissions

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


Click Here to Expand Forum to Full Width