Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Unanswered: Oracle Trigger...Plz Translat in SQL Server

    Hi,

    Can any , please, translate this oracle trigger in SQL Server

    Any Help will be appreciated.

    DECLARE
    DB_ID NUMBER;
    CQE NUMBER;
    PC NUMBER;
    item VARCHAR2(7);
    AMT_PAID NUMBER(12,2);
    AMT_RET NUMBER(12,2);
    ITEM_NEW VARCHAR2(1);
    quant number(12,3);
    fiyr NUMBER;
    BEGIN
    /* Find the non null values to be used for propagating changes */
    if :new.db_contract is not null then
    db_id := :new.db_contract;
    cqe := :new.cqe_numb;
    pc := :new.pc_code;
    item := :new.item_no;
    fiyr := :new.fy_item;
    else
    db_id := ld.db_contract;
    cqe := ld.cqe_numb;
    pc := ld.pc_code;
    item := ld.item_no;
    fiyr := ld.fy_item;
    end if;
    amt_paid := nvl(:new.amt_paid_item,0) -nvl(ld.amt_paid_item,0);
    amt_ret := nvl(:new.amt_ret_item,0) -nvl(ld.amt_ret_item,0);
    quant := nvl(:new.quantity,0) -nvl(ld.quantity,0);
    /* RAISE_APPLICATION_ERROR(-20501,'CHEK1 '||DB_ID||' CHEK2 '||PC
    ||' CHEK3 '||ITEM); */
    SELECT NEW_ITEM INTO ITEM_NEW
    FROM VALID_ITEM
    WHERE DB_CONTRACT = DB_ID
    AND PC_CODE = PC
    AND ITEM_NO = ITEM;
    update ae_contract
    set amt_paid_contr = nvl(amt_paid_contr,0) + amt_paid,
    amt_ret_contr = nvl(amt_ret_contr,0) + amt_ret
    where db_contract = db_id;
    if item_new = 'N' then
    update vendor
    set used_amt = nvl(used_amt,0) + amt_paid + amt_ret
    where db_vendor = (select gen_contr from ae_contract
    where ae_contract.db_contract=db_id);
    end if;
    update enc_det
    set amt_paid_fy = nvl(amt_paid_fy,0) + amt_paid,
    amt_ret_fy = nvl(amt_ret_fy,0) + amt_ret
    where db_contract = db_id
    and pc_code = pc
    and fy = fiyr;
    update valid_item
    set tamt_ret_item = nvl(tamt_ret_item,0) + amt_ret,
    tamt_paid_item = nvl(tamt_paid_item,0) + amt_paid,
    qtd = nvl(qtd,0) + quant
    where db_contract = db_id
    and pc_code = pc
    and item_no = item;
    end;
    mr_roomi

  2. #2
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Row level

    sorry i forgot to tell..in oracle it is row level trigger...

    thanks
    mr_roomi

Posting Permissions

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