· 9 years ago · Oct 03, 2016, 11:10 AM
1
2/****** Object: Table [dbo].[calls] Script Date: 03.10.2016 14:04:54 ******/
3SET ANSI_NULLS ON
4GO
5
6SET QUOTED_IDENTIFIER ON
7GO
8
9CREATE TABLE [dbo].[calls](
10 [call_id] [int] NOT NULL,
11 [caller_id] [int] NULL,
12 [callee_id] [int] NULL,
13 [duration] [int] NOT NULL,
14 CONSTRAINT [PK_calls] PRIMARY KEY CLUSTERED
15(
16 [call_id] ASC
17)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
18)
19
20GO
21
22ALTER TABLE [dbo].[calls] WITH NOCHECK ADD CONSTRAINT [Callee] FOREIGN KEY([callee_id])
23REFERENCES [dbo].[customer] ([customer_id])
24GO
25
26ALTER TABLE [dbo].[calls] CHECK CONSTRAINT [Callee]
27GO
28
29ALTER TABLE [dbo].[calls] WITH NOCHECK ADD CONSTRAINT [Caller] FOREIGN KEY([caller_id])
30REFERENCES [dbo].[customer] ([customer_id])
31GO
32
33ALTER TABLE [dbo].[calls] CHECK CONSTRAINT [Caller]
34GO
35
36ALTER TABLE [dbo].[calls] WITH CHECK ADD CONSTRAINT [Call_Duration] CHECK (([duration]>(0)))
37GO
38
39ALTER TABLE [dbo].[calls] CHECK CONSTRAINT [Call_Duration]
40GO
41
42USE [course-db]
43GO
44
45/****** Object: Table [dbo].[calls] Script Date: 03.10.2016 14:04:54 ******/
46SET ANSI_NULLS ON
47GO
48
49SET QUOTED_IDENTIFIER ON
50GO
51
52CREATE TABLE [dbo].[calls](
53 [call_id] [int] NOT NULL,
54 [caller_id] [int] NULL,
55 [callee_id] [int] NULL,
56 [duration] [int] NOT NULL,
57 CONSTRAINT [PK_calls] PRIMARY KEY CLUSTERED
58(
59 [call_id] ASC
60)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
61)
62
63GO
64
65ALTER TABLE [dbo].[calls] WITH NOCHECK ADD CONSTRAINT [Callee] FOREIGN KEY([callee_id])
66REFERENCES [dbo].[customer] ([customer_id])
67ON UPDATE CASCADE
68ON DELETE SET NULL
69GO
70
71ALTER TABLE [dbo].[calls] CHECK CONSTRAINT [Callee]
72GO
73
74ALTER TABLE [dbo].[calls] WITH NOCHECK ADD CONSTRAINT [Caller] FOREIGN KEY([caller_id])
75REFERENCES [dbo].[customer] ([customer_id])
76ON UPDATE CASCADE
77ON DELETE SET NULL
78GO
79
80ALTER TABLE [dbo].[calls] CHECK CONSTRAINT [Caller]
81GO
82
83ALTER TABLE [dbo].[calls] WITH CHECK ADD CONSTRAINT [Call_Duration] CHECK (([duration]>(0)))
84GO
85
86ALTER TABLE [dbo].[calls] CHECK CONSTRAINT [Call_Duration]
87GO
88
89
90CREATE TRIGGER [dbo].[Same_Operator] ON [dbo].[customer]
91 AFTER INSERT, UPDATE
92AS
93BEGIN
94 IF EXISTS(
95 SELECT * FROM inserted
96 INNER JOIN
97 (
98 SELECT
99 LEFT(phone_number, 3) AS Ext,
100 MIN(provider) AS Prv
101 FROM customer
102 GROUP BY LEFT(phone_number, 3)
103 ) Dict
104 ON Dict.Ext = LEFT(inserted.phone_number, 3)
105 WHERE Dict.Prv != inserted.provider
106 )
107 BEGIN
108 ROLLBACK TRANSACTION
109 END
110END
111GO
112
113CREATE TRIGGER [dbo].[Same_Operator] ON [dbo].[customer]
114 AFTER INSERT, UPDATE
115AS
116BEGIN
117 IF EXISTS(
118 SELECT * FROM inserted
119 INNER JOIN
120 (
121 SELECT
122 LEFT(phone_number, 3) AS Ext,
123 MIN(provider) AS Prv
124 FROM customer
125 GROUP BY LEFT(phone_number, 3)
126 ) Dict
127 ON Dict.Ext = LEFT(inserted.phone_number, 3)
128 WHERE Dict.Prv != inserted.provider
129 )
130 BEGIN
131 ROLLBACK TRANSACTION
132 END
133END
134GO