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

    Question Unanswered: rewriting Oracle trigger to MSSQL

    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 MSSQL

    Thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Providing that your table has a unique record identifier field, the following will do the trick:

    create trigger PLN_TEST on TEST for INSERT as
    update t set syscode = newid()
    from inserted i
    inner join TEST t
    on i.record_id = t.record_id
    where t.syscode is null

  3. #3
    Join Date
    Jul 2003
    Posts
    8
    The problem is that i can get my id in oracle in a AUTONOMOUS_TRANSACTION, i think that this can not be done in MSSQL
    Is this true.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The entire UPDATE in proposed trigger is autonomous. Of course, it should be followed by this code to meet the basic programming standards:

    if @@error != 0 begin
    raiserror ('Update failed!', 15, 1)
    rollback transaction
    end

Posting Permissions

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