0

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.

  • 1
    Is there a reason why you can't do that in an AFTER trigger, using the DELETED virtual table in your trigger code? – Tibor Karaszi Apr 2 at 10:42
  • I'm not an SQL Server expert, I'm doing a course and i'm not sure how that code would be in that case so i was looking for someone who showed me how that code would be and that explained me why is this code used in that way – Albert Hernández Torné Apr 2 at 10:50
  • finally I used this code and it works: create trigger befo_usuarios_update on users AFTER UPDATE as if update(password) begin insert into formerpasswords(name, password) (select name, password from deleted where name = deleted.name); end; – Albert Hernández Torné Apr 2 at 13:52
0

Here is a functional (albeit basic) example for you. Triggers in SQL Server can be either AFTER or INSTEAD and can apply to any DML (INSERT, UPDATE or DELETE), even at the same time. Inside the scope of each trigger is a virtual table called:

  • INSERTED - what is intended to be.
  • DELETED - what the old values were.

These virtual tables contain every column from the base table and make modification to either the base table or other tables.

CAVEATS (or basic reminders):

  • MAKE SURE that the statement inside your trigger can handle multiple rows properly. A common mistake that I see is people making the logic inside the trigger only work for one row at a time.
  • These can kill performance, use them sparingly and keep the logic as simple as possible.
  • Bulk Inserts do not typically fire triggers, but can be configured to do so.
  • AVOID cursors inside triggers.
  • You cannot try/catch inside a trigger. Any exception that happens in there WILL bubble out to the caller.

FINALLY

Take care storing the passwords using a proper cryptographic library. I used VARCHAR in my example below to better illustrate the trigger logic.


DROP TABLE IF EXISTS dbo.FormerPasswords 
DROP TABLE IF EXISTS dbo.Users 

GO

CREATE TABLE dbo.Users
    (
    UsersID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , UserName NVARCHAR(200) NOT NULL
    , CurrentPassword VARCHAR(64) NOT NULL
    )

CREATE TABLE dbo.FormerPasswords
    (
    FormerPasswords INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , UsersID INT NOT NULL REFERENCES dbo.Users (UsersID)
    , PriorPasswords VARCHAR(64) NOT NULL
    , DateLastUsed_UTC DATETIME2(7) NOT NULL DEFAULT (SYSUTCDATETIME())
    )

GO

CREATE OR ALTER TRIGGER trgUsers 
    ON dbo.Users
    AFTER UPDATE
AS
BEGIN

    INSERT INTO dbo.FormerPasswords
    (UsersID, PriorPasswords)
    SELECT D.UsersID
        , D.CurrentPassword
    FROM DELETED AS D

END

GO

INSERT INTO dbo.Users 
(UserName, CurrentPassword)
VALUES ('jsmith', 'original 1')
    , ('jdoe', 'original 2')

UPDATE dbo.Users 
SET CurrentPassword = 'updated 1'
WHERE UserName = 'jsmith'

SELECT * FROM dbo.Users 
SELECT * FROM dbo.FormerPasswords
| improve this answer | |
0

Try something like this:


alter trigger demotrigger on table1 for update as
begin
    select * into #tmp from deleted;
    insert into table2
    select * from #tmp where #tmp.name not in (select name from table2)

    update table2 set oldpass=#tmp.pass
    from #tmp 
    join table2  on table2.name=#tmp.name

    drop table #tmp;
end
| improve this answer | |
0

finally I used this code and it works:

create trigger befo_usuarios_update on users AFTER UPDATE as if update(password) begin insert into formerpasswords(name, password) (select name, password from deleted where name = deleted.name); end;

| improve this answer | |
  • Of course, you don't actually /mean/ "password" do you? You /mean/ the one-way hash of the User's entered password that cannot be recovered that you're storing in a field that just happens to be called "password". Never store passwords in plain text. – Phill W. Apr 2 at 14:10
  • it was only part of a course to learn how to create triggers, not a real database nor real data. if I had to that, I obviously would use them encrypted by using the ENCRYPTBYPASSPHRASE function that I already know how to use. – Albert Hernández Torné Apr 2 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.