T-SQL - Trigger: How to select columns that can only be updated and the other columns are not?

[Firedragon]

I'm here for the first time. I will introduce myself. I'm Dennis and I live in the Netherlands. I used "Google Translate" to be able to put my question clearly in English. Hopefully this isn't a problem? And hopefully I'm in a good place to ask my question?

I have a problem with my trigger on Microsoft SQL Server Version 13. The purpose of this trigger is: When a certain study (MRD or Merus, see my trigger below) is closed, the data may not be changed anymore. Only the 8 columns (Vials_BM_Sample_Code, Vials_BM_Amount, Vials_BM_Cells_Per_Vial, Vials_BM_Comments, Vials_PB_Sample_Code, Vials_PB_Amount, Vials_PB_Cells_Per_Vial, Vials_PB_Comments) in a tbl_def_Patients_Materials table are allowed to be updated.

At the moment my trigger on SQL Server works well if one or more columns of eight columns (see above) are updated via database Microsoft Access 2016. Or if I accidentally update the other columns. If I accidentally update these two groups at the same time, my trigger will no longer work as it should. The trigger should also be able to block everything if these two groups are updated at the same time. I hope I described my problem well?

Thank you very much in advance for your effort to read my problem and I hope you can help me with my problem. Enjoy your day!

Greetings from Dennis.

ALTER TRIGGER
       trg_completed_studies_2_prevent_update
ON
       tbl_def_Patients_Materials
FOR UPDATE
AS
/* The data is partially blocked. */
IF EXISTS (
       SELECT
             *
       FROM
             inserted AS ins JOIN tbl_def_Patients AS pat
       ON
             ins.Patient_ID = pat.Patient_ID
       WHERE
             /* The MRD group containing the completed studies. */
             (
                    pat.Patient = 'MRD' AND
                    pat.Study_Name_1 IN (
                           'HOVON42A',
                           'HOVON67',
                           'HOVON81',
                           'HOVON92',
                           'HOVON102',
                           'HOVON103',
                           'HOVON116'
                  
                    ) AND NOT (
                           UPDATE(Vials_BM_Sample_Code) OR UPDATE(Vials_BM_Amount) OR UPDATE(Vials_BM_Cells_Per_Vial) OR UPDATE(Vials_BM_Comments) OR
                           UPDATE(Vials_PB_Sample_Code) OR UPDATE(Vials_PB_Amount) OR UPDATE(Vials_PB_Cells_Per_Vial) OR UPDATE(Vials_PB_Comments)
                    )
             ) OR
             /* The closed Merus group. */
             (
                    pat.Patient IN (
                           'Merus'
                    ) AND NOT (
                           UPDATE(Vials_BM_Sample_Code) OR UPDATE(Vials_BM_Amount) OR UPDATE(Vials_BM_Cells_Per_Vial) OR UPDATE(Vials_BM_Comments) OR
                           UPDATE(Vials_PB_Sample_Code) OR UPDATE(Vials_PB_Amount) OR UPDATE(Vials_PB_Cells_Per_Vial) OR UPDATE(Vials_PB_Comments)
                    )
             )
)
       BEGIN
             RAISERROR('Attention! Only the data of vials of completed studies can be updated!', 16, 1)
             ROLLBACK TRANSACTION
       END
/* The data is completely blocked. */
ELSE IF EXISTS (
       SELECT
             *
       FROM
             inserted AS ins JOIN tbl_def_Patients AS pat
       ON
             ins.Patient_ID = pat.Patient_ID
       WHERE
             /* The other and closed groups. */
             pat.Patient IN (
                    'JandJ'
             )
)
       BEGIN
             RAISERROR('Attention! The records of completed studies may not be updated!', 16, 1)
             ROLLBACK TRANSACTION
       END

 

[Minty]

I think you might be better to use

INSTEAD OF UPDATE
or
COLUMNS_UPDATED

COLUMNS_UPDATED (Transact-SQL) - SQL Server

COLUMNS_UPDATED (Transact-SQL)

learn.microsoft.com

As a trigger mechanism based on those same conditions?

However, depending on the front end to the data (Let's assume Access as this is an Access forum) you could prevent those fields from being able to be edited based on the study type within the form used to enter the data?

 

[WayneRyan]

Dennis

Very brief as I'm on a phone.

Your inserted table is the same as the source table (IF YOU EXIT THE TRIGGER)

Apply your logic to the inserted table;
If no violators exit - all is OK
Else EITHER
1) rollback - cancel ALL rows updated OR
2) use the deleted table to "restore" the original values for only the "bad" rows.

Note: The 2nd option gets tricky because:
A) you can't easily tell the user which bad ones you restored
B) you have to shut off recursive triggers

Hth,
Wayne