I have a table with a column that I need to restrict who can update that column. I need everyone to be able to read the column, but only certain people to be able to make an update on it. I wrote a TRIGGER that worked great... until I put it into production, at our home office I have about 50 people reading and writing to that table constantly. My trigger quickly began to generate Deadlock and brought our system to a screeching halt. Specifically, I want to restrict who can change data (DML) if the user ID in the inserted table is a certain not a member of a certain group of people and not allow them to write their changes. Below is the Trigger I wrote that was causing the Deadlocks:
CREATE TRIGGER [dbo].[Restrict_Primary_Location_Update]
ON [dbo].[Professional_Location]
INSTEAD OF UPDATE AS
BEGIN
IF (
SELECT i.last_record_update_user
FROM inserted i
INNER JOIN professional_location (NOLOCK) pl ON pl.ProfLoc_Identity = i.ProfLoc_Identity
WHERE i.primary_location <> pl.primary_location
) NOT IN(***-- the group of users that I approve to make changes***)
BEGIN
RAISERROR(N'You have been warned not to alter the primary address from your PDA or in TBO. The address changes you have attempted have been rejected. If you are an outside representative this means that you can no longer sync your PDA until you call IT or undo your changes',16,1)
DECLARE @email varchar(400);
DECLARE @rep_name varchar(200);
DECLARE @message varchar(400);
SET @email = 'etemplet@pamlab.com';
SET @rep_name = (SELECT First_name + ' ' + Last_name FROM dbo.employee e
INNER JOIN inserted i ON i.last_record_update_user = e.emp_id)
SET @message = 'ADDRESS CHANGE ATTEMPT: ' + @rep_name + ' just tried to change an address';
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'TargetBackOfficeAlerts', -- For actual DB
--@profile_name = 'TBOUser', -- For Test DB
@recipients = @email,
@body = @message,
@subject = 'Address Change Attempt'
END
ELSE
BEGIN
UPDATE professional_location
SET
Professional_ID = i.professional_id ,
Location_ID = i.location_id ,
Current_Status_Code = i.Current_Status_Code ,
DEA_Number = i.DEA_Number ,
DEA_Expiration =i.DEA_Expiration ,
DEA_Schedule = i.DEA_Schedule ,
State_License_State = i.State_License_State ,
State_License_Number = i.State_License_Number ,
State_License_Expiration = i.State_License_Expiration ,
Customer_ProfLoc_Number = i.Customer_ProfLoc_Number ,
Alternate_ProfLoc_ID = i.Alternate_ProfLoc_ID ,
Phone = i.phone ,
Fax = i.fax ,
Pager = i.pager ,
Cellular = i.cellular,
Email = i.email,
Gatekeeper_Name = i.Gatekeeper_Name ,
Staff_Name_1 = i.Staff_Name_1 ,
Staff_Name_1_Notes = i.Staff_Name_1_Notes ,
Staff_Name_2 = i.Staff_Name_2 ,
Staff_Name_2_Notes =i.Staff_Name_2_Notes ,
Validation_Date =i.Validation_Date ,
Validation_Source = i.Validation_Source ,
Primary_Location = i.Primary_Location ,
Profloc_Effective_Date = i.Profloc_Effective_Date ,
Validation_Status = i.Validation_Status,
Validation_Reason = i.Validation_Reason ,
Validation_Eligibility = i.Validation_eligibility ,
SLN_On_File = i.SLN_On_File ,
SLN_On_File_SFA_notif_Sent = i.SLN_On_File_SFA_notif_Sent ,
SL_Val_Status_SFA_notif_Sent = i.SL_Val_Status_SFA_notif_Sent ,
SLN_Validation_Sent_Date = i.SLN_Validation_Sent_Date ,
SLN_With_Rep = i.SLN_With_Rep ,
SLN_With_Rep_Date = i.SLN_With_Rep_Date ,
Department_ID = i.Department_ID ,
Rx_Sampling_Count = i.Rx_Sampling_Count ,
Initial_Load_Status = i.Initial_Load_Status ,
Inactivation_Reason = i.Inactivation_Reason ,
Payment_Terms = i.Payment_Terms ,
Credit_Hold = i.Credit_Hold ,
Order_Limit = i.Order_Limit ,
Tax_Exempt = i.Tax_Exempt ,
Taxpayer_ID = i.Taxpayer_ID ,
Bill_To_Organization_Name = i.Bill_To_Organization_Name ,
Bill_To_Address1 = i.Bill_To_Address1 ,
Bill_To_Address2 = i.Bill_To_Address2 ,
Bill_To_City = i.Bill_To_City ,
Bill_To_State =i.Bill_To_State ,
Bill_To_Zip = i.Bill_To_Zip ,
Bill_To_Country = i.Bill_To_Country ,
Bill_To_Attention = i.Bill_To_Attention ,
On_File_CC_Type = i.On_File_CC_Type ,
On_File_CC_Number = i.On_File_CC_Number ,
On_File_CC_Exp_Month = i.On_File_CC_Exp_Month ,
On_File_CC_Exp_Year = i.On_File_CC_Exp_Year ,
On_File_CC_Cardholder_Name = i.On_File_CC_Cardholder_Name ,
Blanket_PO_Number = i.Blanket_PO_Number ,
Operator_Note = i.Operator_Note ,
OE_Extra1 = i.OE_Extra1 ,
OE_Extra2 = i.OE_Extra2 ,
OE_Extra3 = i.OE_Extra3 ,
On_File_CC_Security_Code =i.On_File_CC_Security_Code ,
DEA_Sched_1 = i.DEA_Sched_1 ,
DEA_Sched_2 = i.DEA_Sched_2 ,
DEA_Sched_2N = i.DEA_Sched_2N ,
DEA_Sched_3 = i.DEA_Sched_3 ,
DEA_Sched_3N = i.DEA_Sched_3N ,
DEA_Sched_4 =i.DEA_Sched_4,
DEA_Sched_5 = i.DEA_Sched_5 ,
DDD_Outlet_Number = i.DDD_Outlet_Number ,
State_License_Status_Desc = i.State_License_Status_Desc ,
Sampleability_License_Status =i.Sampleability_License_Status ,
Sampleability_Expiration = i.Sampleability_Expiration ,
State_Grace_Period = i.State_Grace_Period ,
Adjudication_Sampleability = i.Adjudication_Sampleability ,
Adjudication_Code_Desc = i.Adjudication_Code_Desc ,
State_Lic_Import_Comment = i.State_Lic_Import_Comment ,
website_address = i.website_address,
record_create_user = i.record_create_user,
record_create_Date = i.record_create_Date,
last_record_update_user = i.last_record_update_user,
last_record_update_date = i.last_record_update_date,
r_save_timestamp = i.r_save_timestamp
FROM inserted i
INNER JOIN professional_location pl ON pl.ProfLoc_Identity = i.ProfLoc_Identity
END
END