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