SEO

I get the following error when i try to update a table with the a
trigger (trigger script below),
"Error Message: The row value(s) updated or deleted either do not make
the row unique or they alter multiple rows(3 rows)"

When i run the update on the table with the trigger disabled it works
with no problem. Every table involved has a primary key and as you
can see in the trigger code i set the nocount property to on. I am at
a loss for ideas. thanks for your help, Names have been changed to
protect the guilty


GO
/****** Object: Trigger [dbo].[ecr_approval_update] Script Date:
02/26/2008 09:04:47 ******/
SET NOCOUNT ON
GO

SET ANSI_NULLS ON
go

SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[ecr_approval_update]
ON [dbo].[ecr_approval]
after UPDATE

as
IF ( UPDATE (rd_approval) or UPDATE (prod_approval) )
BEGIN
Declare @r_d_release bit, @production_release bit, @ecr_id
uniqueidentifier,
@mail_text nvarchar(2000), @ecr_num numeric(6, 0), @date datetime,
@ecr_user nvarchar(50), @ecr_type nvarchar(50), @ecr_project
nvarchar(50),
@part_number nvarchar(50), @part_rev nvarchar(5), @part_group
nvarchar(50),
@part_descrip nvarchar(40), @ecr_description nvarchar(500),
@drawing_location nvarchar(100), @email_list nvarchar(1000)

declare @emails table(user_email nvarchar(50))

Select @ecr_id = ecr_id, @r_d_release = rd_approval,
@production_release = prod_approval
from inserted

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =
N'<font size="2" face="Ariel">' +
N'<H1>Change Notification Submital Notice</H1>' +
N'<table border="1" >' +--cellpadding="20"
N'<tr><th>CN #</th><th>Date</th>' +
N'<th>CN User</th><th>CN Type</th><th>CN Project</th>' +
N'<th> Part_Number </th><th>Part Rev</th><th>Part Group</th>' +
N'<th>Part Description</th><th>CN Description</th><th>Drawing
Location</th></tr>' +
CAST ( ( SELECT td = ecr_num, '',
td = date, '',
td = ecr_user, '',
td = ecr_type, '',
td = ecr_project, '',
td = part_number, '',
td = part_rev, '',
td = part_group, '',
td = part_descrip, '',
td = ecr_description, '',
td = drawing_location
from tbl_ecr where ecr_id = @ecr_id
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>'+
N'<F1>This is an auto generated email please do not respond.</F1>'
+
N'<F2>The information contained in this message is confidential and
is intended solely for the recipient(s)</F2>' +
N'<F3>identified above. If you are not the intended recipient or
there are any problems, please notify the sender </F3>' +
N'<F4>immediately. The unauthorized use, disclosure, or copying of
this message is strictly forbidden and may subject you to legal
action.</F4>' +
N'</font>';
Declare @email nvarchar(50)
if ( @r_d_release = 'True')
Begin
insert into @emails(user_email)
SELECT user_email
from tbl_CN_Notifications
where RD_Approved = 'True'
if ((select r_d_cup_open from tbl_ecr where ecr_id = @ecr_id) =
'True')
begin
insert into @emails(user_email)
SELECT user_email
from tbl_CN_Notifications
where RD_Approved_Cup_Open = 'True'
end
if ((select r_d_cup_plate from tbl_ecr where ecr_id = @ecr_id) =
'True')
begin
insert into @emails(user_email)
SELECT user_email
from tbl_CN_Notifications
where RD_Approved_Cup_Plate = 'True'
end
if ((select r_d_busch_open from tbl_ecr where ecr_id = @ecr_id) =
'True' or (select r_d_busch_plate from tbl_ecr where ecr_id = @ecr_id)
= 'True')
begin
insert into @emails(user_email)
SELECT user_email
from tbl_CN_Notifications
where RD_Approved_Busch = 'True'
end
if ((select part_group from tbl_ecr where ecr_id = @ecr_id) =
'Cylinder Head System')
begin
insert into @emails(user_email)
SELECT user_email
from tbl_CN_Notifications
where RD_Approved_Cylinder_Head = 'True'
end
if ((select part_group from tbl_ecr where ecr_id = @ecr_id) =
'Valvetrain System')
begin
insert into @emails(user_email)
SELECT user_email
from tbl_CN_Notifications
where RD_Approved_Valvetrain = 'True'
end
Declare email_curs CURSOR FOR
Select user_email From @emails group by user_email
Open email_curs

Set @email_list = ''
Fetch Next FROM email_curs into @email

While (@@FETCH_STATUS <> -1)
BEGIN
if @email_list <> ''
begin
select @email_list = @email_list+ '; '
end
select @email_list = @email_list + @email
Fetch Next FROM email_curs into @email
END

CLOSE email_curs
DEALLOCATE email_curs

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ECR',
@recipients = @email_list,
@body = @tableHTML,
@body_format = 'HTML',
@subject = 'A New R+D CN Has Been Approved'
End
if ( @production_release = 'True')
Begin
insert into @emails(user_email)
SELECT DISTINCT user_email
from tbl_CN_Notifications
where Production_Approval = 'True' or CNs_created = 'True'
or Production_All = 'True'
if ((select production_busch_open from tbl_ecr where ecr_id =
@ecr_id) = 'True' or (select production_busch_plate from tbl_ecr where
ecr_id = @ecr_id) = 'True')
begin
insert into @emails(user_email)
SELECT DISTINCT user_email
from tbl_CN_Notifications
where Production_Busch = 'True'
end
if ((select part_group from tbl_ecr where ecr_id = @ecr_id) =
'Cylinder Head System')
begin
insert into @emails(user_email)
SELECT DISTINCT user_email
from tbl_CN_Notifications
where Production_Cylinder_Head = 'True'
end
if ((select part_group from tbl_ecr where ecr_id = @ecr_id) =
'Valvetrain System')
begin
insert into @emails(user_email)
SELECT DISTINCT user_email
from tbl_CN_Notifications
where Production_Valvetrain = 'True'
end
Declare email_curs CURSOR FOR
SELECT DISTINCT user_email From @emails group by user_email
Open email_curs
Set @email_list = ''
Fetch Next FROM email_curs into @email

While (@@FETCH_STATUS <> -1)
BEGIN
if @email_list <> ''
begin
select @email_list = @email_list+ '; '
end
select @email_list = @email_list + @email
Fetch Next FROM email_curs into @email
END

CLOSE email_curs
DEALLOCATE email_curs

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ECR',
@recipients = @email_list,
@body = @tableHTML,
@body_format = 'HTML',
@subject = 'A New Production CN Has Been Approved'
End
END

I figured it out. I needed to move the "SET NOCOUNT ON" below the as
statement. Then it worked flawlessly.



On Feb 26, 9:34 am, pyrahna <pltayl...@gmail.com> wrote:
> I get the following error when i try to update a table with the a
> trigger (trigger script below),
> "Error Message: The row value(s) updated or deleted either do not make
> the row unique or they alter multiple rows(3 rows)"
>
> When i run the update on the table with the trigger disabled it works
> with no problem. Every table involved has a primary key and as you
> can see in the trigger code i set the nocount property to on. I am at
> a loss for ideas. thanks for your help, Names have been changed to
> protect the guilty
>
> GO
> /****** Object: Trigger [dbo].[ecr_approval_update] Script Date:
> 02/26/2008 09:04:47 ******/
> SET NOCOUNT ON
> GO
>
> SET ANSI_NULLS ON
> go
>
> SET QUOTED_IDENTIFIER ON
> GO
>
> ALTER TRIGGER [dbo].[ecr_approval_update]
> ON [dbo].[ecr_approval]
> after UPDATE
>
> as
> IF ( UPDATE (rd_approval) or UPDATE (prod_approval) )
> BEGIN
> Declare @r_d_release bit, @production_release bit, @ecr_id
> uniqueidentifier,
> @mail_text nvarchar(2000), @ecr_num numeric(6, 0), @date datetime,
> @ecr_user nvarchar(50), @ecr_type nvarchar(50), @ecr_project
> nvarchar(50),
> @part_number nvarchar(50), @part_rev nvarchar(5), @part_group
> nvarchar(50),
> @part_descrip nvarchar(40), @ecr_description nvarchar(500),
> @drawing_location nvarchar(100), @email_list nvarchar(1000)
>
> declare @emails table(user_email nvarchar(50))
>
> Select @ecr_id = ecr_id, @r_d_release = rd_approval,
> @production_release = prod_approval
> from inserted
>
> DECLARE @tableHTML NVARCHAR(MAX) ;
>
> SET @tableHTML =
> N'<font size="2" face="Ariel">' +
> N'<H1>Change Notification Submital Notice</H1>' +
> N'<table border="1" >' +--cellpadding="20"
> N'<tr><th>CN #</th><th>Date</th>' +
> N'<th>CN User</th><th>CN Type</th><th>CN Project</th>' +
> N'<th> Part_Number </th><th>Part Rev</th><th>Part Group</th>' +
> N'<th>Part Description</th><th>CN Description</th><th>Drawing
> Location</th></tr>' +
> CAST ( ( SELECT td = ecr_num, '',
> td = date, '',
> td = ecr_user, '',
> td = ecr_type, '',
> td = ecr_project, '',
> td = part_number, '',
> td = part_rev, '',
> td = part_group, '',
> td = part_descrip, '',
> td = ecr_description, '',
> td = drawing_location
> from tbl_ecr where ecr_id = @ecr_id
> FOR XML PATH('tr'), TYPE
> ) AS NVARCHAR(MAX) ) +
> N'</table>'+
> N'<F1>This is an auto generated email please do not respond.</F1>'
> +
> N'<F2>The information contained in this message is confidential and
> is intended solely for the recipient(s)</F2>' +
> N'<F3>identified above. If you are not the intended recipient or
> there are any problems, please notify the sender </F3>' +
> N'<F4>immediately. The unauthorized use, disclosure, or copying of
> this message is strictly forbidden and may subject you to legal
> action.</F4>' +
> N'</font>';
> Declare @email nvarchar(50)
> if ( @r_d_release = 'True')
> Begin
> insert into @emails(user_email)
> SELECT user_email
> from tbl_CN_Notifications
> where RD_Approved = 'True'
> if ((select r_d_cup_open from tbl_ecr where ecr_id = @ecr_id) =
> 'True')
> begin
> insert into @emails(user_email)
> SELECT user_email
> from tbl_CN_Notifications
> where RD_Approved_Cup_Open = 'True'
> end
> if ((select r_d_cup_plate from tbl_ecr where ecr_id = @ecr_id) =
> 'True')
> begin
> insert into @emails(user_email)
> SELECT user_email
> from tbl_CN_Notifications
> where RD_Approved_Cup_Plate = 'True'
> end
> if ((select r_d_busch_open from tbl_ecr where ecr_id = @ecr_id) =
> 'True' or (select r_d_busch_plate from tbl_ecr where ecr_id = @ecr_id)
> = 'True')
> begin
> insert into @emails(user_email)
> SELECT user_email
> from tbl_CN_Notifications
> where RD_Approved_Busch = 'True'
> end
> if ((select part_group from tbl_ecr where ecr_id = @ecr_id) =
> 'Cylinder Head System')
> begin
> insert into @emails(user_email)
> SELECT user_email
> from tbl_CN_Notifications
> where RD_Approved_Cylinder_Head = 'True'
> end
> if ((select part_group from tbl_ecr where ecr_id = @ecr_id) =
> 'Valvetrain System')
> begin
> insert into @emails(user_email)
> SELECT user_email
> from tbl_CN_Notifications
> where RD_Approved_Valvetrain = 'True'
> end
> Declare email_curs CURSOR FOR
> Select user_email From @emails group by user_email
> Open email_curs
>
> Set @email_list = ''
> Fetch Next FROM email_curs into @email
>
> While (@@FETCH_STATUS <> -1)
> BEGIN
> if @email_list <> ''
> begin
> select @email_list = @email_list+ '; '
> end
> select @email_list = @email_list + @email
> Fetch Next FROM email_curs into @email
> END
>
> CLOSE email_curs
> DEALLOCATE email_curs
>
> EXEC msdb.dbo.sp_send_dbmail
> @profile_name = 'ECR',
> @recipients = @email_list,
> @body = @tableHTML,
> @body_format = 'HTML',
> @subject = 'A New R+D CN Has Been Approved'
> End
> if ( @production_release = 'True')
> Begin
> insert into @emails(user_email)
> SELECT DISTINCT user_email
> from tbl_CN_Notifications
> where Production_Approval = 'True' or CNs_created = 'True'
> or Production_All = 'True'
> if ((select production_busch_open from tbl_ecr where ecr_id =
> @ecr_id) = 'True' or (select production_busch_plate from tbl_ecr where
> ecr_id = @ecr_id) = 'True')
> begin
> insert into @emails(user_email)
> SELECT DISTINCT user_email
> from tbl_CN_Notifications
> where Production_Busch = 'True'
> end
> if ((select part_group from tbl_ecr where ecr_id = @ecr_id) =
> 'Cylinder Head System')
> begin
> insert into @emails(user_email)
> SELECT DISTINCT user_email
> from tbl_CN_Notifications
> where Production_Cylinder_Head = 'True'
> end
> if ((select part_group from tbl_ecr where ecr_id = @ecr_id) =
> 'Valvetrain System')
> begin
> insert into @emails(user_email)
> SELECT DISTINCT user_email
> from tbl_CN_Notifications
> where Production_Valvetrain = 'True'
> end
> Declare email_curs CURSOR FOR
> SELECT DISTINCT user_email From @emails group by user_email
> Open email_curs
> Set @email_list = ''
> Fetch Next FROM email_curs into @email
>
> While (@@FETCH_STATUS <> -1)
> BEGIN
> if @email_list <> ''
> begin
> select @email_list = @email_list+ '; '
> end
> select @email_list = @email_list + @email
> Fetch Next FROM email_curs into @email
> END
>
> CLOSE email_curs
> DEALLOCATE email_curs
>
> EXEC msdb.dbo.sp_send_dbmail
> @profile_name = 'ECR',
> @recipients = @email_list,
> @body = @tableHTML,
> @body_format = 'HTML',
> @subject = 'A New Production CN Has Been Approved'
> End
> END

On Tue, 26 Feb 2008 06:34:02 -0800 (PST), pyrahna wrote:

>I get the following error when i try to update a table with the a
>trigger (trigger script below),
>"Error Message: The row value(s) updated or deleted either do not make
>the row unique or they alter multiple rows(3 rows)"

Hi pyrahna,

That does not sound like a SQL Server error message. But rather like a
message generated by your front end. Many front end programs can get
confused when triggers modify data. This message sounds like such an
issue.

However, I saw a different problem with your trigger:

(snip)
> Select @ecr_id = ecr_id, @r_d_release = rd_approval,
> @production_release = prod_approval
> from inserted

If an UPDATE statement affects multiple rows, this will cause one (more
or less at random) of them to be processed. The others will be ignored
by your trigger. I don't think that is what you want!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

pyrahna (pltaylor3@gmail.com) writes:
> Select @ecr_id = ecr_id, @r_d_release = rd_approval,
> @production_release = prod_approval
> from inserted

Ohoh, that won't fly. A trigger fires once per statement, so if there
are more that row updated, you will only send mail for one row.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

It was an error message from Microsoft SQL Server Managment Studio
front end....not technically a sql message but i would hope that it
would handle error messages decently.

As for the select statement that has been pointed out by two people
now I don't see it being a problem in the application, but I am
interested in possible solutions and work arounds so that the problem
doesn't exist and so Ii can better at this.
thank you for your time



On Feb 26, 5:48 pm, Hugo Kornelis
<h...@perFact.REMOVETHIS.info.INVALID> wrote:
> On Tue, 26 Feb 2008 06:34:02 -0800 (PST), pyrahna wrote:
> >I get the following error when i try to update a table with the a
> >trigger (trigger script below),
> >"Error Message: The row value(s) updated or deleted either do not make
> >the row unique or they alter multiple rows(3 rows)"
>
> Hi pyrahna,
>
> That does not sound like a SQL Server error message. But rather like a
> message generated by your front end. Many front end programs can get
> confused when triggers modify data. This message sounds like such an
> issue.
>
> However, I saw a different problem with your trigger:
>
> (snip)
>
> > Select @ecr_id = ecr_id, @r_d_release = rd_approval,
> > @production_release = prod_approval
> > from inserted
>
> If an UPDATE statement affects multiple rows, this will cause one (more
> or less at random) of them to be processed. The others will be ignored
> by your trigger. I don't think that is what you want!
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

On Wed, 27 Feb 2008 03:57:04 -0800 (PST), pyrahna wrote:

>It was an error message from Microsoft SQL Server Managment Studio
>front end....not technically a sql message but i would hope that it
>would handle error messages decently.

Hi pyrahna,

I only now see your own follow up message about the SET NOCOUNT. Yes,
omitting this command at the beginning of a trigger or stored procedure
results in extra results being sent to the client, which can cause all
kind of problems.

>As for the select statement that has been pointed out by two people
>now I don't see it being a problem in the application, but I am
>interested in possible solutions and work arounds so that the problem
>doesn't exist and so Ii can better at this.

The ideal method would be to replace the entire trigger with a single
set-based logic. However, the trigger code is too complicated and too
long for me to try this from here and in my spare time. So either you do
that yourself, or you take a shortcut and simply use a cursor to process
all rows in the "inserted" pseudo table one by one.

Note that a cursor in a trigger is NOT generally recommended since it
hurts performance, but each rule has its exceptions.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

pyrahna (pltaylor3@gmail.com) writes:
> It was an error message from Microsoft SQL Server Managment Studio
> front end....not technically a sql message but i would hope that it
> would handle error messages decently.

The Open Table thing in Mgmt Studio is fairly crappy in my opinion. I never
use it myself.

> As for the select statement that has been pointed out by two people
> now I don't see it being a problem in the application, but I am
> interested in possible solutions and work arounds so that the problem
> doesn't exist and so Ii can better at this.
> thank you for your time

You could set up a cursor that loops over inserted, if you think that
multi-row updates are rare.

Or you can be this brutal and add this first in the trigger:

IF @@rowcount > 1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Multi-row updates are not permitted on this table', 16, 1)
RETURN
END



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx