﻿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