Creating dynamic sql in a tsql trigger

[csphard]

I am using a trigger to update a record in another table when a record is inserted. My problem is that I have a purchase price field that the user may or may not enter a dollar amount.
I was trying to understand how I validate if the purchase price has been entered and include or exclude it from the update statement.

I declared the variable
declare @NewPurchasePrice money

I read it
select @NewPurchasePrice=i.NewPurchasePrice from inserted i

I check by doing the following:
IF @actType = '3' and @PONumber <> '' and @UpdateType = 'Model Number' and @NewPurchasePrice < 1
or
IF @actType = '3' and @PONumber <> '' and @UpdateType = 'Model Number' and @NewPurchasePrice > 0

but this does not work. Am I missing something?

STATEMENT

BEGIN


declare @CubicleRoom varchar(50)
declare @NewCubicleRoom varchar(50)
declare @LOV varchar(15)
declare @NewCubiclestatus varchar(50)
declare @createdbyempid varchar(15)
declare @Createby varchar(50)
declare @createdDateTime DATE
declare @actType varchar(15)
declare @UpdateType varchar(15)
declare @PONumber varchar(25)
declare @NewPONumber varchar(25)
declare @lotpallet varchar(50)
declare @newlotpallet varchar(50)
declare @NewLotPalletstatus varchar(50)
declare @facilityName varchar(150)
declare @DisposalType varchar(25)
declare @ModelNumber varchar(50)
declare @ModelDescription varchar(130)
declare @NewPurchasePrice money

select @CubicleRoom=i.CubicleRoom from inserted i
select @NewCubicleRoom=i.NewCubicleRoom from inserted i
select @LOV=i.LOV from inserted i
select @NewCubiclestatus=i.NewCubicleStatus from inserted i
select @lotpallet=i.LotPallet from inserted i
select @newlotpallet=i.NewLotPallet from inserted i
select @NewLotPalletstatus=i.NewLotPalletStatus from inserted i
select @facilityName=i.FacilityName from inserted i
select @PONumber=i.PONumber from inserted i
select @NewPONumber=i.NewPONumber from inserted i
select @createdbyempid=i.CreatedByEmpid from inserted i
select @Createby=i.CreatedBy from inserted i
select @createdDateTime=i.CreatedDateTime from inserted i
select @actType=i.MassUpdateType from inserted i
select @UpdateType=i.LOV from inserted i
select @DisposalType=i.DisposalType from inserted i
select @ModelNumber=i.ModelNumber from inserted i
select @ModelDescription=i.ModelDescription from inserted i
select @NewPurchasePrice=i.NewPurchasePrice from inserted i


-- PO Number MASS UPDATE: UPDATE PO Number
IF @actType = '3' and @PONumber <> '' and @UpdateType = 'PO Number' and @NewPONumber <> ''
BEGIN
update ConfigurationItem Set "PONumber" = @NewPONumber, MassUpdateType = 'PONumber CHG',
"LastUpdatedBy" = @Createby,"LastUpdatedDateTime" = @createdDateTime, "LastUpdatedEmpid" = @createdbyempid
where PONumber = @PONumber
and ConfigurationItemTypeName = 'Config - Assets'
-- and assetstatus != 'In Use'
END

IF @actType = '3' and @PONumber <> '' and @UpdateType = 'Model Number' and @NewPurchasePrice < 1
BEGIN
update ConfigurationItem Set "ModelDescription" = @ModelDescription,"ModelNumber"= @ModelNumber,
MassUpdateType = 'PONumber Model CHG',"PurchaseDate" = GETDATE(),"LastUpdatedBy" = @Createby,"LastUpdatedDateTime" = @createdDateTime, "LastUpdatedEmpid" = @createdbyempid
where PONumber = @PONumber
and ConfigurationItemTypeName = 'Config - Assets'
-- and assetstatus != 'In Use'
END

IF @actType = '3' and @PONumber <> '' and @UpdateType = 'Model Number' and @NewPurchasePrice > 0
BEGIN
update ConfigurationItem Set "ModelDescription" = @ModelDescription,"ModelNumber"= @ModelNumber,"PurchasePrice" = @NewPurchasePrice,
MassUpdateType = 'PONumber Model CHG',"PurchaseDate" = GETDATE(),"LastUpdatedBy" = @Createby,"LastUpdatedDateTime" = @createdDateTime, "LastUpdatedEmpid" = @createdbyempid
where PONumber = @PONumber
and ConfigurationItemTypeName = 'Config - Assets'
-- and assetstatus != 'In Use'
END

END

I appreciate the help.

 

[bborissov]

NEVER!
Never program trigger with the assumption that you will update only one record at a time.
Remember triggers are fired after all records are updated/inserted or deleted.
So this should be smething like:

-- This
-- IF @actType = '3' and @PONumber <> '' and @UpdateType = 'PO Number' and @NewPONumber <> ''
-- should be something like:

UPDATE ConfigurationItem SET PONumber = Ins.NewPoNumer
                           , MassUpdateType = 'PONumber CHG'
                           , LastUpdatedBy = Ins.Createby
                           ...
FROM ConfigurationItem
INNER JOIN Inserted Ins ON ConfigurationItem.PONumber = Ins.PoNumber
                       AND Ins.MassUpdateType = 3
                       AND Ins.LOV =  'PO Number'
                       Ins.NewPoNumber <> ''
...

Borislav Borissov
VFP9 SP2, SQL Server