question

CEO-8149 avatar image
0 Votes"
CEO-8149 asked ErlandSommarskog commented

CREATING TRIGGER WHEN A PARTICULAR COLUMN OF A TABLE IS UPDATED

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?

sql-server-transact-sqlazure-sql-database
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CEO-8149 avatar image
0 Votes"
CEO-8149 answered CEO-8149 edited

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

That looks like the way to do it.

One thing, though, if not related to the trigger. I hope that you are not actually storing the password, but only a salted hash of the same. I get a little nervous when you save the old password, but I assume that this for checking that the user does not reuse a recent password. Anyway, as long as you don't store it in cleartext, that's OK.

If you were to use stored procedure, it would be a simpler, because them you would expose a stored procedure EmployChangePassword which would also save the data into the history table.

0 Votes 0 ·
CEO-8149 avatar image CEO-8149 ErlandSommarskog ·

I have fixed it right. I mean my most recent request on how to update the remaining 18 columns and set a trigger:

CREATE TRIGGER after_update_on_employees
ON Employees
AFTER UPDATE
AS IF
NOT UPDATE(pw)
AND
NOT UPDATE(pwc)
AND
NOT UPDATE(pwsalt)
AND
NOT UPDATE(pwkey)
INSERT INTO tablename()
SELECT old.columns, new.columns
FROM INSERTED ....


This helps me.
Thanks for your time

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft commented

Hi @CEO-8149,

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

hello @MelissaMa-msft thank you very much for this lengthy and insightful tutorial, I will try it. I would have responded yesterday but I have been having an issue to resolve in the program application I am developing. Hopefully when I fix it, I shall come back to this. I also have another idea and will be asking you a question then.

For now, thank you very much and please stay in touch.

0 Votes 0 ·

Hi @CEO-8149,

Thanks for your update.

Please let me know whether above is working once you fixed the issue.

If it is not working, please provide your table structure, some sample data and expected output after firing the trigger.

Best regards,
Melissa

0 Votes 0 ·
CEO-8149 avatar image CEO-8149 MelissaMa-msft ·

thank you so much for your concern. Definitely, I will do so. But I don't get email notification of responses to my post or comments. That would have been better and made it easier for me to easy visit any page by following the mail thread. Now, I come to my browser history to find this page

0 Votes 0 ·
Show more comments
CEO-8149 avatar image
0 Votes"
CEO-8149 answered ErlandSommarskog commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

That should not be any problem. Below are my settings, and I only set the mail I want, not any irrelevant junk. Since you might mainly be in the forum only to ask questions, and I am here to answer, you may not want to have exactly the same setting, but I dump these as an example. Having notifications on makes it a lot easier to follow what is going on.


133272-clipboard01.jpg

133262-clipboard02.jpg


0 Votes 0 ·
clipboard01.jpg (46.3 KiB)
clipboard02.jpg (30.1 KiB)
CEO-8149 avatar image
0 Votes"
CEO-8149 answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @CEO-8149,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CEO-8149 avatar image
0 Votes"
CEO-8149 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CEO-8149 avatar image
0 Votes"
CEO-8149 answered ErlandSommarskog commented

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?

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @CEO-8149,

It sounds like working.

You could have a try in a testing environment firstly once issue fixed.

Please let us know if any update.

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
CEO-8149 avatar image CEO-8149 MelissaMa-msft ·

Hello madam @MelissaMa-msft , thank you.
I have set my email notifications and also fixed my program issues, so I have resolved the password table trigger. Actually my table has 22 columns (attributes), 4 of them have to do with the password (encryption features).

Now, having created a table for updatedPasswords (old pw and new pw), is there a way I can create a trigger if any out of the remaining 18 columns is updated? Remember, the total columns is 22, I have used 4 for the password update, I am left with 18.

I want to create a table for updatedRecord which would contain old and new details of each of the 18 columns but how do I create the trigger such that the table is updated ONLY when one or more of the remaining 18 columns (excluding password and its encryption features columns) is updated?

0 Votes 0 ·

I have fixed it right. I mean my most recent request on how to update the remaining 18 columns and set a trigger:

CREATE TRIGGER after_update_on_employees
ON Employees
AFTER UPDATE
AS IF
NOT UPDATE(pw)
AND
NOT UPDATE(pwc)
AND
NOT UPDATE(pwsalt)
AND
NOT UPDATE(pwkey)
INSERT INTO tablename()
SELECT old.columns, new.columns
FROM INSERTED ....


This helps me.
Thanks for your time

0 Votes 0 ·
Show more comments