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

    Unanswered: 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_item
    mr_roomi

  2. #2
    Join Date
    Jan 2004
    Posts
    49
    strange logic....

    on delete
    inserted table will be empty

    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

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

Posting Permissions

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