PHvEUmE4

1CREATE TABLE table1
2(
3   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
4   AnotherID INT NOT NULL,
5   SomeData VARCHAR(100) NOT NULL
6)
7
8CREATE TABLE table2
9(
10   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
11   AnotherID INT NOT NULL,
12   MoreData VARCHAR(30) NOT NULL,
13
14   CONSTRAINT fk_table2_table1 FOREIGN KEY (AnotherID) REFERENCES table1 (AnotherID)
15) 
16     
17IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_REM_AP_Raum_Reinigung]'))
18ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung DROP CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]
19GO
20
21
22IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fu_Constaint_ValidRmApertureId]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
23DROP FUNCTION [dbo].[fu_Constaint_ValidRmApertureId]
24GO
25
26
27
28
29CREATE FUNCTION [dbo].[fu_Constaint_ValidRmApertureId](
30     @in_RM_ApertureID uniqueidentifier 
31    ,@in_DatumVon AS datetime 
32    ,@in_DatumBis AS datetime 
33    ,@in_Status AS integer 
34) 
35    RETURNS bit 
36AS 
37BEGIN   
38    DECLARE @bNoCheckForThisCustomer AS bit 
39    DECLARE @bIsInvalidValue AS bit 
40    SET @bNoCheckForThisCustomer = 'false' 
41    SET @bIsInvalidValue = 'false' 
42
43    IF @in_Status = 99 
44        RETURN 'false' 
45
46
47    IF @in_DatumVon > @in_DatumBis 
48    BEGIN 
49        RETURN 'true' 
50    END 
51
52
53    IF @bNoCheckForThisCustomer = 'true'
54        RETURN @bIsInvalidValue 
55
56
57    IF NOT EXISTS
58    ( 
59        SELECT 
60             T_Raum.RM_UID 
61            ,T_Raum.RM_Status 
62            ,T_Raum.RM_DatumVon 
63            ,T_Raum.RM_DatumBis 
64            ,T_Raum.RM_ApertureID 
65        FROM T_Raum 
66        WHERE (1=1) 
67        AND T_Raum.RM_ApertureID = @in_RM_ApertureID 
68        AND @in_DatumVon >= T_Raum.RM_DatumVon 
69        AND @in_DatumBis <= T_Raum.RM_DatumBis 
70        AND T_Raum.RM_Status <> 99  
71    ) 
72        SET @bIsInvalidValue = 'true' -- IF ! 
73
74    RETURN @bIsInvalidValue 
75END 
76
77
78
79GO
80
81
82
83IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_REM_AP_Raum_Reinigung]'))
84ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung DROP CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]
85GO
86
87
88-- ALTER TABLE dbo.T_AP_Kontakte WITH CHECK ADD CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]  
89ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung WITH NOCHECK ADD CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] 
90CHECK 
91( 
92    NOT 
93    ( 
94        dbo.fu_Constaint_ValidRmApertureId(ZO_RMREM_RM_UID, ZO_RMREM_GueltigVon, ZO_RMREM_GueltigBis, ZO_RMREM_Status) = 1 
95    ) 
96) 
97GO
98
99
100IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung]') AND parent_object_id = OBJECT_ID(N'[dbo].[T_ZO_REM_AP_Raum_Reinigung]')) 
101ALTER TABLE dbo.T_ZO_REM_AP_Raum_Reinigung CHECK CONSTRAINT [Check_RM_ApertureIDisValid_T_ZO_REM_AP_Raum_Reinigung] 
102GO