Trigger - accessing updated and deleted

  • 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



    Select [Date Posted] into #mydel from deleted

    Select * into #myupd from updated

    IF (Update([Date Purchased])

    AND (#mydel.[Date Posted] > (GetDate() - GetDate())))


    PRINT 'Chop has already been posted - Use OUTTURN to modify.'



    IF (#myupd.[Date Purchased] < GetDate())


    PRINT 'Date Purchased must be >= Today'



    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 Time],


    [Vendor ID],



    [Invoice Date],

    [Invoice Due],


    [Gross Weight],

    [Tare Weight],

    [Net Weight],

    [Cost Per LB],

    [Debit Amount],

    [Credit Amount],

    [Transaction Description],

    [Invoice ID])











    CONVERT (Date, GetDate()),

    CONVERT (Time, GetDate()),





    Convert (Date, GetDate()),

    Convert (Date, GetDate() + 30),






    @updatedCostPerLB * @updatedNetWeight,

    @updatedCostPerLB * @updatedNetWeight * -1,

    'Purchase: ' + @Company,


    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

  • This got posted twice - answers should go over here:

    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