55 lines
1.3 KiB
Transact-SQL
55 lines
1.3 KiB
Transact-SQL
IF OBJECT_ID('dbo.getRand', 'V') IS NOT NULL BEGIN
|
|
DROP VIEW dbo.getRand;
|
|
END;
|
|
GO
|
|
|
|
CREATE VIEW dbo.getRand AS
|
|
SELECT RAND(CHECKSUM(NEWID())) AS newId
|
|
GO
|
|
|
|
DISABLE trigger TR_FELHASZNALOBELEPESLOG ON T_FELHASZNALOBELEPES
|
|
|
|
IF OBJECT_ID('dbo.fnConvertBase64') IS NOT NULL BEGIN
|
|
DROP FUNCTION dbo.fnConvertBase64
|
|
END
|
|
GO
|
|
|
|
CREATE FUNCTION dbo.fnConvertBase64 (@source VARBINARY(MAX))
|
|
RETURNS NVARCHAR(64)
|
|
BEGIN
|
|
RETURN CAST('' AS XML).value('xs:base64Binary(sql:variable(''@source''))', 'varchar(max)')
|
|
END;
|
|
GO
|
|
|
|
GO
|
|
UPDATE fb
|
|
SET
|
|
fb.C_JELSZO = src.pwValue,
|
|
fb.C_SO = src.randomValue
|
|
FROM [dbo].[T_FELHASZNALOBELEPES] AS fb
|
|
INNER JOIN (SELECT
|
|
felhasznalobelepes.ID,
|
|
dbo.fnConvertBase64(HASHBYTES('SHA1', CONVERT(VARBINARY(MAX), CONVERT(NVARCHAR(MAX), felhasznalobelepes.C_JELSZO+RND)))) AS pwValue
|
|
,RND AS randomValue
|
|
FROM
|
|
(SELECT
|
|
ID,
|
|
C_NEPTUNNAPLOJELSZO,
|
|
C_SO,
|
|
C_JELSZO,
|
|
CAST((SELECT * FROM dbo.getRand) AS nvarchar(64)) AS RND
|
|
FROM dbo.T_FELHASZNALOBELEPES
|
|
WHERE
|
|
C_NEPTUNNAPLOJELSZO IS NULL AND
|
|
C_SO IS NULL AND
|
|
C_JELSZO IS NOT NULL) AS felhasznalobelepes) src ON fb.ID = src.ID
|
|
|
|
GO
|
|
|
|
IF OBJECT_ID('dbo.fnConvertBase64') IS NOT NULL BEGIN
|
|
DROP FUNCTION dbo.fnConvertBase64
|
|
END
|
|
|
|
GO
|
|
|
|
ENABLE trigger TR_FELHASZNALOBELEPESLOG ON T_FELHASZNALOBELEPES |