Updating multiple rows.
-
Hi, I'm having trouble updating fields from an insert trigger.
DECLARE @IDField as char(10)
DECLARE @IDField2 as char(7)
SELECT @IDField = (Select IDField from inserted)
SELECT @IDField2 = (Select IDField2 from inserted)
UPDATE dbo.myTable
SET RecordStatus = 'R'
WHERE IDField = @IDField AND NOT IDField2 = @IDField2
When this trigger executes I get an error message saying it cannot complete as the sub query returns more than one value. I don't see the problem with this as I want to update all earlier records that have the same primary IDField. Any ideas or is this just the way it is?
-
Well check if
(Select IDField from inserted)
and
(Select IDField2 from inserted)
are returning single records or not.
Thanks
Prasad Bhogadi
www.inforaise.com -
Already checked that, inserted those values into a table and it only returned one record.
If I specify just one record for the update query it works, however as soon as it has to update more than one record I recieve the error.
Cheers
-
The inserted table will contain 1 or more rows depending on how many rows are being inserted.
with this staement
INSERT INTO dbo.myTable (IDField, IDField) VALUES ('A','B')
the trigger will have an inserted table with 1 row
with this staement
INSERT INTO dbo.myTable (IDField, IDField)
SELECT 'A','B' UNION SELECT 'C','D'
the trigger will have an inserted table with 2 rows
this should do what you want
UPDATE a
SET a.RecordStatus = 'R'
FROM dbo.myTable a
INNER JOIN inserted i
ON i.IDField = a.IDField
AND i.IDField2 <> a.IDField2
also i presume this an AFTER trigger !!
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks for that fella, still no joy though. The engine just doesn't seem to like it when it has to run an update query that affects more than one record from within a trigger. I'll try and explain what I'm trying to do again.
I have a personal details table and a table that records an individuals transactions through a process. Each time a new record is added to show a new transaction I want the trigger to ensure that all previous transaction records for that individual have a record status of R leaving the new record as the active one.
-
Hey the smily faces aren't mine, but use a temp table and maybe a cursor for nice processing of each row. Also don't listen to any rheotoric about cusors...
Then you capture all rows and get the time to process them either as a set, the temp table, or a cursor.
I use this extensively for building audit trails, what column, who, when, beforeandafter images of data, etc.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER TEST
ON TABLE_TEST
INSTEAD OF INSERT
AS
CREATE TABLE #TEMP_INSERT
(
SOURCE_VALUE_A VARCHAR(1000),
SOURCE_VALUE_B VARCHAR(1000),
SOURCE_VALUE_C VARCHAR(1000)
 
) INSERT INTO #TEMP_INSERT (SOURCE_VALUE_A, SOURCE_VALUE_B, SOURCE_VALUE_C)
SELECT SOURCE_VALUE_A, SOURCE_VALUE_B, SOURCE_VALUE_CFROM inserted
/*
FOR INSERT, UPDATE, DELETE
CREATE TABLE #TEMP_INSERT
(
SOURCE_VALUE_A VARCHAR(1000),
SOURCE_VALUE_B VARCHAR(1000),
SOURCE_VALUE_C VARCHAR(1000)
 
)INSERT INTO #TEMP_INSERT (SOURCE_VALUE_A, SOURCE_VALUE_B, SOURCE_VALUE_C)
SELECT SOURCE_VALUE_A, SOURCE_VALUE_B, SOURCE_VALUE_CFROM inserted
*/
-- Now process each of the rows
-- Maybe a cursor is good here??
DECLARE @TOKEN_ID INT
DECLARE @SOURCE_VALUE_A VARCHAR(2000)
DECLARE @SOURCE_VALUE_B VARCHAR(2000)
DECLARE @SOURCE_VALUE_C VARCHAR(2000)
DECLARE @VALUE_FOUND VARCHAR(2000)
DECLARE TEMP_CURSOR CURSOR FOR (
SELECT SOURCE_VALUE_A,
SOURCE_VALUE_B,
SOURCE_VALUE_C,
VALUE_FOUND
FROM #TEMP_INSERT
 
)OPEN TEMP_CURSOR
FETCH NEXT FROM TEMP_CURSOR INTO @SOURCE_VALUE_A, @SOURCE_VALUE_B, @SOURCE_VALUE_C
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'DO SOMETHING'
FETCH NEXT FROM TEMP_CURSOR INTO @SOURCE_VALUE_A, @SOURCE_VALUE_B, @SOURCE_VALUE_C
END
CLOSE TEMP_CURSOR
DEALLOCATE TEMP_CURSOR
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Oops on SQL in post, its a little hacked.
At any rate I read your post again and thats really what I am doing too in this case.
In my case I have a composite primary key on a table (two int fields make up the primary key).
Getting the intA is no problem, just ask for the max on that table. Getting the second value is tricky. You have to be next in line to increment.
So with this trigger I can
...row by row control
...intA = N, intB = M1
...intA = N, intB = M2
...intA = N, intB = M3
...intA = N, intB = M4
...intA = N, intB = M5
-
QUOTE:
I have a personal details table and a table that records an individuals transactions through a process. Each time a new record is added to show a new transaction I want the trigger to ensure that all previous transaction records for that individual have a record status of R leaving the new record as the active one.
---
Absolutely no need for a cursor (in a trigger? come on!), or for variables. Think SET based...your trigger would only work for one record, as only one value will fit in a variable. You're crippling SQL when you force it to deal with one row at a time, either this way or through a cursor.
From the logical table inserted you should join to the updated table on the individualID's being the same and the RecordIDs being different:
create trigger myTrigger
on myTable
After Update
as
Update
Set RecordStatus = 'R'
From inserted i
JOIN dbo.myTable t on t.IndividualID = i.IndividualID
Where i.RecordID <> t.RecordID
Now while this should work for you; you run a risk of some pretty bad locking going on. This is a tough problem and you should consider other options if possible, although if this table is open to be updated directly then you're pretty much stuck with this. An option is only allowing updates through procs, which can handle all the logic normally put in triggers.
Signature is NULL
-
Can you please post the TRIGGER in full including it's CREATE statement and any triggers that run for UPDATES.
-
Ok so I answered how to hold onto all the rows in table "inserted" and "deleted". Don't need a cursor for that. But you do for other stuff.
It has been my experience you ask either of these table one question like COUNT(*) and they disappear on you.
I knew the cursor thing would bring a flame from the set-minded sql gurus (no pun)...
Wonder how many folks really benefit from hearing what the lunatic fringe thing about TSQL cursors. My dbs don't have trillions of transactions per second. Microsoft gave us the tool and I use it solve real problems.
Any way, good weekend, bye, my DTS is done...
-
Yeah, there's a need for cursors...gulp. That was hard. Honestly, though, I have yet to find a cursor I couldn't get rid of. Granted, sometimes looping is necessary, but that's what the While keyword is for (and it performs better).
One this I would say, though; when confronted with the need for a cursor, try to see if you can do the same tasks in sets instead. Many times (like above) you can.
cl
Signature is NULL
-
Yes your right. Cursors blow... but so do correlated subqueries....which is what set-minded folks do for fancy looping. While statements don't calc for each tuple. But I am always looking for better ways to do things.
In my experience, when large tables are being banged on for inserts, updates, and the like, my correlated subqueries perform so much poorer than cursors.
Also the people paying my salary don't see the need for the extra nanosecond of improvement when it takes all darn day to code a correlated subquery that works.
Finally they like results quickly and outsource crap I like to take my time on. So I got nothing to look forward to by avoiding cursors.
Yes your constructive criticism is helpful for the masses, but in reality there are three competing metrics; time, budget, and features. None of which is benefited by avoiding cursors.
So I read Celko 1996 smarties and learned a lot. But the only real trait I carry on today that is a trick is derived tables. They are the really neat and highly functional.
And looping in a while doesn't solve the "increment the second field sequentially" for me. A correlated subquery does but when your tables have 10 million plus rows it hurts.
Hope the original poster got something good out of this debate.
Party on SQL people!!!!
-
Here's the original trigger, MobID and ServNum make up a composite primary key.
CREATE TRIGGER [RecStat] ON dbo.tblMobilisationTransaction
FOR INSERT
AS
DECLARE @MobID as varchar (7)
DECLARE @ServNum as char (9)
SELECT @MobID =(SELECT MOBID FROM inserted)
SELECT @ServNum = (SELECT ServiceNumber FROM inserted)
UPDATE m
SET RecordStatus = 'R'
FROM dbo.tblMobilisationTransaction m
WHERE m.ServiceNumber = @ServNum AND MobID != @MobID
I've also tried it by joining directly to the inserted table.
-
Try this...
CREATE TRIGGER [RecStat] ON dbo.tblMobilisationTransaction
FOR INSERT
AS
UPDATE
m
SET
RecordStatus = 'R'
FROM
dbo.tblMobilisationTransaction m
INNER JOIN
inserted i
ON
m.ServiceNumber = i.ServiceNumber
WHERE
m.MobID != i.MOBID
Also, what would you want to happen if m.MobID were NULL and/or i.MOBID were NULL or is that column not nullable?
-
-------------------------------------
---- YOUR CODE --------------------
-------------------------------------
DECLARE @MobID as varchar (7)
DECLARE @ServNum as char (9)
SELECT @MobID =(SELECT MOBID FROM inserted)
SELECT @ServNum = (SELECT ServiceNumber FROM inserted)
-------------------------------------
---- SUBSTITUTE WITH THIS CODE---
-------------------------------------
DECLARE @MobID as varchar (7)
DECLARE @ServNum as char (9)
CREATE TABLE #TEMP_INSERT
(
@MobID as varchar (7),
@ServNum as char (9)
 
INSERT INTO #TEMP_INSERT ([MOBID],[ServiceNumber])
SELECT [MOBID], [ServiceNumber] FROM inserted
SELECT @MobID =(SELECT MOBID FROM #TEMP_INSERT )
SELECT @ServNum = (SELECT ServiceNumber FROM #TEMP_INSERT )
DROP #TEMP_INSERT
-----------------------------------------
and leave the remainder of your code in tact
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply