INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Creating dynamic sql in a tsql trigger

Creating dynamic sql in a tsql trigger

(OP)
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.

RE: Creating dynamic sql in a tsql trigger

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:

CODE

-- 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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close