| Author |
 Topic  |
|
|
cplusplus
Constraint Violating Yak Guru
481 Posts |
 Posted - 06/11/2012 : 10:14:17
|
Unable to modify the table rows via edit table option, i get the following message: The row values updated or deleted either do not make the row unique or they alter multiple rows
coorrect the errors and retry or press esc.
I can modify the rows via update queries with no issues, why is there a problem updating this way.
I have trigger associated with this table for insert/updates. to create row in a log table.
Thanks a lot for the helpful info. |
Edited by - cplusplus on 06/11/2012 10:28:15
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
cplusplus
Constraint Violating Yak Guru
481 Posts |
Posted - 06/11/2012 : 15:37:29
|
Hello Tara, I have a trigger, on insert, update it creates a row to log table, also has three constraints on parent table.
trigger is causing the issue, i have identity column activityid in parent table, in child i do have identity column ID but that is a surrogate key id.
here is my trigger, i have three constraints within table, 1.) put getdate(), 2.) a bit column default value is (0), 3.) bit value default =(0).
are my constraints causing the issue:
CREATE TRIGGER [dbo].[insTab_Workflow_log] ON [dbo].[Tab_Workflows]
FOR INSERT, UPDATE
AS
INSERT INTO Tab_Workflow_log
([ActivityID],
[ModuleRecordID],
[ModuleName],
[Step],
[Type],
[AssignedTo],
[Description],
[DueDate],
[DoneDate],
[Disposition],
[Comments],
[Critical],
[EmailFlag],
[Date_Updated],
[IsRejected],
[IsPlaceHolder])
Select
ins.[ActivityID],
ins.[ModuleRecordID],
na.[ModuleName],
na.[Step],
na.[Type],
na.[AssignedTo],
na.[Description],
na.[DueDate],
na.[DoneDate],
na.[Disposition],
na.[Comments],
na.[Critical],
na.[EmailFlag],
na.[Date_Updated],
na.[IsRejected],
na.[IsPlaceHolder]
FROM inserted ins JOIN Tab_Workflows na ON
na.ActivityID = ins.ActivityID
|
 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
cplusplus
Constraint Violating Yak Guru
481 Posts |
Posted - 06/11/2012 : 16:43:46
|
Hello TARA,
here is teh message at the end i see (2 rows), what does that mean?
Any issues with my trigger?
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows).
Thanks a lot for the helpful info.
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
cplusplus
Constraint Violating Yak Guru
481 Posts |
Posted - 06/12/2012 : 14:37:52
|
Hello Tara, I am sorry for the late response. here is the definitions of both tables.
---------------Table1-------
CREATE TABLE [dbo].[Tab_Workflows](
[ActivityID] [int] IDENTITY(1,1) NOT NULL,
[ModuleRecordID] [int] NOT NULL,
[ModuleName] [nvarchar](50) NOT NULL,
[Step] [int] NOT NULL,
[Type] [int] NOT NULL,
[AssignedTo] [int] NOT NULL,
[Description] [nvarchar](100) NOT NULL,
[DueDate] [datetime] NOT NULL,
[DoneDate] [datetime] NULL,
[Disposition] [int] NULL,
[Comments] [nvarchar](max) NULL,
[Critical] [bit] NULL,
[EmailFlag] [bit] NULL,
[Date_Updated] [datetime] NULL,
[IsRejected] [bit] NULL,
[IsPlaceHolder] [bit] NULL,
[UpdatedBy] [varchar](40) NULL,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Tab_ccsNetWorkflows] ADD DEFAULT (getdate()) FOR [Date_Updated]
GO
ALTER TABLE [dbo].[Tab_ccsNetWorkflows] ADD DEFAULT ((0)) FOR [IsRejected]
GO
ALTER TABLE [dbo].[Tab_ccsNetWorkflows] ADD DEFAULT ((0)) FOR [IsPlaceHolder]
GO
--------------Table2
CREATE TABLE [dbo].[Tab_Workflow_log](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ActivityID] [int] NULL,
[ModuleRecordID] [int] NOT NULL,
[ModuleName] [nvarchar](50) NOT NULL,
[Step] [int] NOT NULL,
[Type] [int] NOT NULL,
[AssignedTo] [int] NOT NULL,
[Description] [nvarchar](100) NOT NULL,
[DueDate] [datetime] NOT NULL,
[DoneDate] [datetime] NULL,
[Disposition] [int] NULL,
[Comments] [nvarchar](max) NULL,
[Critical] [bit] NULL,
[EmailFlag] [bit] NULL,
[Date_Updated] [datetime] NULL,
[IsRejected] [bit] NULL,
[IsPlaceHolder] [bit] NULL
) ON [PRIMARY]
GO
Thanks a lot for the helpful info.
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
cplusplus
Constraint Violating Yak Guru
481 Posts |
Posted - 06/12/2012 : 17:38:45
|
Thanks a lot TARA, all my tables has Identity column with increments 1, i thought that will take care of the id to be unique.
didn't think i still need to set the field as primary key.
it worked perfectly fine, now allowing changes manually.
|
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
| |
Topic |
|
|
|
Unable to modify the table rows via edit table opt
