Consider the trigger below

ALTER TRIGGER [dbo].[trgUpdateTasks] 
   ON  [dbo].[Tasks] 
   FOR UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    -- Check if job has quotation --
    DECLARE @JobID NUMERIC(18,0)
    DECLARE @QuotationID UNIQUEIDENTIFIER
    SELECT @JobID = INSERTED.JobID FROM INSERTED
    SET @QuotationID = (SELECT TOP 1 QuotationID FROM Jobs WHERE (JobID=@JobID))
    --------------------------------
    IF( NOT (@QuotationID IS NULL) )
    BEGIN
        ROLLBACK TRANSACTION;
    END;
END

The idea is to stop any changes from being done to a Tasks record if it is tied to a Jobs record that has a value in the QuotationID field.

It works because when these conditions are met, the changes are not saved. However, though, an error is returned to the application that causes it to crash. Unfortunately, the application cannot be updated to ignore such an error because it is running on a very old Windows machine that does not allow newer exe's to be installed.

Is there a way to set the trigger to NOT return an error when it does a rollback, so that as far as the application is concerned, the change has been saved normally?

CodePudding user response：

If you use an instead of trigger you only need to update when quotation_id is null. Also, there should be no variables in the trigger as it should allow for multiple rows to be updated at once.

CodePudding user response：

/* *** DDL and Test Data *** */
USE tempdb;
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE dbo.Tasks
(
    TaskID int NOT NULL PRIMARY KEY
    ,JobID int NOT NULL
    ,Task varchar(50) NOT NULL
    ,TaskStart date NOT NULL
    ,TaskEnd date NULL
);
GO
INSERT INTO dbo.Tasks
VALUES (1, 1, 'Task1 Job1', '20210901', '20210902')
    ,(2, 1, 'Task2 Job1', '20210902', NULL)
    ,(3, 2, 'Task1 Job2', '20210903', '20210904')
    ,(4, 2, 'Task2 Job2', '20210905', '20210906')
    ,(5, 3, 'Task1 Job3', '20210910', '20210911')
    ,(6, 3, 'Task2 Job3', '20210915', NULL)
    ,(7, 4, 'Task1 Job4', '20210903', '20210904')
    ,(8, 4, 'Task2 Job4', '20210905', '20210906');
GO
CREATE TABLE dbo.Jobs
(
    JobID int NOT NULL PRIMARY KEY
    ,QuotationID uniqueidentifier NULL
    ,Job varchar(50) NOT NULL
);
GO
INSERT INTO dbo.Jobs
VALUES (1, 'F15EAF03-4F45-40E8-85D6-D89B20E00F38', 'Job1')
    ,(2, NULL, 'Job2')
    ,(3, 'F15EAF03-4F45-40E8-85D6-D89B20E00F38', 'Job3')
    ,(4, '3D74F043-2FAC-4C91-8288-13D71C6558C8', 'Job4');
GO
/* *** End DDL and Test Data *** */

/* *** Create Trigger *** */
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER dbo.Tasks_TR_U
ON dbo.Tasks
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;

    UPDATE T
    SET Task = I.Task
        ,TaskStart = I.TaskStart
        ,TaskEnd = I.TaskEnd
    FROM dbo.Tasks T
        JOIN inserted I
            ON T.TaskID = I.TaskID
    WHERE EXISTS
    -- Only QuotationID of NULL allowed
    (
        SELECT 1
        FROM dbo.Jobs J
        WHERE J.JobID = T.JobID
            AND J.QuotationID IS NULL
    )
        -- Only update if any changes
        AND NOT
        (
            T.Task = I.Task
            AND T.TaskStart = I.TaskStart
            AND ISNULL(T.TaskEnd, '1900') = ISNULL(I.TaskEnd, '1900')
        );
END
GO
/* *** End Create Trigger *** */


/* Check data in tables. */
select * from dbo.Tasks;
select * from dbo.Jobs;

/* Run test */
UPDATE dbo.Tasks
SET Task = Task   ' Updated'

/* Only JobId = 2 should be updated. */
select * from dbo.Tasks;

/* Drop Test Tables
DROP TABLE dbo.Tasks;
DROP TABLE dbo.Jobs;
*/