February 19, 2009 at 11:50 am
Hi when I run the following trigger , I get an error (cited below).
Please let me know how to fix it.
Thanks.
USE [Performance]
GO
/****** Object: Trigger [dbo].[trgInsHSBCNetEPM1] Script Date: 02/19/2009 10:58:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trgInsHSBCNetEPM1]
ON [dbo].[FundPerfValues]
AFTER INSERT, Update
AS
If update(TotalNAVFinal)
DECLARE @CurrMonth datetime
SET @CurrMonth = (Select ReportMonth from INSERTED)
DECLARE @NewMonth datetime
SET @NewMonth = dateadd( month, 1, @CurrMonth)
DECLARE @NewID nvarchar(50)
SET @NewID = (Select GAMFullId from INSERTED)
INSERT INTO dbo.[FundPerfValues] (ReportMonth, GAMFullID) VALUES ( @NewMonth, @NewID )
No row was updated.
The data in row 205 was not commited.
Error Source: .NEtSqlClient Data Provider.
Error Message: Subquery returned more than 1 value. This is not permitted when the s.....
February 19, 2009 at 11:58 am
massoud.karimzadeh (2/19/2009)
Hi when I run the following trigger , I get an error (cited below).Please let me know how to fix it.
Thanks.
USE [Performance]
GO
/****** Object: Trigger [dbo].[trgInsHSBCNetEPM1] Script Date: 02/19/2009 10:58:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trgInsHSBCNetEPM1]
ON [dbo].[FundPerfValues]
AFTER INSERT, Update
AS
If update(TotalNAVFinal)
DECLARE @CurrMonth datetime
SET @CurrMonth = (Select ReportMonth from INSERTED)
DECLARE @NewMonth datetime
SET @NewMonth = dateadd( month, 1, @CurrMonth)
DECLARE @NewID nvarchar(50)
SET @NewID = (Select GAMFullId from INSERTED)
INSERT INTO dbo.[FundPerfValues] (ReportMonth, GAMFullID) VALUES ( @NewMonth, @NewID )
No row was updated.
The data in row 205 was not commited.
Error Source: .NEtSqlClient Data Provider.
Error Message: Subquery returned more than 1 value. This is not permitted when the s.....
Is your trigger duplicating rows on the same table ?
Why are you coding for single-row changes, triggers must be coded to handle multiple rows!
* Noel
February 19, 2009 at 12:02 pm
Hi Noel,
My user enters one value, TotalNAVFinal, for a given month and I create a new row for the following month. This is why it's coded for one row.
Thanks
February 19, 2009 at 12:09 pm
But the same happens every time you update ?
Also there is no BEGIN END pair on the IF UPDATED ?
Sorry I think you have to re-write the entire thing.
* Noel
February 19, 2009 at 12:26 pm
[font="Verdana"]
Your trigger code contains statements like:
SET @CurrMonth = (Select ReportMonth from INSERTED)
But the INSERTED (and DELETED) pseudo-tables can contain more than one row. So when you try to do an operation to your [dbo].[FundPerfValues] table that changes more than one row at a time, your trigger will fall over.
Also, because your If statement has no begin/end, it will only apply to the next statement. So the effect of this:
If update(TotalNAVFinal)
DECLARE @CurrMonth datetime
...is to ensure that the @CurrMonth variable is only declared if the TotalNAVFinal field is updated. The rest of the trigger runs all of the time. Which means that for any operation not changing TotalNAVFinal, the trigger will fail as the code uses @CurrMonth, but it's not defined.
But wait... your code, even when it works, will generate a failure. This is because it goes on to do an insert into the table, causing the trigger to run again, which will do an insert into the table, which will cause the trigger to run again, which will do an insert into the table, which will cause the trigger to run again...
You get the picture.
So there are many things wrong with your trigger. Why don't you tell us what you are trying to achieve, and we can give you some ideas on how best to achieve it? A trigger is the option of last resort, and unless you know precisely what you are doing, should be avoided at all cost.
[/font]
February 19, 2009 at 12:26 pm
Yes, but the variables change because the same row is not always updated and put in the INSERTED Table.
Adding Begin and End doesn't change the results. I get the same error.
February 19, 2009 at 12:31 pm
Massoud (2/19/2009)
Yes, but the variables change because the same row is not always updated and put in the INSERTED Table.Adding Begin and End doesn't change the results. I get the same error.
The error is clearly telling you that one or more of those subqueries is sporting multiple rows. Until you recode to handle multiple rows being updated at a time - you will have a problem.
----------------------------------------------------------------------------------
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?
February 19, 2009 at 12:32 pm
Hi Bruce,
I changed the code to
USE [Performance]
GO
/****** Object: Trigger [dbo].[trgInsHSBCNetEPM1] Script Date: 02/19/2009 10:58:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trgInsHSBCNetEPM1]
ON [dbo].[FundPerfValues]
AFTER INSERT, Update
AS
If update(TotalNAVFinal)
Begin
DECLARE @CurrMonth datetime
SET @CurrMonth = (Select ReportMonth from INSERTED)
DECLARE @NewMonth datetime
SET @NewMonth = dateadd( month, 1, @CurrMonth)
DECLARE @NewID nvarchar(50)
SET @NewID = (Select GAMFullId from INSERTED)
INSERT INTO dbo.[FundPerfValues] (ReportMonth, GAMFullID) VALUES ( @NewMonth, @NewID )
End
Would it still run in a loop if I don't update TotalNAVFinal in the new entry?
My goal is just to create a new entry for the given id changed for the following month.
Thanks
February 19, 2009 at 1:09 pm
[font="Verdana"]I'm tempted to just tell you to rewrite your solution without using a trigger. A gateway procedure is a better design, and has less performance issues. However... let's have a look at your code. You did fix one issue (well done.) But the issue of still assuming that INSERTED and DELETED only contain one row remains.
Anyway, here's my suggested modification to your code. I haven't tested this, so beware!
alter trigger dbo.trgInsHSBCNetEPM1
on dbo.FundPerfValues
after insert, update
as begin
--
-- insert some commentary here just to explain what your code is trying
-- to achieve, so that when you come back in 2 months time to change
-- it, you can remember what you did and why
--
--
-- use update to check whether the TotalNavFinal field has been changed
-- somewhere in the new values. If it hasn't, no further work is required
--
if update(TotalNAVFinal) begin
--
-- create a list of new report months for the records with a changed
-- TotalNavFinal
--
insert into dbo.FundPerfValues(
ReportMonth,
GAMFullID
)
select dateadd(month, 1, i.ReportMonth),
i.GAMFullID
from inserted i
--
-- join between the new values and the old values so we can make the
-- new values just where the TotalNavFinal field has been changed on
-- the record.
--
-- also, only do this for non-null values of the TotalNavFinal field,
-- so we don't end up with a chain of inserts caused by the trigger
--
left join
deleted d
on i.ReportMonth = d.ReportMonth and
i.GAMFullID = d.GAMFullID
where i.TotalNavFinal is not null and (
d.TotalNavFinal is null or
i.TotalNavFinal != d.TotalNavFinal
);
end; -- if
end; -- trigger
go
[/font]
February 19, 2009 at 3:51 pm
Thanks Bruce and Noel.
Testing for nulls fixed my issue.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply