Post reply

need help w creating trigger.....

  • January 4, 2012 at 9:47 am

    #262357

    need to create trigger that is relative to the data in the record being inserted or updated

  • January 4, 2012 at 9:47 am

    #1428459

    sorry. having trouble getting the rest of my scripts in here....

    CREATE TABLE [dbo].[tbl1](

    [desc] [nvarchar](100) NOT NULL,

    [encryptedValue] [nvarchar](100) NOT NULL)

  • January 4, 2012 at 9:48 am

    #1428460

    So where are you getting stuck?

    http://msdn.microsoft.com/en-us/library/ms189799.aspx

  • January 4, 2012 at 9:48 am

    #1428461

    insert into [dbo].[tbl1]( [desc], [encryptedValue] )

    values ( 'UserOne','UserOneEncrypt')

  • January 4, 2012 at 9:48 am

    #1428464

    --So this would insert a record w/ the below

    --[desc] = UserOne

    --[encryptedValue] = UserOneEncrypt

    --How can i create a trigger so when the record is inserted or updated the passord is encrypted using

    --the EncryptByPassPhrase([desc]-"this is the value that was just inserted or updated", [encryptedValue]-"this valuse that was just inserted or updated) function in sql 2005.

    --So the [encryptedValue] should actually be set to 0x01000000953F8BB269DF9BEA0A060005EBA64DD8088391413F47729DBB8D3754F4AA8D7B

    --select EncryptByPassPhrase('UserOne','UserOneEncrypt')

  • January 4, 2012 at 9:49 am

    #1428465

    odd. i couldnt post the whole thing at once. had to do it in chuncks......very strange

  • January 4, 2012 at 10:01 am

    #1428486

    Point 1. Don't encrypt passwords. They shouldn't be encrypted because they should never be decrypted. They should be stored hashed (salted hash). Encrypting by passphrase and storing the passphrase in the trigger is almost a waste of time. It means anyone that can get the definition of the trigger can decrypt all passwords.

    Table definition please (the entire definition) and some sample data

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 4, 2012 at 10:02 am

    #1428492

    here's a full working example; like gail said, don't use this for passwords, only for preventing casual review of the raw data:

    CREATE TABLE [dbo].[tbl1](

    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    [desc] [nvarchar](100) NOT NULL,

    [UnencryptedValue] [nvarchar](100) NOT NULL,

    [encryptedValue] varbinary(max) NULL)

    GO

    CREATE TRIGGER TR_TBL1 ON tbl1

    FOR INSERT,UPDATE

    AS

    BEGIN

    UPDATE tbl1

    SET UnencryptedValue='--',

    encryptedValue = EncryptByPassPhrase(INSERTED.[desc],INSERTED.UnencryptedValue)

    FROM INSERTED

    WHERE tbl1.ID = INSERTED.ID

    END --TRIGGER

    --insert a row:

    INSERT INTO tbl1 ([desc],UnencryptedValue)

    SELECT 'UserOne','UserOneEncrypt'

    --see the ecryption:

    SELECT * FROM tbl1

    --decrypt it:

    SELECT [desc],

    convert(nvarchar(100),DecryptByPassPhrase([desc],encryptedValue)) AS DecryptedValue

    FROM tbl1

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 8 posts - 1 through 7 (of 7 total)

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