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.
| Author |
Topic |
|
Fishwagon
Starting Member
10 Posts |
 Posted - 2007-09-12 : 08:29:14
|
| 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]GOALTER TRIGGER [dbo].[tr_RemoveExpNumberFromPickList] ON [dbo].[Expense_Report]AFTER UPDATEASDeclare @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 EndGOAny help would be appreciated...Thanks and have a great day...Rich |
|
|
|
|
|
Trigger Help