June 27, 2019 at 5:26 am
Hi,
I have an application pushing data from source SQL database to an external SQL database. I have only access to the external (destination) database.
I want to make sure there is no valid credit card number in the external database, how can I achieve this in the external database?
I initially thought I could create an AFTER INSERT trigger, but then I read that triggers cannot modify the changed data (Inserted or Deleted) otherwise you could get infinite recursion as the changes invoked the trigger again.
Triggers cannot modify the changed data: https://stackoverflow.com/questions/405288/sql-server-after-insert-trigger-doesnt-see-the-just-inserted-row
My request is similar to this: https://stackoverflow.com/questions/3580123/how-can-i-edit-values-of-an-insert-in-a-trigger-on-sql-server
Thoughts ?
June 27, 2019 at 10:58 am
I would check "instead of insert" trigger and modify card numbers there
https://blog.sqlauthority.com/2013/01/24/sql-server-use-instead-trigger/
OR
check if Dynamic Data Masking is suitable solution
https://www.sqlshack.com/using-dynamic-data-masking-in-sql-server-2016-to-protect-sensitive-data/
June 27, 2019 at 11:16 am
Honestly, I would want this change to occur before it ever gets to your database; it shouldn't be part of the INSERT. Even if you're replacing the value with an INSTEAD OF trigger, someone with permissions to perform a trace or create an extended event could intercept those values as they are inserted.
The anonymisation really needs to happen before the INSERT.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply