Sunday, February 19, 2012

For Deletion..trigger Is Not Working

Hi,
I have this trigger, it is working fine when i add new data but it doesn't work when I delete data from the table?

Any idea?

Any help will be highly appreciated.

CREATE TRIGGER [PROP_AMT] ON [dbo].[cqe_item]
FOR INSERT, UPDATE, DELETE
AS
DECLARE
@.var_DB_contract INTEGER,
@.var_CQE INTEGER,
@.var_PC INTEGER,
@.var_item VARCHAR(7),
@.var_AMT_PAID INTEGER,
@.var_AMT_RET INTEGER,
@.var_ITEM_NEW VARCHAR(1),
@.var_quant DECIMAL,
@.var_fiyr INTEGER,
@.var_amt_result INTEGER,
@.var_amt_ret_result INTEGER,
@.var_amt_old INTEGER,
@.var_amt_ret_old INTEGER,
@.var_quant_result INTEGER,
@.var_quant_new INTEGER,
@.var_quant_old INTEGER,
@.Item_new VARCHAR(7),
@.var_chk varchar(1)

--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 )
IF @.var_db_contract IS NOT NULL
BEGIN
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 )
SET @.var_cqe=(SELECT a.cqe_numb 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 )
SET @.var_pc=(SELECT a.pc_code 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 )
SET @.var_item=(SELECT a.item_no 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 )
SET @.var_fiyr=(SELECT a.fy_item 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 )
set @.var_chk ="Y"
END
ELSE
BEGIN
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 )
SET @.var_cqe=(SELECT a.cqe_numb 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 )
SET @.var_pc=(SELECT a.pc_code 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 )
SET @.var_item=(SELECT a.item_no 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 )
SET @.var_fiyr=(SELECT b.fy_item 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 )
set @.var_chk="N"
END
SET @.var_amt_paid=(SELECT a.amt_paid_item 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 )
SET @.var_amt_old=(SELECT b.amt_paid_item 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 )
SET @.var_amt_result =ISNULL(@.var_amt_paid,0) - ISNULL(@.var_amt_old,0)

SET @.var_amt_ret = (SELECT a.amt_ret_item 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)
SET @.var_amt_ret_old=(SELECT b.amt_ret_item 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)
SET @.var_amt_ret_result = isnull(@.var_amt_ret,0) - isnull(@.var_amt_ret_old,0)

SET @.var_quant_new = (SELECT a.quantity 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)
SET @.var_quant_old =(SELECT b.quantity 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)
SET @.var_quant_result = isnull(@.var_quant_new,0) - isnull(@.var_quant_old,0)
SELECT @.item_new = new_item
FROM VALID_ITEM
WHERE DB_CONTRACT = @.var_db_contract
AND PC_CODE = @.var_PC
AND ITEM_NO = @.var_ITEM

UPDATE ae_contract
set amt_paid_contr = isnull(amt_paid_contr,0) +@.var_amt_result,
amt_ret_contr = isnull(amt_ret_contr,0) + @.var_amt_ret_result
where db_contract = @.var_db_contract

IF @.item_new = 'N'
BEGIN
update vendor
set used_amt = isnull(used_amt,0) + @.var_amt_result + @.var_amt_ret_result
where db_vendor = (select gen_contr from ae_contract
where ae_contract.db_contract=@.var_db_contract);
END
UPDATE enc_det
set amt_paid_fy = isnull(amt_paid_fy,0) + @.var_amt_result,
amt_ret_fy = isnull(amt_ret_fy,0) + @.var_amt_ret_result
where db_contract = @.var_db_contract
and pc_code = @.var_pc
and fy = @.var_fiyr

UPDATE valid_item
set tamt_ret_item = isnull(tamt_ret_item,0) + @.var_amt_ret_result,
tamt_paid_item = isnull(tamt_paid_item,0) + @.var_amt_result,
qtd = isnull(qtd,0) + @.var_quant_result
where db_contract = @.var_db_contract
and pc_code = @.var_pc
and item_no = @.var_itemstrange logic...

on delete
inserted table will be empty

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

and what will You do if updated/deleted more then one records?
sorry for my English

No comments:

Post a Comment