Last post Sep 30, 2016 06:15 PM by nilsan
Sep 27, 2016 04:19 AM|j2ee|LINK
If I try the sql statement with normal query, it works fine like this:
UPDATE TraineeMonthlyShopItemListRecord SET Data01 = 1 WHERE TraineeID = '553' and ShopItemListID='17' and RecordID IN (select a.recordid from TraineeMonthlyHourRecord a join MonthlyHourRecord b on a.RecordID = b.RecordID where b.Month ='201609' and
a.TraineeID ='553' GROUP BY a.RecordID HAVING COUNT(*) = 1)
However it doesn't work in Trigger. I can successfully save the Trigger but when I test it with manually insert a record in the table, keep having the error: Incorrect syntax near 'MonthlyHourRecord'
Here is my trigger code, please have a look and see what could be wrongs, thanks!
/****** Object: Trigger [dbo].[t2] Script Date: 09/27/2016 11:50:06 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
ALTER TRIGGER [dbo].[t2]
declare @TraineeID varchar(10)
declare @MM varchar(10)
declare @YYYY varchar(10)
declare @ShopItemListID varchar(20)
declare @RecordID varchar(10)
declare @YYYYMM varchar(10)
declare @RecordTime datetime
declare @DD varchar(10) declare @test varchar(10)
declare @DataDD varchar(10)
declare @Quantity varchar(10)
declare @SQL nvarchar(200)
SELECT @ShopItemListID = ShopItemListID from inserted
SELECT @RecordTime = RecordTime from inserted
SELECT @Quantity = Quantity from inserted
SET @MM = substring(CONVERT(varchar,@RecordTime, 112),5,2)
SET @YYYY = substring(CONVERT(varchar,@RecordTime, 112),1,4)
SET @DD = substring(CONVERT(varchar,@RecordTime, 112),7,2)
SELECT @TraineeID = TraineeID from inserted
SET @YYYYMM = @YYYY+@MM
SET @DataDD = 'Data'+@DD
set @SQL = 'UPDATE TraineeMonthlyShopItemListRecord SET '+@DataDD+'='+@Quantity+' WHERE TraineeID ='''+@TraineeID+''' and ShopItemListID='''+@ShopItemListID+''' and RecordID IN (SELECT DISTINCT a.RecordID from TraineeMonthlyHourRecord a JOIN MonthlyHourRecord b on a.RecordID = b.RecordID WHERE b.Month ='''+@YYYYMM+''' and a.TraineeID ='''+@TraineeID+''' GROUP BY a.RecordID HAVING COUNT(*) = 1 )'
exec sp_executesql @SQL
Sep 29, 2016 03:27 AM|Chris Zhao|LINK
You could comment out
--exec sp_executesql @SQL
check the output, whether the sql query is valid.
Sep 30, 2016 02:59 PM|PatriceSc|LINK
declare @SQL nvarchar(200) might be a bit short (and so the SQL statement would be cut too early resulting in random errors depending where it is cut).
Not directly related but if you insert multiple rows (INSERT SELECT) your trigger. inserted, deleted, updated are pseudo tables which might include multiple rows and it might be best to author your trigger with that in mind.
Finally you are forced to create a dynanmic string because you have a part of a column name which is actually a data. I would likely try to avoid that for a better normalised table even if I have to pivot my data as needed depending on what I want to do.
Sep 30, 2016 06:15 PM|nilsan|LINK
I agree with PatriceSc
Increate the length of @SQL and use PRINT before executing it to make sure that it doesn't get cut.
Please share your views after trying these things.