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