I am in a strange situation where I am not able to update rows in my View via SSMS. It gives me following error.
Error Source: sqlserver management datatools
Error Message: the row value updated or deleted either do not make the row unique or they alter multiple rows
However, I am able to update them in in Visual Studio and also via TSQL Update statement. This looks like a bug in SQL Server Management Studio.
Any help/suggestions are appreciated.
View
CREATE VIEW [ELog].[FileDroppingArea_2]
AS
SELECT
ea.SK_EmailAttachementsId,
e.SK_EmailId,
e.ServiceTypeCode,
e.serviceprovidercode,
pa.OrginalNameOfAttachment AS ParentAttachmentOriginalName,
pa.NewNameOfAttachment AS ParentAttachmentName,
e.FileNameModifier,
ea.OrginalNameOfAttachment,
--ea.NewFileName,
(
SELECT
ServiceTypeSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') ServiceTypeSplit
WHERE ServiceTypeSplit.RowId = '1'
) AS ServiceTypeFromFile,
(
SELECT
ServiceProviderSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') ServiceProviderSplit
WHERE ServiceProviderSplit.RowId = '2'
) AS ServiceProviderFromFile,
(
SELECT
SubmissionYearSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') SubmissionYearSplit
WHERE SubmissionYearSplit.RowId = '3'
) AS SubmissionYearFromFile,
(
SELECT
SubmissionMonthSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') SubmissionMonthSplit
WHERE SubmissionMonthSplit.RowId = '4'
) AS SubmissionMonthFromFile,
(
SELECT
CommissionerCodeSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') CommissionerCodeSplit
WHERE CommissionerCodeSplit.RowId = '5'
) AS CommissionerCodeFromFile,
(
SELECT
FlexOrFreezeeSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') FlexOrFreezeeSplit
WHERE FlexOrFreezeeSplit.RowId = '6'
) AS FlexOrFreezeeFromFile,
(
SELECT
VersionSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') VersionSplit
WHERE VersionSplit.RowId = '7'
) AS VersionFromFile,
(
SELECT
ServiceSubTypeSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') ServiceSubTypeSplit
WHERE ServiceSubTypeSplit.RowId = '8'
) AS ServiceSubTypeFromFile,
ea.CopyToServiceType
FROM ELog.EmailAttachements ea
INNER JOIN
ELog.Emails e ON e.SK_EmailId = ea.SK_EmailId
LEFT JOIN
ELog.EmailAttachements pa ON ea.SK_ParentEmailAttachements = pa.SK_EmailAttachementsId
WHERE ea.IsReadyForProcessing = 1
AND ea.IsMovedToRaw = 0
Instead of Update Trigger
CREATE TRIGGER [ELog].[TRG_upd_FileDropAreaByColumn] ON [ELog].[FileDroppingArea_2]
INSTEAD OF UPDATE
AS
BEGIN
DECLARE @SKEmailAttachmentId INT
DECLARE @ServiceTypeFromFile VARCHAR(MAX)
DECLARE @ServiceProviderFromFile VARCHAR(MAX)
DECLARE @SubmissionYearFromFile VARCHAR(MAX)
DECLARE @SubmissionMonthFromFile VARCHAR(MAX)
DECLARE @CommissionerCodeFromFile VARCHAR(MAX)
DECLARE @FlexOrFreezeeFromFile VARCHAR(MAX)
DECLARE @VersionFromFile VARCHAR(MAX)
DECLARE @ServiceSubTypeFromFile VARCHAR(MAX)
DECLARE @CopyToServiceType VARCHAR(MAX)
DECLARE @NewFileName VARCHAR(MAX)
DECLARE @CalculatedVersion VARCHAR(MAX)
DECLARE @CurrentFileName VARCHAR(MAX)
DECLARE @ServiceType_Current VARCHAR(MAX)
DECLARE @ServiceProvider_Current VARCHAR(MAX)
DECLARE @SubmissionYear_Current VARCHAR(MAX)
DECLARE @SubmissionMonth_Current VARCHAR(MAX)
DECLARE @CommissionerCode_Current VARCHAR(MAX)
DECLARE @FlexOrFreezee_Current VARCHAR(MAX)
DECLARE @Version_Current VARCHAR(MAX)
DECLARE @ServiceSubType_Current VARCHAR(MAX)
DECLARE InsteadOfUpdate CURSOR LOCAL FAST_FORWARD
FOR SELECT
I.SK_EmailAttachementsId,
I.ServiceTypeFromFile,
I.ServiceProviderFromFile,
I.SubmissionYearFromFile,
I.SubmissionMonthFromFile,
I.CommissionerCodeFromFile,
I.FlexOrFreezeeFromFile,
I.VersionFromFile,
I.ServiceSubTypeFromFile,
I.CopyToServiceType
FROM INSERTED I
INNER JOIN
DELETED D ON D.SK_EmailAttachementsId = I.SK_EmailAttachementsId
WHERE NOT(ISNULL(I.ServiceTypeFromFile, '') COLLATE latin1_general_cs_as = ISNULL(D.ServiceTypeFromFile, '')
COLLATE latin1_general_cs_as
AND ISNULL(I.ServiceProviderFromFile, '') COLLATE latin1_general_cs_as = ISNULL(D.
ServiceProviderFromFile, '') COLLATE latin1_general_cs_as
AND ISNULL(I.SubmissionYearFromFile, '') COLLATE latin1_general_cs_as = ISNULL(D.SubmissionYearFromFile,
'') COLLATE latin1_general_cs_as
AND ISNULL(I.SubmissionMonthFromFile, '') COLLATE latin1_general_cs_as = ISNULL(D.
SubmissionMonthFromFile, '') COLLATE latin1_general_cs_as
AND ISNULL(I.CommissionerCodeFromFile, '') COLLATE latin1_general_cs_as = ISNULL(D.
CommissionerCodeFromFile, '') COLLATE latin1_general_cs_as
AND ISNULL(I.FlexOrFreezeeFromFile, '') COLLATE latin1_general_cs_as = ISNULL(D.FlexOrFreezeeFromFile,
'') COLLATE latin1_general_cs_as
AND ISNULL(I.VersionFromFile, '') COLLATE latin1_general_cs_as = ISNULL(D.VersionFromFile, '') COLLATE
latin1_general_cs_as
AND ISNULL(I.ServiceSubTypeFromFile, '') COLLATE latin1_general_cs_as = ISNULL(D.ServiceSubTypeFromFile,
'') COLLATE latin1_general_cs_as
AND ISNULL(I.CopyToServiceType, '') COLLATE latin1_general_cs_as = ISNULL(D.CopyToServiceType, '')
COLLATE latin1_general_cs_as)
OPEN InsteadOfUpdate
FETCH NEXT FROM InsteadOfUpdate INTO @SKEmailAttachmentId,
@ServiceTypeFromFile,
@ServiceProviderFromFile,
@SubmissionYearFromFile,
@SubmissionMonthFromFile,
@CommissionerCodeFromFile,
@FlexOrFreezeeFromFile,
@VersionFromFile,
@ServiceSubTypeFromFile,
@CopyToServiceType
WHILE @@FETCH_STATUS = 0
BEGIN
SET @CurrentFileName =
(
SELECT
ea.NewFileName
FROM ELog.EmailAttachements ea
WHERE ea.SK_EmailAttachementsId = @SKEmailAttachmentId
)
SELECT
@ServiceType_Current = ServiceTypeSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(@CurrentFileName, '_') ServiceTypeSplit
WHERE ServiceTypeSplit.RowId = '1'
SELECT
@ServiceProvider_Current = ServiceProviderSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(@CurrentFileName, '_') ServiceProviderSplit
WHERE ServiceProviderSplit.RowId = '2'
SELECT
@SubmissionYear_Current = SubmissionYearSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(@CurrentFileName, '_') SubmissionYearSplit
WHERE SubmissionYearSplit.RowId = '3'
SELECT
@SubmissionMonth_Current = SubmissionMonthSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(@CurrentFileName, '_') SubmissionMonthSplit
WHERE SubmissionMonthSplit.RowId = '4'
SELECT
@CommissionerCode_Current = CommissionerCodeSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(@CurrentFileName, '_') CommissionerCodeSplit
WHERE CommissionerCodeSplit.RowId = '5'
SELECT
@FlexOrFreezee_Current = FlexOrFreezeeSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(@CurrentFileName, '_') FlexOrFreezeeSplit
WHERE FlexOrFreezeeSplit.RowId = '6'
SELECT
@Version_Current = VersionSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(@CurrentFileName, '_') VersionSplit
WHERE VersionSplit.RowId = '7'
SELECT
@ServiceSubType_Current = ServiceSubTypeSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(@CurrentFileName, '_') ServiceSubTypeSplit
WHERE ServiceSubTypeSplit.RowId = '8';
WITH CurrentVersion
AS (SELECT
(
SELECT
VersionSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') VersionSplit
WHERE VersionSplit.RowId = '7'
) StoredVersion
FROM ELog.EmailAttachements ea
WHERE ea.SK_EmailAttachementsId <> @SKEmailAttachmentId
AND @ServiceTypeFromFile =
(
SELECT
ServiceTypeSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') ServiceTypeSplit
WHERE ServiceTypeSplit.RowId = '1'
)
AND @ServiceProviderFromFile =
(
SELECT
ServiceProviderSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') ServiceProviderSplit
WHERE ServiceProviderSplit.RowId = '2'
)
AND @SubmissionYearFromFile =
(
SELECT
SubmissionYearSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') SubmissionYearSplit
WHERE SubmissionYearSplit.RowId = '3'
)
AND @SubmissionMonthFromFile =
(
SELECT
SubmissionMonthSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') SubmissionMonthSplit
WHERE SubmissionMonthSplit.RowId = '4'
)
AND @CommissionerCodeFromFile =
(
SELECT
CommissionerCodeSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') CommissionerCodeSplit
WHERE CommissionerCodeSplit.RowId = '5'
)
AND @FlexOrFreezeeFromFile =
(
SELECT
FlexOrFreezeeSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') FlexOrFreezeeSplit
WHERE FlexOrFreezeeSplit.RowId = '6'
)
AND @ServiceSubTypeFromFile =
(
SELECT
ServiceSubTypeSplit.FieldValue
FROM Fnc.mTVFSplitStringIntoTable_MT(ea.NewFileName, '_') ServiceSubTypeSplit
WHERE ServiceSubTypeSplit.RowId = '8'
))
SELECT
@CalculatedVersion = ISNULL(
(
SELECT
'V'+RIGHT('00'+CAST(MAX(REPLACE(StoredVersion, 'V', ''))+1 AS
VARCHAR(10)), 2)
), 'V01')
FROM CurrentVersion
SET @CurrentFileName = REPLACE(@CurrentFileName, @ServiceType_Current, @ServiceTypeFromFile)
SET @CurrentFileName = REPLACE(@CurrentFileName, @ServiceProvider_Current, @ServiceProviderFromFile)
SET @CurrentFileName = REPLACE(@CurrentFileName, @SubmissionYear_Current, @SubmissionYearFromFile)
SET @CurrentFileName = REPLACE(@CurrentFileName, @SubmissionMonth_Current, @SubmissionMonthFromFile)
SET @CurrentFileName = REPLACE(@CurrentFileName, @CommissionerCode_Current, @CommissionerCodeFromFile)
SET @CurrentFileName = REPLACE(@CurrentFileName, @FlexOrFreezee_Current, @FlexOrFreezeeFromFile)
SET @CurrentFileName = REPLACE(@CurrentFileName, @Version_Current, @CalculatedVersion)
SET @CurrentFileName = REPLACE(@CurrentFileName, @ServiceSubType_Current, @ServiceSubTypeFromFile)
SET @NewFileName = @CurrentFileName
UPDATE EA
SET
EA.NewFileName = @CurrentFileName,
EA.CopyToServiceType = @CopyToServiceType,
EA.LastUpdatedTimestamp = GETDATE(),
EA.ModifiedBYUser = SUSER_SNAME()
FROM ELog.EmailAttachements EA
WHERE
IsReadyForProcessing = 1
AND IsMovedToRaw = 0
AND EA.SK_EmailAttachementsId = @SKEmailAttachmentId
AND NOT(ISNULL(EA.NewFileName, '') COLLATE latin1_general_cs_as = ISNULL(@CurrentFileName, '') COLLATE
latin1_general_cs_as
AND ISNULL(EA.CopyToServiceType, '') COLLATE latin1_general_cs_as = ISNULL(@CopyToServiceType, '')
COLLATE latin1_general_cs_as)
FETCH NEXT FROM InsteadOfUpdate INTO @SKEmailAttachmentId,
@ServiceTypeFromFile,
@ServiceProviderFromFile,
@SubmissionYearFromFile,
@SubmissionMonthFromFile,
@CommissionerCodeFromFile,
@FlexOrFreezeeFromFile,
@VersionFromFile,
@ServiceSubTypeFromFile,
@CopyToServiceType
END
END
GO
P.S. Earlier I was updating newFileName column directly from view, since I split the column into 8 different columns, these 8 new columns are appearing as disabled in Visual Studio
Best Answer
It's not a bug, you should use T-SQL statement as you did and not a GUI. When your update/delete statement affects more than one identical rows (duplicates) you've got this kind of error ("or they alter multiple rows"), that's because in GUI you edit one row at a time, but GUI have to produce a T-SQL statement corresponding to your action and it fails, because the statement produced cannot distinguish between duplicates, which one of them you wanted to update/delete.