Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    8

    Question Unanswered: rewritng oracle trigger to Sybase

    Hello,

    I have this trigger :
    CREATE OR REPLACE TRIGGER PLN_TEST
    BEFORE INSERT
    ON
    TEST
    REFERENCING
    New AS MyNew
    Old AS MyOld
    FOR EACH ROW
    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_new_syscode TEST.SYSCODE%TYPE;
    BEGIN
    if :MyNew.Syscode is null then

    PLN_GETNEWID('TEST',v_new_syscode);
    commit;
    :MyNew.SYSCODE :=v_new_syscode;
    end if;
    END
    ;
    /

    How can I rewrite it to Sybase

    Thanks

  2. #2
    Join Date
    Dec 2002
    Posts
    104
    Hello,

    just FYI unlike oracle sybase has 3 types of triggers which are insert,delete and update.

    -Pooja

  3. #3
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: rewritng oracle trigger to Sybase

    Originally posted by tompoes
    Hello,

    I have this trigger :
    CREATE OR REPLACE TRIGGER PLN_TEST
    BEFORE INSERT
    ON
    TEST
    REFERENCING
    New AS MyNew
    Old AS MyOld
    FOR EACH ROW
    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_new_syscode TEST.SYSCODE%TYPE;
    BEGIN
    if :MyNew.Syscode is null then

    PLN_GETNEWID('TEST',v_new_syscode);
    commit;
    :MyNew.SYSCODE :=v_new_syscode;
    end if;
    END
    ;
    /

    Sybase triggers are called after the data has been inserted, so you wil need to rollback your transaction before exiting the trigger.

    I suppose "New" and "Old" refer to the versions of the rows before and after the insert. Sybase refers to these as "inserted" and "deleted". Sybase triggers are called once per statement and work on the whole transaction. So if something is inserted that the trigger determines is an error, rollback transaction will undo the whole transaction not just the row in question.

    Looking at the syntax presented here the Oracle version of the trigger is called once per row inserted, to acheive the same you will need to loop round each of the rows in the inserted table - Not recommended. Consider writing the trigger so that it can detect problems within the set of data affected by the statement rather than on a per row basis.

    Richard.

  4. #4
    Join Date
    Jul 2003
    Posts
    8

    Question

    Can you tell me if this is true.

    If i insert a record in sybase in a table with a primeryKey column ID and one other column name (String)

    I want to generate the id by my self. so i only insert the name.

    This is not possible because the trigger works after the insert and the id can not be null.

    Is this correct.

  5. #5
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159
    Originally posted by tompoes
    Can you tell me if this is true.

    If i insert a record in sybase in a table with a primeryKey column ID and one other column name (String)

    I want to generate the id by my self. so i only insert the name.

    This is not possible because the trigger works after the insert and the id can not be null.

    Is this correct.
    It is possible to do this, but not as part of the trigger functionality.

    The best solution is to have a "key fountain" table with the next key number for a given table, or part of the database and make updating this "key fountain" part of the transaction.

    declare
    @error int,
    @rowcount int

    < generate your string here >

    begin tran

    update key_fountain set
    next_key = next_key + 1
    where
    key_name = 'NAME_OF_KEY'

    select
    @error = @@error,
    @rowcount = @@rowcount

    if @error != 0 and @rowcount != 1
    begin
    rollback transaction
    return 1
    end

    insert into table_name ( col1, col2, etc ) ....

    select
    @error = @@error,
    @rowcount = @@rowcount

    if @error != 0 and @rowcount != (number of rows inserted)
    begin
    rollback transaction
    return 2
    end

    commit transaction


    This way the key fountain will be consistent with the database. If the key fountain is given a datarows locking scheme, contention should be minimal.

Posting Permissions

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