October 13, 2008 at 8:29 pm
Below is the first part of my first attempt at a Trigger...
I want to:
* see if they entered a date purchased - if so, I need to generate a transaction record
* if the record [prior to update] has a posted date, do NOT generate a transaction record
* copy several of the fields from the updated record into the transaction record, along with several new and calculated fields
I get a message saying that
The multi-part identifier "#mydel.Date Posted" could not be bound.
CREATE TRIGGER tr_PurchaseThisChop
ON dbo.Chops
AFTER UPDATE
AS
Select [Date Posted] into #mydel from deleted
Select * into #myupd from updated
IF (Update([Date Purchased])
AND (#mydel.[Date Posted] > (GetDate() - GetDate())))
BEGIN
PRINT 'Chop has already been posted - Use OUTTURN to modify.'
RETURN
END
IF (#myupd.[Date Purchased] < GetDate())
BEGIN
PRINT 'Date Purchased must be >= Today'
RETURN
END
DECLARE @GLAccount nVarChar(5)
DECLARE @Company nVarChar(20)
DECLARE @UpdatedChopID nVarChar(6)
DECLARE @UpdatedContractLevel nVarChar(1)
DECLARE @UpdatedCoffeeType nVarChar (5)
DECLARE @UpdatedChopDate DateTime
DECLARE @UpdatedLocationID nVarChar(5)
DECLARE @UpdatedVendorID nVarChar(15)
DECLARE @UpdatedBags SmallInt
DECLARE @UpdatedGrossWeight Int
DECLARE @UpdatedTareWeight int
DECLARE @UpdatedNetWeight int
DECLARE @UpdatedCostPerLB int
DECLARE @UpdatedInvoiceID nVarChar(15)
SET @UpdatedVendorID = (Select [Vendor ID] From updated)
SET @UpdatedVendorID = #myupd[Vendor ID]
SET @UpdatedChopID = #mydel.[Chop ID]
SET @UpdatedContractLevel = #mydel.[Contract Level]
SET @UpdatedCoffeeType = #myupd[Coffee Type ID]
SET @UpdatedChopDate = #myupd[Chop Date]
SET @UpdatedLocationID = #myupd[Location ID]
SET @UpdatedBags = #myupd[Bags]
SET @UpdatedGrossWeight = #myupd[Gross Weight]
SET @UpdatedTareWeight = #myupd[Tare Weight]
SET @UpdatedNetWeight = #myupd[Net Weight]
SET @UpdatedCostPerLB = #myupd[Cost Per LB]
SET @UpdatedInvoiceID = #myupd[Invoice ID]
EXEC @GLAccount = sp_GetGLAccountByCoffeeType @CoffeeType = 'Coffee Type'
EXEC @Company = sp_GetCompanyByVendorID @VendorID = @UpdatedVendorID
/* Create an Inventory Transaction */
INSERT INTO [Inventory Transactions]
([Transaction Date],
[Transaction Type],
[Debit Account],
[Credit Account],
[Chop ID],
[Contract Level],
[Coffee Type],
[Chop Date],
[Location ID],
[Entered],
[Entered Time],
[Number],
[Vendor ID],
[UserID],
[Reference],
[Invoice Date],
[Invoice Due],
[Bags],
[Gross Weight],
[Tare Weight],
[Net Weight],
[Cost Per LB],
[Debit Amount],
[Credit Amount],
[Transaction Description],
[Invoice ID])
VALUES
(GetDate(),
'AP',
@GLAccount,
@UpdatedVendorID,
@updatedChopID,
@updatedContractLevel,
@updatedCoffeeType,
@updatedChopDate,
@updatedLocationID,
CONVERT (Date, GetDate()),
CONVERT (Time, GetDate()),
'?',
@updatedVendorID,
CURRENT_USER,
@updatedInvoiceID,
Convert (Date, GetDate()),
Convert (Date, GetDate() + 30),
@updatedBags,
@updatedGrossWeight,
@updatedTareWeight,
@updatedNetWeight,
@updatedCostPerLB,
@updatedCostPerLB * @updatedNetWeight,
@updatedCostPerLB * @updatedNetWeight * -1,
'Purchase: ' + @Company,
@updatedInvoiceID)
Also, is there a simpler way to test the presence of a date than > GetDate() - GetDate()?
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
October 13, 2008 at 9:23 pm
This got posted twice - answers should go over here:
http://www.sqlservercentral.com/Forums/Topic585181-1292-1.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply