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