before update trigger on sql server

Question

Post reply

before update trigger on sql server

albertht1987

SSC Veteran

Points: 268
More actions
April 2, 2020 at 11:11 am
Go to Answer
#3739940
Hi everyone,
I have two tables called users and formerpasswords. In users I have two fields: name which is primary key and password. In formerpasswords I have three fields: number which is primary key, name and password. I would like to create a trigger on users table so if I insert pairs of values in users table for name and password fields and after I update the value for password field where name = some of the inserts I have made in users table, trigger fires and inserts old value of name and password in formerpasswords table. How could I do that in SQL Server? I know that this can be done easily in MySQL using a before update trigger but in SQL Server that doesn't exist so I look for a way to do this.
Thank you so much in advance.
- This topic was modified 4 years, 7 months ago by albertht1987.
Jeff Moden

SSC Guru

Points: 1004339
More actions
April 3, 2020 at 2:34 pm
Answer
#3740227

BWAAA-HAAA!!! Whew! I feel much better about what you're doing now. Thanks for the feedback, Albert.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18842 More actions · Answer 1

You could use an after trigger and insert the values in the deleted pseudo table into your history table.

With SQL2016 and above I would be inclines to make the table versioned:

https://docs.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table?view=sql-server-ver15

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

albertht1987 wrote:

In users I have two fields: name which is primary key and password.

You may have a much larger issue on your hands. Are the passwords in clear text or have they been one-way hashed or ???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

albertht1987 SSC Veteran Points: 268 More actions · Answer 3

It was an example for a course to learn to create triggers not a real database nor real data, the datatype of password field was varchar but if I had to use it in the real world I would use varbinary datatype for passwords field and encrypt them by using the ENCRYPTBYPASSPHRASE function.

This reply was modified 4 years, 7 months ago by albertht1987.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Ok, with that being said, you could home-grow a solution pretty easily but take a look at "System-Versioned Temporal Tables" in SQL Server. I think that might be what you're looking for. Here's the "starter" link for the subject.

https://docs.microsoft.com/en-us/sql/relational-databases/tables/getting-started-with-system-versioned-temporal-tables?view=sql-server-ver15

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)