CREATING TRIGGER WHEN A PARTICULAR COLUMN OF A TABLE IS UPDATED

Question

Hello,

I am looking for a way whereby a trigger will be created ONLY when a particular column of a table is updated. Take for instance, I have a table with about 15 columns including password column. I want a situation whenever password is updated, the former and current password will be inserted in another table which I have already created.

I can create a trigger after the update on a table generally, but in this case, I want only the new table to be updated only when the password column is updated. How do I go about this please?

Answer 1

Lest I forget, I created something like this before:
CREATE TRIGGER afterUpdate_employee_pw
ON employees
AFTER UPDATE
AS
IF UPDATE(pw)
INSERT INTO updatedPW(userid, former_pw, cur_pw, dateRecorded)
SELECT old.userid, old.pw, new.pw, old.pwC, new.pwC, old.dateOFRegistration,
FROM INSERTED new INNER JOIN DELETED OLD ON old.userid = new.userid

---

but haven't tried it due to my program issue which I am resolving now.

Answer 2

Hi @CEO ,

When the employee accesses his record page, he would be eligible to update ONLY the field for password

Maybe you could refer below:

 drop table if exists testusers,pswlog  
       
  create table testusers  
  (userid int,  
  [password] varchar(20),  
  name varchar(20),  
  age int)  
          
  insert into testusers values  
  (1,'password1','Tom',20),  
  (2,'password2','Ann',19),  
  (3,'password3','Jim',22)  
          
  create table pswlog  
  (userid int,  
  oldpassword varchar(20),  
  newpassword varchar(20),  
  updatetime datetime)  

CREATE TRIGGER psw_trigger  
 ON testusers  
 AFTER  UPDATE  
 AS  
 IF ( UPDATE ([password]) )  
 BEGIN  
 insert into pswlog   
 select a.userid,c.password,a.password,getdate() from inserted a  
 inner join testusers b on a.userid=b.userid  
 inner join deleted c on a.userid=c.userid  
 END;  

Perform some actions to make sure the employee could only update password column.

 CREATE ROLE Employee  
 GO  
 CREATE USER EmployeeOne WITHOUT LOGIN;  
 GO   
 EXEC sp_addrolemember @membername = 'EmployeeOne', @rolename = 'Employee';  
 GO  
 GRANT SELECT ON dbo.testusers TO Employee;  
 GO  
 GRANT UPDATE ON dbo.testusers(PASSWORD) TO Employee;  
 GO   

Then make a test.

 EXECUTE AS USER = 'EmployeeOne';  
 GO  
  
 SELECT * FROM dbo.testusers WHERE userid=1;  
 GO   
  
 UPDATE testusers set password='password123' where userid=1  
 GO   

Above is working. You could query this change in the table pswlog.

But if the user would like to update other column, it reports error.

 update  testusers set age=23 where userid=1  
  GO   

Error:

Msg 230, Level 14, State 1, Line 20  
The UPDATE permission was denied on the column 'age' of the object 'testusers', database 'testdb', schema 'dbo'.  

After all, your requirement is a little complex and it may need more actions.Only TSQL statement is not enough.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thank you sooooo much madam @MelissaMa-msft . I am filled with awe at your intelligence and skills.
Definitely I will try to create an app for the password and do some manipulations in it too.
Honestly I truly appreciate your effort in making sure I achieve my desired goal.

I will give you a feedback after my attempt.

Answer 4

Presently, I have a table named updatedRecord_Employees and a trigger is created on the employees table whenever any field is updated, the updatedRecord_Employee table takes record of every column in the record where the update took place whether the column is affected or not (former_col1, current_col1, former_col2, current_col2, etc),

I will still try to create another trigger whereby when only the password column of the employees table is updated, the passwordUpdate table will be triggered to create the record of the new and old data while the employees table gets only the new data. And I would not want the updatedRecord_Employees table to be updated in this case because it is only the password column that was updated.

What do you think please?