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

See:
https://www.mssqltips.com/sqlservertip/6076/how-to-find-updated-column-in-sql-server-trigger/

PS. UPDATE() only detects the column was in the UPDATE SET statement. It DOES NOT detect if the value is different than the previous value.

Answer 3

Hi @CEO ,

Please refer below example and check whether it is helpful to you.

create table testusers  
(userid int,  
[password] varchar(20))  
  
insert into testusers values  
(1,'password1'),  
(2,'password2'),  
(3,'password3')  
  
create table pswlog  
(userid int,  
oldpassword varchar(20),  
newpassword varchar(20),  
updatetime datetime)  

Create one trigger on PARTICULAR column.

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;  

Fire this trigger.

update  testusers set password='password11' where userid=1  
update  testusers set password='password111' where userid=1  
update  testusers set password='password22' where userid=2  

Validate the pswlog table.

select * from pswlog  

Output:

userid	oldpassword	newpassword	updatetime  
1	password1	password11	2021-09-14 10:11:41.890  
1	password11	password111	2021-09-14 10:11:46.443  
2	password2	password22	2021-09-14 10:11:53.387  

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 4

Thanks for the guidance. I followed the link and wanted to sign in for only notifications of responses to posts or comments but it seems I would also be sent some other updates which I won't be comfortable with.

Anyway, I just went through your comment. Madam, I saw your code for the testusers table which has only two (2) attributes: userid and password. My question was actually if the example like your testusers table has more than 2 attributes and you want to update only 2 of the attributes and create a trigger ONLY when the two attributes are updated.

Answer 5

I think what I should need is this:

There is a table containing employees records (let me call it Employees table). This table can only be accessed by the HR personnel. They get the record from employees on paper and then fill it into the database table.

username and password are created in the course of filling the table and creating the records for each employee.

The HR personnel gives the employees their username (userID) and password, instructing them to change their password so they would be the only ones who know their password without the knowledge of the HR personnel.

When the employee accesses his record page, he would be eligible to update ONLY the field for password, other fields (attributes or columns) would be "uneditable" by him. I think of creating a small window where only his password and confirmation of the password fields would display for him to type in his new password and for this password to be stored in another table (password table) or somehow connected to the employee table through trigger

Is there a way whereby immediately this password table is updated, a trigger will be created on the password column on the employee table managed by the HR to be updated too? only the column of the employee table in the database.

Do you understand what I'm trying to say here please?