Click here to Skip to main content
14,877,543 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have two table one table having the transaction details and other table have the closing balance of all ledger accounts.when i am trying to delete any transaction the update does not effect.

here is my trigger.Here bcdet is the transaction table and accmast is main table.

SQL
ALTER trigger [dbo].[tr_ledgerdelete] on [dbo].[BCDet] 
after delete 
as 
begin 
declare @dlcode int; 
declare @ddc nvarchar(2); 
declare @dAmt float;
SET NOCOUNT ON;

set @ddc=(select dc from deleted)
set @damt=(select amt from deleted)
if (@ddc='C')
begin
    update accmast set totcr=totcr-@damt where Lcode in (select Lcode from deleted)
    update accmast set clbal=totcr-totdr+opbal where Lcode in (select Lcode from deleted)
end

else

begin
    update accmast set totdr=totdr-@damt where  Lcode in (select Lcode from deleted)
    update accmast set clbal=totcr-totdr+opbal where Lcode in (select Lcode from deleted)
end


[edit]Code block added[/edit]
Posted
Updated 15-Feb-14 2:10am
v3

SQL
ALTER trigger [dbo].[tr_ledgerdelete]
on [dbo].[BCDet]
after delete
as
begin
    SET NOCOUNT ON;

    update t
    set totcr = t.totcr - d.amt
    from accmast t
    inner join deleted d on t.lcode = d.lcode
    where d.dc='C'

    update t
    set totdr = t.totdr - d.amt
    from accmast t
    inner join deleted d on t.lcode = d.lcode
    where d.dc != 'C'

    update t
    set clbal = t.totcr - t.totdr + t.opbal
    from accmast t
    inner join deleted d on t.lcode = d.lcode
   
SQL
ALTER trigger [dbo].[tr_ledgerdelete] on [dbo].[BCDet]
after delete
as
begin
declare @dlcode int;
declare @ddc nvarchar(2);
declare @dAmt float;
SET NOCOUNT ON;

set @ddc=(select dc from deleted)
set @damt=(select amt from deleted)
if (@ddc='C')
begin
    begin
        update accmast set totcr=totcr-@damt where Lcode in (select Lcode from deleted)
    end
    begin
        update accmast set clbal=totcr-totdr+opbal where Lcode in (select Lcode from deleted)
    end
end

else

begin
    begin
        update accmast set totdr=totdr-@damt where  Lcode in (select Lcode from deleted)
    end
    begin
        update accmast set clbal=totcr-totdr+opbal where Lcode in (select Lcode from deleted)
    end
end
   
Comments
Sandip Paul 491984 15-Feb-14 14:18pm
   
I have tried this but problem has not solve.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900