quote:

---

Originally posted by TAS

I have a INSERT, UPDATE, DELETE trigger as follows



CREATE TRIGGER [trigInvUpdate] ON [dbo].[tblInv]
FOR INSERT, UPDATE, DELETE
AS
Declare @InsCode varchar(5), @InsCodeOld varchar(5)
Declare @InsCodeInclude varchar(5), @InsCodeIncludeOld varchar(5)
Declare @InsCodeExclude varchar(5), @InsCodeExcludeOld varchar(5)
Declare @InvCal int, @InvCalOld int
Declare @RateVar decimal, @RateVarOld decimal
Declare @RateFixed decimal, @RateFixedOld decimal
Declare @LimitLow decimal, @LimitLowOld decimal
Declare @LimitHigh decimal, @LimitHighOld decimal
Declare @FirstPay smalldatetime, @FirstPayOld smalldatetime
Declare @Name varchar(40), @NameOld varchar(40)
Declare @Street varchar(40), @StreetOld varchar(40)
Declare @Street2 varchar(40), @Street2Old varchar(40)
Declare @City varchar(28), @CityOld varchar(28)
Declare @State char(2), @StateOld char(2)
Declare @Zip varchar(10), @ZipOld varchar(10)
Declare @LikeCode bit, @LikeCodeOld bit
Declare @InsertCount int, @DeleteCount int
Declare @Note varchar(100)

Select @InsertCount = (Select COUNT(*) from INSERTED)

If @InsertCount >0
Begin
Select @InsCode=vchrInsCode,@InsCodeInclude=vchrInsCodeInclude,
@InsCodeExclude =vchrInsCodeExclude,@InvCal=intInvCalc,
@RateVar =decRateVar, @RateFixed=decRateFixed,
@LimitLow =decLimitLow, @LimitHigh =decLimitHigh ,
@FirstPay =sdtmFirstDay , @Name =vchrName,
@Street=vchrStreet, @Street2 = vchrStreet2 ,
@City =vchrCity , @State =chrState , @Zip =vchrZip,
bitLikeCode = @LikeCode from INSERTED

Select @InsCodeOld = vchrInsCode, @InsCodeIncludeOld=vchrInsCodeInclude,
@InsCodeExcludeOld =vchrInsCodeExclude, @InvCalOld = intInvCal,
@RateVarOld =decRateVar,@RateFixedOld =decRateFixed,
@LimitLowOld =decLimitLow, @LimitHighOld =decLimitHigh,
@FirstPayOld =sdtmFirstPay, @NameOld =vchrName,
@StreetOld =vchrStreet,@Street2Old = vchrStreet2,
@CityOld =vchrCity, @StateOld =chrState, @ZipOld =vchrZip,
@LikeCodeOld =bitLikeCode from DELETED
End


If ISNULL(@InsCode,'') <>ISNULL(@InsCodeOld,'')
Select @Note='Change Provider Invoice Info'
Begin
INSERT INTO tblChngAgtProv VALUES('P', @InsCode, 'Provider Code', @InsCodeOld, @InsCode, Getdate(),Substring(System_user,7,30), @Note)
End


If ISNULL(@Street,'') <>ISNULL(@StreetOld,'')
Begin
Insert into tblChngAgtProv values('P', @InsCode, 'Street', @StreetOld, @Street, Getdate(),Substring(System_user,7,30), @Note)

End




When it runs, I got an error:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.


Anyone would help?

---