60 lines
1.9 KiB
Transact-SQL
60 lines
1.9 KiB
Transact-SQL
-- ==================================================================================================================================
|
|
-- CHECK CONSTRAINTS
|
|
-- ==================================================================================================================================
|
|
ALTER TABLE [dbo].[T_FELHASZNALOBELEPES] DROP CONSTRAINT IF EXISTS [CK_C_GUID]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[T_FELHASZNALOBELEPES] DROP CONSTRAINT IF EXISTS [CK_SoNullValidation]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[T_FELHASZNALOBELEPES] DROP CONSTRAINT IF EXISTS [CK_FELHASZNALOBELEPES_GuidValidation]
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[T_FELHASZNALOBELEPES] DROP CONSTRAINT IF EXISTS [CK_FELHASZNALOBELEPES_PasswordValidation]
|
|
GO
|
|
|
|
IF OBJECT_ID('CK_FelhasznaloBelepes_SoNullValidation', 'C') IS NULL BEGIN
|
|
ALTER TABLE [dbo].[T_FELHASZNALOBELEPES]
|
|
WITH CHECK ADD CONSTRAINT CK_FelhasznaloBelepes_SoNullValidation
|
|
CHECK (NOT([C_JELSZO] IS NOT NULL AND [C_SO] IS NULL AND [C_NEPTUNNAPLOJELSZO] IS NULL))
|
|
END
|
|
GO
|
|
|
|
DROP FUNCTION IF EXISTS dbo.fnGuidValidation
|
|
GO
|
|
|
|
CREATE FUNCTION dbo.fnGuidValidation (
|
|
@loginName nvarchar(36)
|
|
,@intezmenyId int
|
|
,@isTorolt char(1)
|
|
,@GUID nvarchar(72)
|
|
) RETURNS int
|
|
BEGIN
|
|
RETURN (
|
|
SELECT COUNT(DISTINCT C_GUID)
|
|
FROM [dbo].[T_FELHASZNALOBELEPES]
|
|
WHERE C_BEJELENTKEZESINEV = @loginName
|
|
AND C_INTEZMENYID = @intezmenyId
|
|
AND TOROLT='F'
|
|
)
|
|
END;
|
|
GO
|
|
|
|
IF OBJECT_ID('CK_FelhasznaloBelepes_GuidValidation', 'C') IS NULL BEGIN
|
|
ALTER TABLE [dbo].[T_FELHASZNALOBELEPES]
|
|
ADD CONSTRAINT CK_FelhasznaloBelepes_GuidValidation
|
|
CHECK (dbo.fnGuidValidation(C_BEJELENTKEZESINEV, C_INTEZMENYID, TOROLT, C_GUID) <= 1)
|
|
END
|
|
GO
|
|
|
|
|
|
IF OBJECT_ID('CK_FelhasznaloBelepes_PasswordValidation', 'C') IS NULL BEGIN
|
|
ALTER TABLE [dbo].[T_FELHASZNALOBELEPES]
|
|
WITH CHECK ADD CONSTRAINT CK_FelhasznaloBelepes_PasswordValidation
|
|
CHECK (NOT(
|
|
C_JELSZO IS NOT NULL AND
|
|
LEN(C_JELSZO)!=28 AND
|
|
C_JELSZO NOT LIKE '%='
|
|
))
|
|
END
|
|
GO |