Triggers, encrypt function etc...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Triggers, encrypt function etc...

Author

Topic

jackstow
Posting Yak Master

160 Posts

Posted - 2001-09-27 : 10:36:55

OK so I have a trigger for inserts that takes the data from the INSERTED table and places it in another using the encrypt() function along the way.

CREATE TRIGGER insert_encrypted
ON test
FOR INSERT

AS

DECLARE @count int
SELECT @count = COUNT(*) FROM INSERTED

IF @count > 0
BEGIN

INSERT INTO encrypted_test (test_id, cart_id, user_id)
SELECT
encrypt(INSERTED.test_id),
encrypt(INSERTED.cart_id),
encrypt(INSERTED.user_id)

FROM INSERTED

END

..where test_id is PK in both tables. This works fine for a while but every now and then the encrypted numbers get mixed up and are placed in the wrong columns (!?) and the primary key constraint is broken and it all goes horribly wrong. If the encrypt is taken off the encrypted_test.test_id column it works fine. Is this something to do with using encrypt on a primary key..?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources