problem in trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

problem in trigger

Author

Topic

ameya_amu
Starting Member

25 Posts

Posted - 2008-11-11 : 02:08:38

i am inserting following code in trigger

if not exists(select TranID from PLMainTransaction where TranID=@TID)
insert into PLMainTransaction (PLTID,PLSubHeadID,Amount,Status,BranchID,TranID,Drcr) Values (@PLTID,1,@amt,'A',1,@TID,'Dr')
insert into PLMainTransaction (PLTID,PLSubHeadID,Amount,Status,BranchID,TranID,DrCr) Values (@PLTID,1,@amt,'A',1,@TID,'Cr')
else
Select @TotAmt=isnull(amount,0) from PLMainTransaction where TranID=@TID
Update PLMainTransaction set Amount= @TotAmt+ @amt where TranID=@TID

it's showing me incorrect syntex near keyword else

is this proper line
Update PLMainTransaction set Amount= @TotAmt+ @amt where TranID=@TID

please help me out

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 02:13:25

[code]if not exists(select TranID from PLMainTransaction where TranID=@TID)
begin
insert into PLMainTransaction (PLTID,PLSubHeadID,Amount,Status,BranchID,TranID,Drcr) Values (@PLTID,1,@amt,'A',1,@TID,'Dr')
insert into PLMainTransaction (PLTID,PLSubHeadID,Amount,Status,BranchID,TranID,DrCr) Values (@PLTID,1,@amt,'A',1,@TID,'Cr')
end
else
begin
Select @TotAmt=isnull(amount,0) from PLMainTransaction where TranID=@TID
Update PLMainTransaction set Amount= @TotAmt+ @amt where TranID=@TID
end[/code]

also how do you get values for all the above variables?
It seems like its best to do above in a procedure rather than a a trigger.

ameya_amu
Starting Member

25 Posts

Posted - 2008-11-11 : 06:42:50

thanks visakh 16

can any one help me out further, i have written following code in trigger, can you please suggest which part of the code must be in storeproceture and which part must be in trigger so that execution can be faster, i also need how to call procedure with and without parameter within trigger

CREATE TRIGGER insertinpurchase_received_details
ON dbo.INV_Purchase_Received_Details
for insert
AS
DECLARE
@PDID integer ,
@Model_ID integer,
@MD_ID integer,
@BranchID integer,
@RID integer,
@SRID integer,
@PLTID integer,
@TID integer,
@TotAmt decimal,
@amt decimal,
@FAmt decimal,
@Tdate datetime,
@MID integer,
@MName nvarchar(50)

select @PDID = inserted.PDID, @Model_ID = inserted.Model_ID, @BranchID= inserted.BranchID,@RID =inserted.RID,@SRID=inserted.SRID,@TID=inserted.PrID,@amt=inserted.total_amt from inserted
update pd set pd.Quantity_Recieved = pd.Quantity_Recieved + i.Quantity_Received
from Inserted i INNER Join INV_Purchase_Detail pd on pd.Model_ID = i.Model_ID WHERE (pd.PDid = @PDID) AND (pd.Model_ID = @Model_ID) AND (pd.BranchID=@BranchID)

if not exists(select * from INV_Stock_Detail where model_id =@model_id and BranchID=@BranchID and RID=@RID and SRID=@SRID) insert into INV_Stock_Detail (model_id,Quantity_In_Stock,BranchID,RID,SRID) values (@model_id,0,@BranchID,@RID,@SRID)
update stk set stk.Quantity_In_Stock = stk.Quantity_In_Stock + i.Quantity_Received from Inserted i INNER Join INV_Stock_Detail stk on stk.Model_ID = i.Model_ID WHERE (stk.Model_ID = @Model_ID) AND (stk.BranchID = @BranchID) AND (Stk.RID=@RID) AND (Stk.SRID =@SRID)

SELECT @Tdate= dbo.INV_Purchase_Received.Recieved_Date, @MName=dbo.INV_Manufacturer_Master.Manufacturer_Name FROM dbo.INV_Purchase_Received INNER JOIN dbo.INV_Manufacturer_Master ON dbo.INV_Purchase_Received.ManufacturerID = dbo.INV_Manufacturer_Master.Manufacturer_ID WHERE (dbo.INV_Purchase_Received.PRID = @TID)

select @MID=PLSubheadID from PLSubHead where PlSubhead_Name=@MName

if not exists(select TranID from PLTransaction where TranID=@TID)
begin
insert into PLTransaction (FY,TDate,Descp,BranchId,TranID) values ('2008-2009',@Tdate,'Being Goods Purchased',1,@TID)
Select @PLTID= ISNULL(nmbr,0) from (select MAX(isnull(PLTID,0)) nmbr from dbo.PLTransaction) DERIVEDTBL
end

if not exists(select TranID from PLMainTransaction where TranID=@TID)
begin
insert into PLMainTransaction (PLTID,PLSubHeadID,Amount,Status,BranchID,TranID,Drcr,FY) Values (@PLTID,1,@amt,'A',1,@TID,'Dr','2008-2009')
insert into PLMainTransaction (PLTID,PLSubHeadID,Amount,Status,BranchID,TranID,DrCr,FY) Values (@PLTID,@MID,@amt,'A',1,@TID,'Cr','2008-2009')
end
else
begin
Select @TotAmt=isnull(amount,0) from PLMainTransaction where TranID=@TID
Update PLMainTransaction set Amount= @TotAmt+ @amt where TranID=@TID
end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 23:44:36

there is a problem i see with trigger code
1.you've used first select statement to get values from inserted onto variables based on two variables @Model_ID & @Branch_ID but i cant see where you set values for these variables
2.Also keep in mind that even if you have values for @Model_ID & @Branch_ID if there are more than one record in inserted satiafying these you cant store the individual values onto variables. you should be trying to store them in a table variable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources