Post reply

Newbie Trigger Question

  • January 15, 2003 at 12:28 pm

    #158811

    I am in the process of encrypting passwords.

    I have some existing ASP pages that basically authenticate a user, and allow them access to the app.

    At this point in time I have the passwords stored as clear text.

    I am using the XP_Crypt Functions to encrypt the passwords.

    Without rewriting my entire authentication routine, I wanted to encrypt the passwords when they are inserted into the table. I wrote trigger that fires on insert/update, the problem is that the trigger affects all rows when I update a single row. (This would be fired by an admin or user resetting their password.) It works correctly when inserting. When updating it essentially is resetting all the passwords except the updated one.

    CREATE TRIGGER encryptpassword ON dbo.sampleAuth

    FOR INSERT, UPDATE

    AS

    Update dbo.sampleAuth Set dbo.sampleAuth.rsaPassword = dbo.ud_pwEncrypt(dbo.sampleAuth.rsaPassword,Null)

    From Inserted

    Where dbo.sampleAuth.sampleAuthID = @@Identity

    return

    I hope this makes sense. I have also tried removing the UPDATE and writing it separately, that does not work either.

  • January 15, 2003 at 1:25 pm

    #446418

    Do a comparison between the inserted and the original table. Such as:

    

    UPDATE dbo.sampleAuth 

    SET rsaPassword = dbo.ud_pwEncrypt(dbo.sampleAuth.rsaPassword,,NULL)

    FROM Inserted I, dbo.sampleAuth sAuth

    WHERE sAuth.sampleAuthID = I.sampleAuthID

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

Viewing 2 posts - 1 through 1 (of 1 total)

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