August 13, 2008 at 1:04 am
hey folks
would someone run their eyes over this code and advise where i've gone wrong. its giving me a syntax error "Incorrect syntax near '@intFrequency'. "
====
CREATE TRIGGER trgMaint1
ON dbo.Equipment
AFTER Update
AS
BEGIN
SET NOCOUNT ON;
Declare @intEquipID int
Declare @strMaintDescription varchar(50)
Declare @intRecurr int
Declare @i int
Declare @intFrequency int
Declare @dteMaintStartDate datetime
Declare @MaintFlag numeric(18,0)
Select @intEquipID = (Select EquipID From Inserted)
Select @MaintFlag = (Select MaintFlag From Inserted)
Select @intFrequency = (Select Frequency From Inserted)
Select @intRecurr = (Select NoOfRecurrances From Inserted)
Select @dteMaintStartDate = (Select MaintStartDate From Inserted)
Select @strMaintDescription = (Select MaintDescription From Inserted)
Set @i = 0
If @MaintFlag =0
Begin
While (@i < @intRecurr)
Begin
@intFrequency = (@intFrequency * @i)
@dteMaintStartDate = DateAdd(Day, @intFrequency, @dteMaintStartDate)
Insert Into MaintenanceDetails (ScheduleDate, MaintDescription) VALUES (@dteMaintStartDate, @strMaintDescription)
If @i = (@intRecurr -1)
Begin
Update dbo.Equipment Set dbo.Equipment.MaintFlag=1 Where dbo.Equipment.EquipID=@intEquipID
End
End
END
GO
====
cheers
Jerry
August 13, 2008 at 1:59 am
When you assign a value to a variable you need to use either a SET or SELECT command. So you should have something like:
SET @intFrequency = (@intFrequency * @i)
instead of
@intFrequency = (@intFrequency * @i)
Regards,
Andras
August 13, 2008 at 2:02 am
Another thing that you may want to handle (although it does not result in an a syntax error), is multiple updates. When more than a single row is affected, your trigger will consider only one (even that one may not be correct if the order in which the inserted/deleted are scanned is not the same)
Regards,
Andras
August 13, 2008 at 2:42 am
hi andras
thanks for your tips.
how do i handle multiple updates row by row? i'm not a trained programmer so excuse my ignorance.
regards
jerry
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply