April 8, 2008 at 10:04 am
I was asked to enhance a trigger that inserts more than one row from a set of inserts to insert into a cross-reference table
the trigger looks at a set of invoice items coming in (as a table called INSERTED) and inserts into a table called VendorItems, if the item being invoiced is not already there
the trigger now needs to add a row for the store (from the Invoice Header) and vendoritem
the table called INSERTED is the set of invoicedetails added
but if I add an OUTPUT clause then it becomes a set of VendorItems, right?
what do I do?
April 8, 2008 at 11:04 am
If you need the list of VendorItems being inserted, you could use an OUTPUT clause on the insert statement, but I don't think you need to get that complex.
You can reference the INSERTED table (which is the list of initially updated or inserted records for the table that the trigger is on) multiple times in your trigger. Feel free to do another:
INSERT INTO MyTable (MyField1, MyField2)
SELECT FieldA, FieldB FROM INSERTED
April 9, 2008 at 6:38 am
Thanks for your response
Reluctantly I used a cursor at the firm request of the lead programmer - this process runs mostly in the background (no user impatiently waiting for it to finish)
DECLARE @Variables INT (or VARCHAR, etc)
DECLARE @New_ID INT
DECLARE Knarly_Cursor AS CURSOR JOINing a lot of tables (including INSERTED)
OPEN Knarly_Cursor
FETCH Knarly_Cursor INTO @Variables
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT FirstTable
VALUES(@Variables)
SET @New_ID = @@IDENTITY
INSERT Second Table
VALUES (@Variables, @New_ID)
FETCH Knarly_Cursor INTO @Variables
END
CLOSE Knarly_Cursor
DEALLOC Knarly_Cursor
April 9, 2008 at 6:50 am
Just to be blunt, your lead programmer who insists on a cursor in that trigger should be told to step away from the database and keep both hands visible. He shouldn't be allowed to touch a database.
First, that whole thing can be done with 2 insert ... select statements, which will perform much better than the cursors, even on a single row of data.
Second, even though no user is waiting for it to finish, the server certainly is. The transaction can't complete till the trigger is done, which means it's keeping locks held, which means it is affecting every other transaction that affects either table the trigger references. At the very least, even if it's just taking row-level locks, it's holding up CPU resources and RAM that could be better used for something else.
The only excuse for using a trigger in a case like this is if you also happen to be the hardware vendor and you're trying to convince the customer that he needs a new server, "because look how slow the current one is!" (That would be an unethical excuse, but at least it would make sense.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply