Trigger - accessing updated and deleted

• billross

  SSC Eights!

  Points: 972

  More actions

  October 13, 2008 at 8:29 pm

  #383050

  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

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  October 13, 2008 at 9:23 pm

  #884400

  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?