hmstechsupport
asked on
Updating multiple rows with my trigger gives me a subquery error
I have created the following SQL trigger which always returns an error when I run it on multiple rows, so if have more than 1 row and I execute "UPDATE TSDETAIL SET TSD_SUN=120" I get the error below. When I execute with the KEY such as "UPDATE TSDETAIL SET TSD_SUN=120 WHERE TSD_KEY=1" , I do not get any errors.
My question is how do I write the trigger to work in both cases.
Msg 512, Level 16, State 1, Procedure UPDATE_TSD_RATES, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
ALTER TRIGGER [dbo].[UPDATE_TSD_RATES] ON [dbo].[TSDETAIL]
AFTER UPDATE
AS
DECLARE @v_rate1 NUMERIC(15,4)
DECLARE @v_rate2 NUMERIC(15,4)
DECLARE @v_min NUMERIC(11)
DECLARE @tsd_key NUMERIC(11)
DECLARE @tsd_rat NUMERIC(11)
BEGIN
SET @tsd_key=(SELECT TSD_KEY FROM inserted)
SET @tsd_rat=(SELECT TSD_RAT FROM inserted)
SET @v_rate1=(SELECT RAT_RATE1 FROM RATE WHERE RAT_KEY=@tsd_rat)
SET @v_rate2=(SELECT RAT_RATE2 FROM RATE WHERE RAT_KEY=@tsd_rat)
SET @v_min = (SELECT TSD_MON+TSD_TUE+TSD_WED+TS
UPDATE [dbo].[TSDETAIL] SET TSD_RATE1=@v_rate1, TSD_RATE2=@v_rate2, TSD_MIN=@v_min
WHERE [dbo].[TSDETAIL].TSD_KEY = @TSD_KEY
END
My question is how do I write the trigger to work in both cases.
Msg 512, Level 16, State 1, Procedure UPDATE_TSD_RATES, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
ALTER TRIGGER [dbo].[UPDATE_TSD_RATES] ON [dbo].[TSDETAIL]
AFTER UPDATE
AS
DECLARE @v_rate1 NUMERIC(15,4)
DECLARE @v_rate2 NUMERIC(15,4)
DECLARE @v_min NUMERIC(11)
DECLARE @tsd_key NUMERIC(11)
DECLARE @tsd_rat NUMERIC(11)
BEGIN
SET @tsd_key=(SELECT TSD_KEY FROM inserted)
SET @tsd_rat=(SELECT TSD_RAT FROM inserted)
SET @v_rate1=(SELECT RAT_RATE1 FROM RATE WHERE RAT_KEY=@tsd_rat)
SET @v_rate2=(SELECT RAT_RATE2 FROM RATE WHERE RAT_KEY=@tsd_rat)
SET @v_min = (SELECT TSD_MON+TSD_TUE+TSD_WED+TS
UPDATE [dbo].[TSDETAIL] SET TSD_RATE1=@v_rate1, TSD_RATE2=@v_rate2, TSD_MIN=@v_min
WHERE [dbo].[TSDETAIL].TSD_KEY = @TSD_KEY
END
ASKER
The trigger works fine 1 row at a time, but I need to retroactively update the rows that are already in the table and the easiest way is an update statment. I could write some PLSQL in a stored procedure to update 1 line at a time, but that seems like a long workaround.
Is there no way to write the TRIGGER in SQL to accomplish this?
Is there no way to write the TRIGGER in SQL to accomplish this?
or what you can do is....
You can remove trigger and update directly..
UPDATE TSDETAIL
SET TSD_SUN=120,
TSD_RATE1=RAT_RATE1,
TSD_RATE2=RAT_RATE2,
TSD_MIN = TSD_MON+TSD_TUE+TSD_WED+TS
from TSDETAIL A INNER JOIN RATE B
ON A.TSD_RAT = B.RAT_KEY
WHERE TSD_KEY=1
You can remove trigger and update directly..
UPDATE TSDETAIL
SET TSD_SUN=120,
TSD_RATE1=RAT_RATE1,
TSD_RATE2=RAT_RATE2,
TSD_MIN = TSD_MON+TSD_TUE+TSD_WED+TS
from TSDETAIL A INNER JOIN RATE B
ON A.TSD_RAT = B.RAT_KEY
WHERE TSD_KEY=1
Hi,
It might not be accomplish with trigger, if you can update in single query then that will more beneficial, in terms of performance and debugging view.
As you said I could write some PLSQL in a stored procedure to update 1 line at a time, but that seems like a long workaround.
- It wont be....i dont think so
It might not be accomplish with trigger, if you can update in single query then that will more beneficial, in terms of performance and debugging view.
As you said I could write some PLSQL in a stored procedure to update 1 line at a time, but that seems like a long workaround.
- It wont be....i dont think so
ASKER
Ok. I have written the stored procedure to accomplish the update and it works fine and accomplished the immediate solution to the problem, but my question about how to do this in a trigger remains unanswered. Is there no answer to this?
You can do so,by changing query.sory buddy bt trigger is not meant 4 that.
http://www.google.co.in/gwt/x?dc=Web&client=Web&hl=en&gl=IN&q=How+many+rows+can+be+store+Inserted+in+trigger&defaultloc=Mumbai,+Maharashtra,+India&sa=D&ei=YpruTKCqJpCYrAfs774F&ved=0CAkQFjAB&source=m&rd=1&u=http://msdn.microsoft.com/en-us/library/ms190752.aspx
http://www.google.co.in/gwt/x?dc=Web&client=Web&hl=en&gl=IN&q=How+many+rows+can+be+store+Inserted+in+trigger&defaultloc=Mumbai,+Maharashtra,+India&sa=D&ei=YpruTKCqJpCYrAfs774F&ved=0CAkQFjAB&source=m&rd=1&u=http://msdn.microsoft.com/en-us/library/ms190752.aspx
ASKER
Ok. Thanks Brichsoft. Unfortunate, but I guess that is the way it works.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I didnt said,its not possible.but does it require?
Wen u can do it without trigger then why trigger require?
Wen u can do it without trigger then why trigger require?
There are many reasons you want to have a trigger acting on the records you have updated:
* updating a secondary table
* no influence to the programming
* expansions which would else require to have the change done on many code lines
* supporting accumulated columns (like the TSD_MIN here).
The danger with such attempt is that is not directly derivable when seeing the query which tables are involved in a transaction. The RATE table would be part of the transaction, hence locked, though the query does not refer to it.
* updating a secondary table
* no influence to the programming
* expansions which would else require to have the change done on many code lines
* supporting accumulated columns (like the TSD_MIN here).
The danger with such attempt is that is not directly derivable when seeing the query which tables are involved in a transaction. The RATE table would be part of the transaction, hence locked, though the query does not refer to it.
ASKER
Ok. I had to modify this a little as I do want it to work for Insert and Update so I added the INSERT clause and I had to make a few other minor changes adding commas and adding aliases to avoid ambiguity but this is EXACTLY what I hoped for. Works like a charm in both inserting new rows and also running a big update statement.
Thank you Qlemo.
I also agree that the reason I do this is to avoid adding code to the application that ther clients do not require. This trigger suits this client perfectly.
ALTER TRIGGER [dbo].[UPDATE_TSD_RATES] ON [dbo].[TSDETAIL]
AFTER INSERT,UPDATE
AS
BEGIN
UPDATE tbl
SET TSD_RATE1 = r.RAT_RATE1,
TSD_RATE2 = r.RAT_RATE2,
TSD_MIN = tbl.TSD_MON+tbl.TSD_TUE+tb
FROM dbo.TSDETAIL tbl
JOIN inserted i ON tbl.TSD_KEY = i.TSD_KEY
JOIN RATE r ON i.TSD_RAT = r.RAT_KEY
END
Thank you Qlemo.
I also agree that the reason I do this is to avoid adding code to the application that ther clients do not require. This trigger suits this client perfectly.
ALTER TRIGGER [dbo].[UPDATE_TSD_RATES] ON [dbo].[TSDETAIL]
AFTER INSERT,UPDATE
AS
BEGIN
UPDATE tbl
SET TSD_RATE1 = r.RAT_RATE1,
TSD_RATE2 = r.RAT_RATE2,
TSD_MIN = tbl.TSD_MON+tbl.TSD_TUE+tb
FROM dbo.TSDETAIL tbl
JOIN inserted i ON tbl.TSD_KEY = i.TSD_KEY
JOIN RATE r ON i.TSD_RAT = r.RAT_KEY
END
ASKER
Fabulous and exactly what I was looking for.
As you also know that
when you run below query
UPDATE TSDETAIL SET TSD_SUN=120
This will update multiple rows.
so SET @tsd_key=(SELECT TSD_KEY FROM inserted) will failed.
You can put (SELECT TOP 1 TSD_KEY FROM inserted) but this is not right way.
Best thing is update query with parameter.
or why you wanted to update multiple at a time????