June 3, 2013 at 10:54 am
INSERT INTO ACC_JVDTL_TRN ( FINYRTRN_ID, VRDT, LEDGER_ID, AMOUNT)
VALUES ((select i.FINYRTRN_ID from inserted i), (select i.BILLDATE from inserted i),
(select i.BILL_LEDG_ID from inserted i), (select i.BILLAMOUNT from inserted i))
The above portion is a part of a trigger. All are working fine except the AMOUNT which always insert 0.00 instead of the actual amount. Please help.
Thanks in advance
PRASAD SJ
June 3, 2013 at 11:08 am
Try this:
INSERT INTO ACC_JVDTL_TRN ( FINYRTRN_ID, VRDT, LEDGER_ID, AMOUNT)
select
i.FINYRTRN_ID,
i.BILLDATE,
i.BILL_LEDG_ID,
i.BILLAMOUNT
from
inserted i
June 3, 2013 at 11:11 am
sj_prasad (6/3/2013)
INSERT INTO ACC_JVDTL_TRN ( FINYRTRN_ID, VRDT, LEDGER_ID, AMOUNT)VALUES ((select i.FINYRTRN_ID from inserted i), (select i.BILLDATE from inserted i),
(select i.BILL_LEDG_ID from inserted i), (select i.BILLAMOUNT from inserted i))
The above portion is a part of a trigger. All are working fine except the AMOUNT which always insert 0.00 instead of the actual amount. Please help.
Thanks in advance
PRASAD SJ
You said this is part of a trigger. Please post the DDL for the entire trigger, it will provide more insight into what is going on with the trigger.
June 3, 2013 at 5:28 pm
USE [FAS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[BILL_TRN_AFTINS_TRIGGER]
ON [dbo].[BILL_TRN]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
IF (select i.BILL_TYPE from inserted i) = 'PURCHASE' AND (select i.BILLFLAG from inserted i) = 'BILL'
BEGIN
INSERT INTO ACC_JVDTL_TRN (VRTRN_ID, FINYRTRN_ID, PROJECT_ID, VRTYP_ID, VRSLNO,
VRDT, LEDGER_ID, VRDRAMT, VRCRAMT)
VALUES (1, (select i.FINYRTRN_ID from inserted i), (select i.PROJECT_ID from inserted i),
2009000019, 1, (select i.BILLDATE from inserted i), (select i.BILL_LEDG_ID from inserted i),
0, (select i.BILLAMOUNT from inserted i))
END
END
-- ------------------- here all all working fine except BILLAMOUNT which inserted only 0.00 even if BILLAMOUNT have value. please note that VRCRAMT and BILLAMOUNT have decimal(12,2)
PRASAD SJ
June 3, 2013 at 5:39 pm
This trigger assumes that only one record will be inserted into the table [dbo].[BILL_TRN] at a time. Is this true?
June 4, 2013 at 4:10 am
Yes, only one record will be inserted into the table [dbo].[BILL_TRN] at a time.
PRASAD SJ
June 4, 2013 at 4:17 am
sj_prasad (6/4/2013)
Yes, only one record will be inserted into the table [dbo].[BILL_TRN] at a time.PRASAD SJ
It will still be better to create the code which can handle multiple row inserts like below
USE [FAS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[BILL_TRN_AFTINS_TRIGGER]
ON [dbo].[BILL_TRN]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERTACC_JVDTL_TRN ( VRTRN_ID, FINYRTRN_ID, PROJECT_ID, VRTYP_ID, VRSLNO, VRDT, LEDGER_ID, VRDRAMT, VRCRAMT )
SELECT1, i.FINYRTRN_ID, i.PROJECT_ID, 2009000019, 1, i.BILLDATE, i.BILL_LEDG_ID, 0, i.BILLAMOUNT
FROMinserted AS i
WHEREi.BILL_TYPE = 'PURCHASE' AND i.BILLFLAG = 'BILL'
END
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 4, 2013 at 4:49 am
My problem is VRCRAMT is not properly updating with BILLAMOUNT.
ie VRCRAMT = 0 even if BILLAMOUNT > 0
please help
PRASAD SJ
June 4, 2013 at 5:03 am
sj_prasad (6/4/2013)
My problem is VRCRAMT is not properly updating with BILLAMOUNT.ie VRCRAMT = 0 even if BILLAMOUNT > 0
please help
PRASAD SJ
You will have to do some de-bugging yourself in that case, I don't see any visible issues with the code.
You can do the following steps:
1. Comment out the INSERT part in the trigger and include onyl a "SELECT * FROM inserted" in the trigger code
2. Check the value of BILLAMOUNT returned by the SELECT when you insert a row in the table.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply