July 30, 2019 at 6:58 am
Can we have add Hash-byte on column for only insert operation not for update operation ?
July 30, 2019 at 9:50 am
Any chance you could clarify your question? I don't really follow what you are asking for.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 30, 2019 at 12:21 pm
Thank You @Erland,
We have a table "employee_table" in which i want to add a column known as "HashValue" and "HashValue" column will be populated using HashByte function.
but we want to update HashValue Column only if we have new records inserted not on if existing records gets updated.
I am using below code but this code needs to run manually which is not acceptable.
UPDATE employee_table
SET row_value = (select hashbytes('SHA2_256', (select first_name, last_name for xml raw)))
where row_value is null
July 30, 2019 at 12:38 pm
I am still not sure that I understand. If you don't want to compute the hash value on update, just don't run that UPDATE statement. Or what am I missing?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 30, 2019 at 4:54 pm
Use an INSERT trigger to add the hashed column.
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER employee_table__TR_INS
ON dbo.employee_table
AFTER INSERT
AS
SET NOCOUNT ON;
UPDATE et
SET row_value = HASHBYTES('SHA2_256', first_name + last_name)
FROM dbo.employee_table et
INNER JOIN inserted i ON et.$IDENTITY = i.$IDENTITY
WHERE et.row_value is null
/*end of trigger*/
GO
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply