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... (AT) gmail (DOT) com> wrote:

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

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)
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 (AT) gmail (DOT) com) writes:
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 (AT) sommarskog (DOT) 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... (AT) perFact (DOT) REMOVETHIS.info.INVALID> wrote:

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

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.

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 (AT) gmail (DOT) com) writes:
The Open Table thing in Mgmt Studio is fairly crappy in my opinion. I never
use it myself.

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 (AT) sommarskog (DOT) 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