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:18 pm
There is no updated virtual table - it's Inserted. That is more than likely what your problem is.
It's always a combination of Inserted and/or Deleted.
----------------------------------------------------------------------------------
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?
October 13, 2008 at 9:48 pm
That was it, Matt. Thanks. That and the fact that I was missing some periods. Thanks! (I don't know how to mark this as the answer).
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
October 13, 2008 at 10:10 pm
Thanks for the feedback! I am glad that helped you.
(There is no way to mark a specific answer, so don't worry about hunting it down!)
----------------------------------------------------------------------------------
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?
October 14, 2008 at 1:40 am
SET @UpdatedVendorID = #myupd[Vendor ID]
That's not how you get a value from a table in SQL.
SELECT @UpdatedVendorID = [Vendor ID] FROM #myupd
Also, what's going to happen if more than 1 row was updated? There will be more than 1 row in the inserted and deleted tables. Your trigger will only process one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2008 at 6:26 am
Yeah, I am only concerned with a single row at the moment. I suppose I need a where clause.
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
October 14, 2008 at 6:43 am
You don't need a where clause anywhere. If only 1 row is affected by the update, then inserted and deleted will have only 1 row in.
If there's a chance that more than 1 row will be updated at once, then you need to make sure the trigger can handle multiple rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2008 at 10:05 am
billross (10/14/2008)
Yeah, I am only concerned with a single row at the moment. I suppose I need a where clause.
You may only be concerned with a single row now, but when there is a multi-row action you will not get the correct results. Sure you are probably using a stored procedure to enforce a single row update from the application, but, what about when you or another developer/DBA does a mass update, or because the users have direct table access someone uses Access to do an update.
In my opinion triggers should ALWAYS be written to handle sets which is why I wrote this article: Introduction to DML Triggers[/url]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 14, 2008 at 8:23 pm
Now, I know someone is asking, "What if I need to process each row?". If this were posted on a forum I would ask, why? What is your desired result at the end of the process? Then I would attempt to provide a set-based option, and if I couldn't I would bet someone else could. In all honesty, if you think you need a loop (cursor) in a trigger you probably want to re-evaluate your process as that will absolutely kill performance.
Hi, Jack. Thanks for both the response and the article. I find myself, in fact, on the horns of this very dillemma.
What I need to do is handle (I plan on using a trigger) the following:
* the user edits a field on an existing Invoice (header record) that indicates that they are ready to post the entire invoice (header and line items), initiating my UPDATE trigger on the header record
* I then need to create an inventory transaction record for each line item as well as one for each of the miscellaneous charges that are on the header record
Should I FETCH?
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
October 14, 2008 at 9:44 pm
It is non-intuitive to me that triggers are batched. That is, it appears that if you update 5 records, the 5 updates are done, then the 5 deleted and 5 inserted records are sent to the trigger. I would have expected that each update would fire a trigger (update-trigger, update-trigger...). This would eliminate to process the trigger as a batch.
I think I need 2 cursors:
* a cursor of each updated Invoice Header (marked to post)
* a nested cursor of each Invoice Line Item (to generate an inventory transaction for each)
It seems logical and unavoidable. Fortunately this will not be a particularly crucial feature to have run super-fast.
"What I lack in youth I make up for in immaturity!"
Please visit my music site at http://woundedego.com
October 14, 2008 at 10:14 pm
hmm... Nothing you've mentioned so far requires a cursor of any kind. What makes you think you absolutely need to process each row one at a time? I understand that's a "typical" procedural approach, but you're not in a procedural world anymore. You might want to consider doing this as a set (i.e. all at once) - that's the way SQL server is designed to operate.
----------------------------------------------------------------------------------
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?
October 15, 2008 at 1:12 am
billross (10/14/2008)
It is non-intuitive to me that triggers are batched. That is, it appears that if you update 5 records, the 5 updates are done, then the 5 deleted and 5 inserted records are sent to the trigger. I would have expected that each update would fire a trigger (update-trigger, update-trigger...).
An update will fire the trigger once, regardless of how many rows are affected. SQL doesn't process updates 1 row at a time, so it's not update, update, update, ... if 5 rows are updated. It's a single update that changes 5 rows
This would eliminate to process the trigger as a batch.
Don't think of it as a batch. SQL's a set-based language and as such it's normal to affect multiple rows at a time
I think I need 2 cursors:
* a cursor of each updated Invoice Header (marked to post)
* a nested cursor of each Invoice Line Item (to generate an inventory transaction for each)
It seems logical and unavoidable. Fortunately this will not be a particularly crucial feature to have run super-fast.
Logical in a procedural world, maybe, but not here, and certainly not unavoidable. Can you explain a bit more what you're trying to do. I'm sure someone can write a trigger that does what you need without cursors.
p.s. Why are you posting double every time you reply?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2008 at 7:35 am
You are missing something. Your syntax is missing some stuff (what is causing your errors).
An update involving another table looks like:
UPDATE myTable
SET
[my field] = table2.[my field]
FROM myTable
inner join Table2 on mytable.ID=table2.MyTableID
Your sub-queries should also work, unless they return more than one value for a given ID. It's probably not an entirely bad thing, since correlated sub-queries tend to perform badly (and don't seem to be necessary.)
----------------------------------------------------------------------------------
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?
October 15, 2008 at 8:13 am
billross (10/15/2008)
What I find exasperating in dealing with T-SQL is that it can't do this:UPDATE myTable
SET
[my field] = table2.[my field]
and it can't even do this:
UPDATE myTable
SET
[my field] = (select [my field] from table2 where [myKey] = insert.[my key])
Sure it can. Your syntax is just slightly incorrect. See Matt's post for the details
Have I missed the simple and direct way to do stuff in this new world?
Yup.
May I suggest you spend some time reading Books Online?
Also, I wrote a series of basics T-SQL guides a while back. Maybe they would help you a bit.
http://grounding.co.za/blogs/gail/default.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply