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

    Unanswered: Trigger in SQL Server

    Hi,

    Can any tell me how to translate a a trigger from oracle to sql server. For example i have an Oracle trigger which is FOR EACH ROW...

    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

    How shall I translate this in SQL SERVER i.e. Old and New. and how i implement a For Each Row trigger in SQL Server
    mr_roomi

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You didn't like the answer the first time?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

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

    SQL Trigger

    Hi Brett!

    Last time I translated like this,

    If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

    set @var_db_contract =(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )

    But you told me that it is not right way to do so I am still on the same point where I was. what is right way to play with :OLD and :NEW values of Oracle in SQL SERVER

    thank you
    mr_roomi

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's because the trigger will work in sets, not for each row...

    If you do 12 inserts, all at once, the inserted table will have 12 rows...

    DOING SELECT @x=x from inserted will only give you the last value of the set.

    Understand?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

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

    Final Solution

    Hi Brett,
    Thank you very much for your prompt reply.

    Then what is the final solution for this problem. Can you use my example to translate that that I meant the code I used above, you will use that code in your example? I'll really appreciate your cooperation.
    mr_roomi

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well you're assigning a value to @var_db_contract for a reason.

    What is it?

    Probably an Insert to anthoer table or an update.

    You'll need to collapse the sql statements in to 1 statement.

    Or use a cursor, which I don't recommend for a trigger
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

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

    All Oracle Trigger

    Hi Brett,

    Here is all Oracle Trigger and It is row level trigger I just copied it from TOAD: If you want to give me any example please try to use my code eample: Thanks again.

    TRIGGER "ADENG".PROP_AMT
    AFTER
    INSERT OR DELETE OR UPDATE OF AMT_PAID_ITEM, AMT_RET_ITEM, QTD_ITEM
    ON CQE_ITEM
    FOR EACH ROW
    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

Posting Permissions

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