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 := :old.db_contract;
cqe := :old.cqe_numb;
pc := :old.pc_code;
item := :old.item_no;
fiyr := :old.fy_item;
end if;
amt_paid := nvl(:new.amt_paid_item,0) -nvl(:old.amt_paid_item,0);
amt_ret := nvl(:new.amt_ret_item,0) -nvl(:old.amt_ret_item,0);
quant := nvl(:new.quantity,0) -nvl(:old.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;sorry i forgot to tell..in oracle it is row level trigger...
thanks
No comments:
Post a Comment