August 19, 2008 at 8:31 pm
hi guys
would appreciate if someone would run their fresh eyes over this code.
i've setup a trigger on a table waiting for some specific info to be updated. the trigger doesnt execute when tested by a simple update statement (directly in sql server). For example;
===
update Equipment
set MainStartDate='2008-08-15', Frequency=90, PeriodTypeID='Days', NoOfRecurrances=8
Where EquipID=9926
===
the execution of the above code in query analyzer keeps running. i stopped it after 4mins.
if i 'Disable' the trigger the above code will execute - no problems.
my code for the trigger is below;
===
ALTER 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)
Set @intFrequency = (@intFrequency * @i)
Set @dteMaintStartDate = DateAdd(Day, @intFrequency, @dteMaintStartDate)
Insert Into MaintenanceDetails (EquipID, ScheduleDate, MaintDescription) VALUES (@intEquipID, @dteMaintStartDate, @strMaintDescription)
If @i = (@intRecurr -1)
Begin
Update dbo.Equipment Set dbo.Equipment.MaintFlag=1 Where dbo.Equipment.EquipID=@intEquipID
End
Set @i = @i + 1
End
END
===
where have i gone wrong here? hope someone can point me in the right direction.
Jerry
August 20, 2008 at 4:17 am
I think that you need one more BEGIN - just after WHILE:
While (@i < @intRecurr)
BEGIN
Set @intFrequency = (@intFrequency * @i)
Set @dteMaintStartDate = DateAdd(Day, @intFrequency, @dteMaintStartDate)
Insert Into MaintenanceDetails (EquipID, ScheduleDate, MaintDescription) VALUES (@intEquipID, @dteMaintStartDate, @strMaintDescription)
If @i = (@intRecurr -1)
Begin
Update dbo.Equipment Set dbo.Equipment.MaintFlag=1 Where dbo.Equipment.EquipID=@intEquipID
End
Set @i = @i + 1
END
August 20, 2008 at 4:43 pm
Hi Grasshopper
adding the extra Begin causes an error.
any other suggestions? anyone?
regards
jerry
August 20, 2008 at 5:03 pm
jerrym (8/20/2008)
Hi Grasshopperadding the extra Begin causes an error.
any other suggestions? anyone?
regards
jerry
Yes, you need to re-write the trigger completely to be able to handle multi-row updates.
Better yet, get rid of the trigger and handle this in the stored procedure that does the update.
August 20, 2008 at 5:10 pm
Hi Grasshopper
actually you are correct with the Extra Begin, i forgot to also add the extra End. the code executes now updating the original table with user inputted data & also executing the trigger.
However, the trigger's Insert Into statement is NOT getting the date right. lets say a user updates the following:
StartMaintDate = '2008-09-15'
Frequency = 90
PeriodType = 'Days'
NoOfRecurrances = 8
the code;
===
Set @intFrequency = (@intFrequency * @i)
Set @dteMaintStartDate = DateAdd(Day, @intFrequency, @dteMaintStartDate)
Insert Into MaintenanceDetails (EquipID, ScheduleDate, MaintDescription) VALUES (@intEquipID, @dteMaintStartDate, @strMaintDescription)
===
i get 8 rows inserted into the child table but all 8 rows have the date as 2008-09-15, it should add (90 days x i) to the date for each row. any ideas anyone?
August 20, 2008 at 11:45 pm
i get 8 rows inserted into the child table but all 8 rows have the date as 2008-09-15, it should add (90 days x i) to the date for each row. any ideas anyone?
Its a mathematical problem :hehe: (x * 0 = 0)
Make the following changes:
Set @i = 1
While (@i <= @intRecurr)
Cheers,
Jon
August 20, 2008 at 11:55 pm
jon (8/20/2008)
i get 8 rows inserted into the child table but all 8 rows have the date as 2008-09-15, it should add (90 days x i) to the date for each row. any ideas anyone?
Its a mathematical problem :hehe: (x * 0 = 0)
Make the following changes:
Set @i = 1
While (@i <= @intRecurr)
Cheers,
Jon
Sorry think i given you a wrong solution, here u go this should be e correct one 😀
Set @i = 0
While (@i < @intRecurr)
begin
if @i > 0
Set @intFrequency = @intFrequency * @i
else
Set @intFrequency = @intFrequency
Set @dteMaintStartDate = DateAdd(Day, @intFrequency, @dteMaintStartDate)
August 21, 2008 at 12:22 am
Hi Jon
thanks for your help mate.
i shall give it a bash later on today.
yes, i needed the first row insert to be zero for @i otherwise the MaintStartDate will be out of sync.
regards
jerry
August 21, 2008 at 12:32 am
Hi jerrym,
No prob 🙂 if u need e first @dteMaintStartDate to be '2008-09-15' and subsequent 7 row to add another 90 on top of e prev row then maybe u can shorten your query to just this. Play ard with the looping 🙂
Set @i = 0
While (@i < @intRecurr)
begin
Set @dteMaintStartDate = DateAdd(Day, (@intFrequency * @i), @dteMaintStartDate)
Cheers,
Jon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply