September 12, 2007 at 8:43 am
Hello,
I am a relative newbie, so if this is simple I apologize.
I have an update trigger. This trigger is suppose to act on a column value (columnname is called Status). If the Status turns to Complete or Paid, then it deletes a value from another table if it exists. If any other status change takes place, it will populate that table if the value does not already reside in that table (using a column called ExpReportNumber).
It seems that while this trigger works, it locks the record that was originaly updated on the table where the trigger resides.....so subsequent updates cannot be made to the record.
Here is the definition....
USE [ExpenseReport]
GO
ALTER TRIGGER [dbo].[tr_RemoveExpNumberFromPickList] ON [dbo].[Expense_Report]
AFTER UPDATE
AS
Declare @ExpRepNumber VarChar(50)
Declare @NewStatus VarChar(50)
If Update(Status)
Begin
Select @ExpRepNumber = (Select ExpReportNumber from Inserted)
Select @NewStatus = (Select Status from Inserted)
If @NewStatus = 'Complete' or @NewStatus = 'Paid'
Begin
Delete from FPL5 where Item = @ExpRepNumber
End
If @NewStatus <> 'Complete' And @NewStatus <> 'Paid'
Begin
Set RowCount 0
Select * from FPL5 Where Item = @ExpRepNumber
If @@RowCount = 0
Begin
Insert into FPL5 Values(@ExpRepNumber, Null, Null, Null, Null, Null)
End
End
End
GO
Any help would be appreciated...
Thanks and have a great day...
Rich
September 12, 2007 at 8:54 am
This is normal behavior as the trigger is part of the UPDATE transaction to that row. SQL Server holds the locks that were placed on the row for the UPDATE for the duration of the trigger execution. If the trigger execution takes long enough, it could delay other processes. Your trigger does not do much so I'm questioning how it could stay running long enough to block. Make sure the Item column in your FPL5 table is indexed.
September 12, 2007 at 9:01 am
Thank you John, I didn't think it was doing much either and couldn't figure out why it would maintain the lock...I'll try to indexing....
Thanks again,
Rich
September 12, 2007 at 9:27 am
It may also help to look and see if there is a DELETE trigger on the table you are deleting from or an INSERT trigger on the insert table.
September 12, 2007 at 12:23 pm
Four things to watch out for:
The trigger is incorrectly coded for single-row changes. All trigers should handle multi-row changes.
UPDATE(STATUS) does not means that the status column 'changed' it means that it was 'touched' which in other words it could have been from 'Complete' to 'Complete' when someone did something like update
status = status. So you will need to verify also that the before and after images are different!
Another issue is that the 'status' coulmn could be NULL you need to take care of that somehow
And last but not least
If @NewStatus = 'Complete' or @NewStatus = 'Paid'
Begin
Delete from FPL5 where Item = @ExpRepNumber
End
If @NewStatus 'Complete' And @NewStatus 'Paid'
Begin
....
should be changed (in my opinion, I could be wrong...) to:
If @NewStatus = 'Complete' or @NewStatus = 'Paid'
Begin
Delete from FPL5 where Item = @ExpRepNumber
End
ELSE
Begin
....
hope you can fix those.
* Noel
September 12, 2007 at 2:50 pm
Rich,
Spend some time learning how to code triggers.
Triggers are not part of your application, they are part of an table or view. They will executed even when you do update from EM or QA.
The way you coded your trigger it may produce absolutely inconsistent data in table FPL5, and you not gonna know what's the problem.
Not to mention missing data.
One-row type coding is absolutely prohibited in triggers.
If you cannot do set-based code better leave triggers for SQL professionals.
To help you start with learning I rewrote your trigger.
-----------------------
USE [ExpenseReport]
GO
ALTER TRIGGER [dbo].[tr_RemoveExpNumberFromPickList] ON [dbo].[Expense_Report]
AFTER UPDATE
AS
If Update(Status)
Begin
Delete from dbo.FPL5
where EXISTS
( select 1 from inserted i
inner join @FinishedStatuses S ON S.Status = i.NewStatus
where dbo.FPL5.Item = i.ExpReportNumber
and (i.NewStatus = 'Complete' or i.NewStatus = 'Paid')
)
Insert into dvo.FPL5
(Item, .., ..., ..., ...)
SELECT i.ExpReportNumber, Null, Null, Null, Null, Null
FROM Inserted i
WHERE i.NewStatus 'Complete' AND i.NewStatus 'Paid'
AND NOT EXISTS (select 1 from dvo.FPL5 F
where F.Item = i.ExpReportNumber
GROUP BY i.ExpReportNumber
End
GO
-------------------
It's not perfect, for example, I would prefer to have business rule for 'Complete' and 'Paid' statuses in separate table, not in code. Then I would join "inserted" to that table to find out if new statuses are within "Finished Statuses" list.
But at least it will give you idea where to start from.
_____________
Code for TallyGenerator
September 13, 2007 at 6:04 am
Thank you all very much, it is very much appreciated.....
Have a great day everyone...
Rich
September 13, 2007 at 6:09 am
Good luck with it.
Just be careful - don't mess with triggers until you sure you know what you are doing.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply