0

I have created the 2 After Insert Trigger on same Table. 1st Trigger executed successfully. But 2nd Trigger is not executing and giving the exception from c# code 'Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded.'

Earlier I used the CTE in my trigger but I read that CTE is not supported in Triggers. Then I used Temp Tables.

ALTER TRIGGER [dbo].[CodedDatas_INS_ProjectOperationalDataUserWise]
    ON [LIO00110].[dbo].[CodedDatas]
    AFTER  INSERT
    AS
    BEGIN

    --UPDATE [SydCoding].[DBO].[ProjectReportOperationDataUserWise]
    --          SET DocCorrected = 0
    
    SET NOCOUNT ON;

    CREATE TABLE #CTE1 (EnteredBy varchar(50), ReviewedTimeInMin Decimal(30,4),CodingTimeInMin Decimal(30,4));
    CREATE TABLE #CTE2 (EnteredBy varchar(50), CodingTimeInHR Decimal(30,4),ReviewedTimeInHR Decimal(30,4));
    CREATE TABLE #CTE3 (EnteredBy varchar(50), DocCoded int);
    CREATE TABLE #CTE4 (EnteredBy varchar(50), DocReviewed int);
    CREATE TABLE #CTE5 (EnteredBy varchar(50), DocCorrected int);
    CREATE TABLE #CTE6 (EnteredBy varchar(50), LastModified varchar(50));
    CREATE TABLE #CTE7 (ProjectId varchar(50),EnteredBy varchar(50), CODINGRATE Decimal(30,4), REVIEWRATE Decimal(30,4), DocCoded int, DocReviewed int,
    CodingTimeInMin Decimal(30,4),ReviewedTimeInMin Decimal(30,4),DocCorrected int,LastModified varchar(50),PerDocCorrected Decimal(30,4));

    INSERT INTO #CTE1 (EnteredBy,ReviewedTimeInMin,CodingTimeInMin)
    Select C1.EnteredBy,ReviewedTimeInMin=Sum(ROUND(CAST(C1.QATime AS FLOAT)/(CAST(60 AS FLOAT)),4)),
    CodingTimeInMin=Sum(ROUND(CAST(C1.CODINGTIME AS FLOAT)/(CAST(60 AS FLOAT)),4))
    from Inserted C1
           INNER JOIN
           (Select Document_Id,Max(LastModified) As LastModified from Inserted Group By Document_Id) C2
            on C1.Document_ID=C2.Document_ID And C1.LastModified=C2.LastModified 
            group by C1.EnteredBy;

    INSERT INTO #CTE2 (EnteredBy,CodingTimeInHR,ReviewedTimeInHR)
    Select CodingTimeInHR=ROUND(CodingTimeInMin/CAST(60 AS FLOAT), 4) ,
    ReviewedTimeInHR=ROUND(ReviewedTimeInMin/CAST(60 AS FLOAT), 4), EnteredBy
    from #CTE1;

    INSERT INTO #CTE3 (EnteredBy,DocCoded)
    Select C1.EnteredBy,DocCoded=Count(C1.Document_Id)
    from Inserted C1
           INNER JOIN
           (Select Document_Id,Max(LastModified) As LastModified from Inserted Group By Document_Id) C2
            on C1.Document_ID=C2.Document_ID And C1.LastModified=C2.LastModified And C1.Coded=1
            group by C1.EnteredBy;

    INSERT INTO #CTE4 (EnteredBy,DocReviewed)
    Select C1.EnteredBy,DocReviewed=Count(C1.Document_Id)
    from Inserted C1
           INNER JOIN
           (Select Document_Id,Max(LastModified) As LastModified from Inserted Group By Document_Id) C2
            on C1.Document_ID=C2.Document_ID And C1.LastModified=C2.LastModified And C1.Coded=1 AND C1.Revision=1
            group by C1.EnteredBy;

    INSERT INTO #CTE5 (EnteredBy,DocCorrected)
    Select C1.EnteredBy,DocCorrected=Count(C1.Document_Id)
    from Inserted C1
           INNER JOIN
           (Select Document_Id,Max(LastModified) As LastModified from Inserted Group By Document_Id) C2
            on C1.Document_ID=C2.Document_ID And C1.LastModified=C2.LastModified And C1.IsCorrected=1
            group by C1.EnteredBy;

     INSERT INTO #CTE6 (EnteredBy,LastModified)
     Select C1.EnteredBy,LastModified=Max(C2.LastModified)
     from Inserted C1
           INNER JOIN
           (Select Document_Id,Max(LastModified) As LastModified from Inserted Group By Document_Id) C2
            on C1.Document_ID=C2.Document_ID And C1.LastModified=C2.LastModified And C1.Coded=1 AND C1.Revision=1
            group by C1.EnteredBy;


    INSERT INTO #CTE7 (ProjectId,EnteredBy,CODINGRATE,REVIEWRATE,DocCoded,DocReviewed,CodingTimeInMin,ReviewedTimeInMin,DocCorrected,LastModified,PerDocCorrected)
    Select ProjectId='LIO00110', CT3.EnteredBy, CODINGRATE=(CT3.DocCoded/NULLIF(CT2.CODINGTIMEINHR,0)),
    REVIEWRATE=(CT4.DocReviewed/NULLIF(CT2.ReviewedTimeInHR,0)),CT3.DocCoded,CT4.DocReviewed,
    CT1.CodingTimeInMin,CT1.ReviewedTimeInMin,CT5.DocCorrected,CT6.LastModified,
    PerDocCorrected=(Case When CT3.DocCoded>0 Then (Cast(  (ROUND(cast(CT5.DocCorrected AS float)/cast(CT3.DocCoded AS float), 4))*100 As int  ))
    Else 0.0000 END
    )
    From #CTE3 CT3
    Inner Join #CTE2 CT2 on CT3.EnteredBy=CT2.EnteredBy
    Inner Join #CTE4 CT4 on CT3.EnteredBy=CT4.EnteredBy
    Inner Join #CTE1 CT1 on CT3.EnteredBy=CT1.EnteredBy
    Inner Join #CTE6 CT6 on CT3.EnteredBy=CT6.EnteredBy
    Full Join #CTE5 CT5 on CT3.EnteredBy=CT5.EnteredBy;
    
    Select ProjectId,EnteredBy,CODINGRATE,REVIEWRATE,CodingTimeInMin,ReviewedTimeInMin,DocCoded,DocReviewed ,DocCorrected,
    PerDocCorrected,LastModified
    From #CTE7;

    MERGE INTO [SydCoding].[DBO].[ProjectReportOperationDataUserWise] AS target
            USING #CTE7 AS source ON target.ProjectId = source.ProjectId  AND target.UserId = source.EnteredBy
            WHEN MATCHED THEN
                -- Update existing clients
                UPDATE
                SET target.[TotalCoded] = source.DocCoded,
                    target.[TotalReviewed] = source.DocReviewed,
                    target.[CodedTime] = source.CodingTimeInMin,
                    target.[ReviewedTime] = source.ReviewedTimeInMin,
                    target.[CodedRate] = source.CODINGRATE,
                    target.[ReviewedRate] = source.REVIEWRATE,
                    target.[DocCorrected] = source.DocCorrected,
                    target.[PercentDocCorrected] = source.PerDocCorrected,
                    target.[LastModifiedOn] = source.LastModified
            WHEN NOT MATCHED BY target THEN
                -- Insert new data
                INSERT ([ProjectId],[TotalCoded], [TotalReviewed], [CodedTime], [ReviewedTime], [CodedRate], [ReviewedRate], [DocCorrected], 
                [PercentDocCorrected], [UserId], [CreatedOn],[LastModifiedOn])                       
                VALUES (source.ProjectId, source.DocCoded, source.DocReviewed, source.CodingTimeInMin,source.ReviewedTimeInMin, source.CODINGRATE, source.REVIEWRATE, 
                source.DocCorrected, source.PerDocCorrected, source.EnteredBy, GetDate(), source.LastModified)
            ;

        DROP TABLE #CTE1;
        DROP TABLE #CTE2;
        DROP TABLE #CTE3;
        DROP TABLE #CTE4;
        DROP TABLE #CTE5;
        DROP TABLE #CTE6;
        DROP TABLE #CTE7;

    End

What is wrong in my trigger or I am doing something wrong. Please suggest.

4
  • 3
    Where did you read that CTEs are not supported in triggers? Your trigger includes a SELECT statement that returns rows to the client, which might be confusing the API and cause the error. Try removing the query and be aware that returning results in trigger code is deprecated functionality that will be removed from a future release.
    – Dan Guzman
    Nov 21, 2022 at 13:14
  • 1
    Separate and distinct from your issue, I'd avoid using Merge - Aaron Bertrand has a good article on it: mssqltips.com/sqlservertip/3074/… and in general it doesn't make things either clear or faster at runtime. Nov 21, 2022 at 14:39
  • I'm not sure the purpose of this trigger is, but this looks like logic that would be better handled by a stored procedure and denying direct table access for inserts/updates/deletes.
    – user212533
    Nov 21, 2022 at 14:47
  • Thanks @DanGuzman Select Statement was the issue
    – Diya Rawat
    Nov 21, 2022 at 16:09

1 Answer 1

2

The problem is this SELECT statement:

Select ProjectId,EnteredBy,CODINGRATE,REVIEWRATE,CodingTimeInMin,ReviewedTimeInMin,DocCoded,DocReviewed ,DocCorrected,
PerDocCorrected,LastModified
From #CTE7;

The result set returned by the trigger is not expected by the client API so it could not properly determine the number of rows affected. The CREATE TRIGGER documentation specifically warns against returning results in trigger code:

The ability to return results from triggers will be removed in a future version of SQL Server. Triggers that return result sets may cause unexpected behavior in applications that aren't designed to work with them. Avoid returning result sets from triggers in new development work, and plan to modify applications that currently do. To prevent triggers from returning result sets, set the disallow results from triggers option to 1.

Also, there are no restrictions regarding use of CTEs in triggers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.