Учебник_2023: Скрипт учебной БД "Абоненты" | РГРТУ (направления: 09.03.02, 09.04.02)

CREATE DATABASE [ABONENT_MSSQL]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'16', FILENAME = N'C:\835\16.mdf' , SIZE = 2048 , MAXSIZE = 4096, FILEGROWTH = 10%)
LOG ON
( NAME = N'16_log', FILENAME = N'C:\835\16.ldf' , SIZE = 1024KB , MAXSIZE = 4096KB , FILEGROWTH = 10%)
GO
USE [ABONENT_MSSQL]
GO
SET DATEFIRST 7
GO

/*****************************************************************/
/* TYPES */
/*****************************************************************/
CREATE TYPE [dbo].[TYEAR] FROM [smallint] NOT NULL
GO
CREATE TYPE [dbo].[CASH] FROM [numeric](15, 2) NOT NULL
GO
CREATE TYPE [dbo].[PKFIELD] FROM [int] NOT NULL
GO
CREATE TYPE [dbo].[TMONTH] FROM [smallint] NOT NULL
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

/*****************************************************************/
/* Exceptions */
/*****************************************************************/
/*Удаляем пользовательское сообщение, если оно было создано ранее*/
EXEC sp_dropmessage 50001;
/*И добавляем пользовательское сообщение*/
EXEC sys.sp_addmessage @msgnum = 50001
,@severity = 16
,@msgtext = 'Ограничение удаления записи из родительской таблицы'
,@lang = 'us_english'
,@with_log = TRUE;
GO

/*****************************************************************/
/* Tables */
/*****************************************************************/
CREATE TABLE [dbo].[ABONENT](
[ACCOUNTCD] [varchar](6) COLLATE Cyrillic_General_CI_AS NOT NULL,
[STREETCD] [dbo].[PKFIELD] NULL,
[HOUSENO] [smallint] NULL,
[FLATNO] [smallint] NULL,
[Fio] [varchar](20) COLLATE Cyrillic_General_CS_AS,
[PHONE] [varchar](15) COLLATE Cyrillic_General_CI_AS NULL,
CONSTRAINT [PK_ABONENT] PRIMARY KEY CLUSTERED
(
[ACCOUNTCD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[DISREPAIR] Script Date: 23.09.2017 20:44:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DISREPAIR](
[FAILURECD] [dbo].[PKFIELD] NOT NULL,
[FAILURENM] [varchar](50) COLLATE Cyrillic_General_CS_AS,
CONSTRAINT [PK_DISREPAIR] PRIMARY KEY CLUSTERED
(
[FAILURECD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[EXECUTOR] Script Date: 23.09.2017 20:44:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EXECUTOR](
[EXECUTORCD] [dbo].[PKFIELD] NOT NULL,
[Fio] [varchar](20) COLLATE Cyrillic_General_CS_AS,
CONSTRAINT [PK_EXECUTOR] PRIMARY KEY CLUSTERED
(
[EXECUTORCD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[NACHISLSUMMA] Script Date: 23.09.2017 20:44:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NACHISLSUMMA](
[NACHISLFACTCD] [dbo].[PKFIELD] NOT NULL,
[ACCOUNTCD] [varchar](6) NOT NULL,
[SERVICECD] [dbo].[PKFIELD] NOT NULL,
[NACHISLSUM] [dbo].[CASH] NULL,
[NACHISLMONTH] [dbo].[TMONTH] NULL,
[NACHISLYEAR] [dbo].[TYEAR] NULL,
CONSTRAINT [PK_NACHISLSUMMA] PRIMARY KEY CLUSTERED
(
[NACHISLFACTCD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[PAYSUMMA] Script Date: 23.09.2017 20:44:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PAYSUMMA](
[PAYFACTCD] [dbo].[PKFIELD] NOT NULL,
[ACCOUNTCD] [varchar](6) NOT NULL,
[SERVICECD] [dbo].[PKFIELD] NOT NULL,
[PAYSUM] [dbo].[CASH] NULL,
[PAYDATE] [date] NULL,
[PAYMONTH] [dbo].[TMONTH] NULL,
[PAYYEAR] [dbo].[TYEAR] NULL,
CONSTRAINT [PK_PAYSUMMA] PRIMARY KEY CLUSTERED
(
[PAYFACTCD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[REQUEST] Script Date: 23.09.2017 20:44:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[REQUEST](
[REQUESTCD] [dbo].[PKFIELD] NOT NULL,
[ACCOUNTCD] [varchar](6) NULL,
[EXECUTORCD] [dbo].[PKFIELD] NULL,
[FAILURECD] [dbo].[PKFIELD] NULL,
[INCOMINGDATE] [date] DEFAULT GETDATE() NOT NULL,
[EXECUTIONDATE] [date] NULL,
[EXECUTED] [bit] DEFAULT 0 NOT NULL,

CONSTRAINT [PK_REQUEST] PRIMARY KEY CLUSTERED
(
[REQUESTCD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[SERVICES] Script Date: 23.09.2017 20:44:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SERVICES](
[SERVICECD] [dbo].[PKFIELD] NOT NULL,
[SERVICENM] [varchar](30) COLLATE Cyrillic_General_CS_AS,
CONSTRAINT [PK_SERVICES] PRIMARY KEY CLUSTERED
(
[SERVICECD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[STREET] Script Date: 23.09.2017 20:44:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[STREET](
[STREETCD] [dbo].[PKFIELD] NOT NULL,
[STREETNM] [varchar](30) COLLATE Cyrillic_General_CS_AS,
CONSTRAINT [PK_STREET] PRIMARY KEY CLUSTERED
(
[STREETCD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ABONENT] WITH CHECK ADD CONSTRAINT [FK_ABONENT_STREET] FOREIGN KEY([STREETCD])
REFERENCES [dbo].[STREET] ([STREETCD])
ON UPDATE CASCADE
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[ABONENT] CHECK CONSTRAINT [FK_ABONENT_STREET]
GO
ALTER TABLE [dbo].[NACHISLSUMMA] WITH CHECK ADD CONSTRAINT [FK_NACHISLSUMMA_ABONENT] FOREIGN KEY([ACCOUNTCD])
REFERENCES [dbo].[ABONENT] ([ACCOUNTCD])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[NACHISLSUMMA] CHECK CONSTRAINT [FK_NACHISLSUMMA_ABONENT]
GO
ALTER TABLE [dbo].[NACHISLSUMMA] WITH CHECK ADD CONSTRAINT [FK_NACHISLSUMMA_SERVICES] FOREIGN KEY([SERVICECD])
REFERENCES [dbo].[SERVICES] ([SERVICECD])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[NACHISLSUMMA] CHECK CONSTRAINT [FK_NACHISLSUMMA_SERVICES]
GO
ALTER TABLE [dbo].[PAYSUMMA] WITH CHECK ADD CONSTRAINT [FK_PAYSUMMA_ABONENT1] FOREIGN KEY([ACCOUNTCD])
REFERENCES [dbo].[ABONENT] ([ACCOUNTCD])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[PAYSUMMA] CHECK CONSTRAINT [FK_PAYSUMMA_ABONENT1]
GO
ALTER TABLE [dbo].[PAYSUMMA] WITH CHECK ADD CONSTRAINT [FK_PAYSUMMA_SERVICES] FOREIGN KEY([SERVICECD])
REFERENCES [dbo].[SERVICES] ([SERVICECD])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[PAYSUMMA] CHECK CONSTRAINT [FK_PAYSUMMA_SERVICES]
GO
ALTER TABLE [dbo].[REQUEST] WITH CHECK ADD CONSTRAINT [FK_REQUEST_ABONENT] FOREIGN KEY([ACCOUNTCD])
REFERENCES [dbo].[ABONENT] ([ACCOUNTCD])
ON UPDATE CASCADE
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[REQUEST] CHECK CONSTRAINT [FK_REQUEST_ABONENT]
GO
ALTER TABLE [dbo].[REQUEST] WITH CHECK ADD CONSTRAINT [FK_REQUEST_DISREPAIR] FOREIGN KEY([FAILURECD])
REFERENCES [dbo].[DISREPAIR] ([FAILURECD])
ON UPDATE CASCADE
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[REQUEST] CHECK CONSTRAINT [FK_REQUEST_DISREPAIR]
GO
ALTER TABLE [dbo].[REQUEST] WITH CHECK ADD CONSTRAINT [FK_REQUEST_EXECUTOR] FOREIGN KEY([EXECUTORCD])
REFERENCES [dbo].[EXECUTOR] ([EXECUTORCD])
ON UPDATE CASCADE
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[REQUEST] CHECK CONSTRAINT [FK_REQUEST_EXECUTOR]
GO
ALTER TABLE [dbo].[NACHISLSUMMA] WITH CHECK ADD CHECK (([NACHISLMONTH]>=(1) AND [NACHISLMONTH]<=(12)))
GO
ALTER TABLE [dbo].[NACHISLSUMMA] WITH CHECK ADD CHECK (([NACHISLYEAR]>=(1990) AND [NACHISLYEAR]<=(2100)))
GO
ALTER TABLE [dbo].[PAYSUMMA] WITH CHECK ADD CHECK (([PAYMONTH]>=(1) AND [PAYMONTH]<=(12)))
GO
ALTER TABLE [dbo].[PAYSUMMA] WITH CHECK ADD CHECK (([PAYYEAR]>=(1990) AND [PAYYEAR]<=(2100)))
GO
/*****************************************************************/
/* Insert STREET */
/*****************************************************************/
INSERT INTO [dbo].[STREET]
([STREETCD]
,[STREETNM])
VALUES
(3, 'ВОЙКОВ ПЕРЕУЛОК'),
(7, 'КУТУЗОВА УЛИЦА'),
(6, 'МОСКОВСКАЯ УЛИЦА'),
(8, 'МОСКОВСКОЕ ШОССЕ'),
(4, 'ТАТАРСКАЯ УЛИЦА'),
(5, 'ГАГАРИНА УЛИЦА'),
(1, 'ЦИОЛКОВСКОГО УЛИЦА'),
(2, 'НОВАЯ УЛИЦА');
GO
/*****************************************************************/
/* Insert SERVICES */
/*****************************************************************/
INSERT INTO [dbo].[SERVICES]
([SERVICECD]
,[SERVICENM])
VALUES
(1, 'Газоснабжение'),
(2, 'Электроснабжение'),
(3, 'Теплоснабжение'),
(4, 'Водоснабжение');
GO
/*****************************************************************/
/* Insert DISREPAIR */
/*****************************************************************/
INSERT INTO [dbo].[DISREPAIR]
([FAILURECD]
,[FAILURENM])
VALUES
(1, 'Засорилась водогрейная колонка'),
(2, 'Не горит АГВ'),
(3, 'Течет из водогрейной колонки'),
(4, 'Неисправна печная горелка'),
(5, 'Неисправен газовый счетчик'),
(6, 'Плохое поступление газа на горелку плиты'),
(7, 'Туго поворачивается пробка крана плиты'),
(8, 'При закрытии краника горелка плиты не гаснет'),
(12, 'Неизвестна');
GO
/*****************************************************************/
/* Insert EXECUTOR */
/*****************************************************************/
INSERT INTO [dbo].[EXECUTOR]
([EXECUTORCD]
,[Fio])
VALUES
(1, 'Стародубцев Е. М.'),
(2, 'Булгаков Т. И.'),
(3, 'Шубин В. Г.'),
(4, 'Шлюков М. К.'),
(5, 'Школьников С. М.'),
(6, 'Степанов А. В.');
GO
/*****************************************************************/
/* Insert ABONENT */
/*****************************************************************/
INSERT INTO [dbo].[ABONENT]
([ACCOUNTCD]
,[STREETCD]
,[HOUSENO]
,[FLATNO]
,[Fio]
,[PHONE])
VALUES
('005488', 3, 4, 1, 'Аксенов С. А.', '556893'),
('115705', 3, 1, 82, 'Мищенко Е. В.', '769975'),
('015527', 3, 1, 65, 'Конюхов В. С.', '761699'),
('443690', 7, 5, 1, 'Тулупова М. И.', '214833'),
('136159', 7, 39, 1, 'Свирина З. А.', NULL),
('443069', 4, 51, 55, 'Стародубцев Е. В.', '683014'),
('136160', 4, 9, 15, 'Шмаков С. В.', NULL),
('126112', 4, 7, 11, 'Маркова В. П.', '683301'),
('136169', 4, 7, 13, 'Денисова Е. К.', '680305'),
('080613', 8, 35, 11, 'Лукашина Р. М.', '254417'),
('080047', 8, 39, 36, 'Шубина Т. П.', '257842'),
('080270', 6, 35, 6, 'Тимошкина Н. Г.', '321002');
GO
/*****************************************************************/
/* Insert NACHISLSUMMA */
/*****************************************************************/
INSERT INTO [dbo].[NACHISLSUMMA]
([NACHISLFACTCD]
,[ACCOUNTCD]
,[SERVICECD]
,[NACHISLSUM]
,[NACHISLMONTH]
,[NACHISLYEAR])
VALUES
(19, '005488', 2, 58.7, 12, 2019),
(2, '005488', 2, 46, 12, 2018),
(3, '005488', 2, 56, 4, 2021),
(4, '115705', 2, 40, 1, 2018),
(5, '115705', 2, 250, 9, 2019),
(13, '136160', 2, 20, 5, 2019),
(1, '136160', 2, 56, 1, 2021),
(15, '136169', 2, 20, 5, 2019),
(7, '080047', 2, 80, 10, 2020),
(8, '080047', 2, 80, 10, 2019),
(9, '080270', 2, 46, 12, 2019),
(10, '080613', 2, 56, 6, 2019),
(11, '115705', 2, 250, 9, 2018),
(12, '115705', 2, 58.7, 8, 2019),
(16, '136169', 2, 58.7, 11, 2019),
(17, '443069', 2, 80, 9, 2019),
(18, '443069', 2, 38.5, 8, 2019),
(6, '136160', 1, 18.3, 1, 2020),
(20, '015527', 1, 28.32, 7, 2020),
(21, '080047', 1, 19.56, 3, 2020),
(22, '080613', 1, 10.6, 9, 2020),
(23, '443069', 1, 38.28, 12, 2020),
(24, '015527', 1, 38.32, 4, 2021),
(25, '115705', 1, 37.15, 10, 2021),
(26, '080613', 1, 12.6, 8, 2018),
(27, '136169', 1, 25.32, 1, 2021),
(28, '080270', 1, 57.1, 2, 2020),
(29, '136159', 1, 8.3, 8, 2021),
(30, '005488', 1, 62.13, 4, 2018),
(31, '115705', 1, 37.8, 5, 2019),
(32, '443690', 1, 17.8, 6, 2020),
(33, '080047', 1, 22.56, 5, 2021),
(34, '126112', 1, 15.3, 8, 2018),
(35, '080047', 1, 32.56, 9, 2019),
(36, '080613', 1, 12.6, 4, 2020),
(37, '115705', 1, 37.15, 11, 2021),
(38, '080270', 1, 58.1, 12, 2018),
(39, '136169', 1, 28.32, 1, 2019),
(40, '015527', 1, 18.32, 2, 2020),
(41, '443690', 1, 21.67, 3, 2021),
(42, '080613', 1, 22.86, 4, 2018),
(43, '080270', 1, 60.1, 5, 2019),
(44, '136169', 1, 28.32, 2, 2020),
(45, '080047', 1, 22.2, 7, 2021),
(46, '126112', 1, 25.3, 8, 2019),
(47, '443069', 1, 38.32, 9, 2019),
(48, '136159', 1, 8.3, 10, 2020),
(49, '115705', 1, 37.15, 6, 2021),
(50, '136160', 1, 18.3, 12, 2018),
(51, '005488', 3, 279.8, 5, 2020),
(52, '005488', 3, 266.7, 2, 2021),
(53, '015527', 3, 343.36, 11, 2021),
(54, '080047', 3, 271.6, 2, 2021),
(55, '080270', 3, 278.25, 11, 2021),
(56, '080613', 3, 254.4, 7, 2019),
(57, '080613', 3, 258.8, 2, 2021),
(58, '080613', 3, 239.33, 5, 2021),
(59, '126112', 3, 179.9, 4, 2020),
(60, '136159', 3, 180.13, 9, 2021),
(61, '136160', 3, 238.8, 3, 2018),
(62, '136160', 3, 237.38, 3, 2019),
(63, '136169', 3, 349.19, 6, 2020),
(64, '136169', 3, 346.18, 7, 2020),
(65, '443690', 3, 290.33, 3, 2021),
(66, '015527', 4, 580.1, 7, 2020),
(67, '015527', 4, 611.3, 10, 2021),
(68, '080270', 4, 444.34, 3, 2019),
(69, '080270', 4, 453.43, 6, 2020),
(70, '080270', 4, 454.6, 4, 2021),
(71, '115705', 4, 553.85, 1, 2020),
(72, '126112', 4, 435.5, 6, 2020),
(73, '136159', 4, 349.38, 4, 2019),
(74, '136159', 4, 418.88, 6, 2020),
(75, '136169', 4, 528.44, 10, 2021),
(76, '443069', 4, 466.69, 5, 2020),
(77, '443069', 4, 444.45, 10, 2021),
(78, '443690', 4, 480.88, 8, 2019),
(79, '443690', 4, 500.13, 9, 2020);
GO
/*****************************************************************/
/* Insert PAYSUMMA */
/*****************************************************************/
INSERT INTO [dbo].[PAYSUMMA]
([PAYFACTCD]
,[ACCOUNTCD]
,[SERVICECD]
,[PAYSUM]
,[PAYDATE]
,[PAYMONTH]
,[PAYYEAR])
VALUES
(1, '005488', 2, 58.7, '2020-01-08', 12, 2019),
(2, '005488', 2, 40, '2019-01-06', 12, 2018),
(3, '005488', 2, 56, '2021-05-06', 4, 2021),
(4, '115705', 2, 40, '2018-02-10', 1, 2018),
(5, '115705', 2, 250, '2019-10-03', 9, 2019),
(6, '136160', 2, 20, '2019-06-13', 5, 2019),
(7, '136160', 2, 56, '2021-02-12', 1, 2021),
(8, '136169', 2, 20, '2019-06-22', 5, 2021),
(9, '080047', 2, 80, '2020-11-26', 10, 2020),
(10, '080047', 2, 80, '2019-11-21', 10, 2019),
(11, '080270', 2, 30, '2020-01-03', 12, 2019),
(12, '080613', 2, 58.5, '2019-07-19', 6, 2019),
(13, '115705', 2, 250, '2018-10-06', 9, 2018),
(14, '115705', 2, 58.7, '2019-09-04', 8, 2019),
(15, '136169', 2, 58.7, '2019-12-01', 11, 2019),
(16, '443069', 2, 80, '2019-10-03', 9, 2019),
(17, '443069', 2, 38.5, '2019-09-13', 8, 2019),
(18, '136160', 1, 18, '2020-02-05', 1, 2020),
(19, '015527', 1, 30, '2020-08-03', 7, 2020),
(20, '080047', 1, 19.56, '2020-04-02', 3, 2020),
(21, '080613', 1, 11, '2020-10-03', 9, 2020),
(22, '443069', 1, 38.28, '2021-02-04', 12, 2020),
(23, '015527', 1, 40, '2021-05-07', 4, 2021),
(24, '115705', 1, 37.15, '2021-11-04', 10, 2021),
(25, '080613', 1, 12, '2018-09-20', 8, 2018),
(26, '136169', 1, 25.32, '2021-02-03', 1, 2021),
(27, '080270', 1, 60, '2020-03-05', 2, 2020),
(28, '136159', 1, 8.3, '2021-09-10', 8, 2021),
(29, '005488', 1, 65, '2018-05-03', 4, 2018),
(30, '115705', 1, 37.8, '2019-07-12', 5, 2019),
(31, '443690', 1, 20, '2020-07-10', 6, 2020),
(32, '080047', 1, 22.56, '2021-06-25', 5, 2021),
(33, '126112', 1, 15.3, '2018-09-08', 8, 2018),
(34, '080047', 1, 32.56, '2019-10-18', 9, 2019),
(35, '080613', 1, 12.6, '2020-05-22', 4, 2020),
(36, '115705', 1, 37.15, '2021-12-23', 11, 2021),
(37, '080270', 1, 58.1, '2019-01-07', 12, 2018),
(38, '136169', 1, 28.32, '2019-02-08', 1, 2019),
(39, '015527', 1, 20, '2020-03-18', 2, 2020),
(40, '443690', 1, 19.47, '2021-04-10', 3, 2021),
(41, '080613', 1, 22.86, '2018-05-04', 4, 2018),
(42, '080270', 1, 60, '2019-06-07', 5, 2019),
(43, '136169', 1, 28.32, '2020-03-05', 2, 2020),
(44, '080047', 1, 22.2, '2021-08-10', 7, 2021),
(45, '126112', 1, 25.3, '2019-09-10', 8, 2019),
(46, '443069', 1, 38.32, '2019-10-09', 9, 2019),
(47, '136159', 1, 8.3, '2020-11-14', 10, 2020),
(48, '115705', 1, 37.15, '2021-08-10', 6, 2021),
(49, '136160', 1, 16, '2019-01-07', 12, 2018),
(50, '005488', 3, 280, '2020-06-10', 5, 2020),
(51, '005488', 3, 260, '2021-03-11', 2, 2021),
(52, '015527', 3, 345, '2021-12-15', 11, 2021),
(53, '080047', 3, 271.6, '2021-03-12', 2, 2021),
(54, '080270', 3, 278, '2021-12-06', 11, 2021),
(55, '080613', 3, 254.4, '2019-08-10', 7, 2019),
(56, '080613', 3, 258.8, '2021-03-8', 2, 2021),
(57, '080613', 3, 239.35, '2021-06-11', 5, 2021),
(58, '126112', 3, 179.9, '2020-05-01', 4, 2020),
(59, '136159', 3, 180.13, '2021-10-21', 9, 2021),
(60, '136160', 3, 240, '2018-04-04', 3, 2018),
(61, '136160', 3, 200, '2019-04-06', 3, 2019),
(62, '136169', 3, 349.19, '2020-07-14', 6, 2020),
(63, '136169', 3, 346.18, '2020-08-13', 7, 2020),
(64, '443690', 3, 295, '2021-04-09', 3, 2021),
(65, '015527', 4, 580.1, '2020-08-08', 7, 2020),
(66, '015527', 4, 611.3, '2021-11-03', 10, 2021),
(67, '080270', 4, 444.5, '2019-04-18', 3, 2019),
(68, '080270', 4, 450, '2020-07-14', 6, 2020),
(69, '080270', 4, 460, '2021-05-12', 4, 2021),
(70, '115705', 4, 553.85, '2020-02-02', 1, 2020),
(71, '126112', 4, 435.5, '2020-07-12', 6, 2020),
(72, '136159', 4, 349.38, '2019-05-18', 4, 2019),
(73, '136159', 4, 420, '2020-07-09', 6, 2020),
(74, '136169', 4, 528.44, '2021-11-26', 10, 2021),
(75, '443069', 4, 466.69, '2020-06-03', 5, 2020),
(76, '443069', 4, 444.45, '2021-11-16', 10, 2021),
(77, '443690', 4, 485, '2019-09-05', 8, 2019);
GO
/*****************************************************************/
/* Insert REQUEST */
/*****************************************************************/
INSERT INTO [dbo].[REQUEST]
([REQUESTCD]
,[ACCOUNTCD]
,[EXECUTORCD]
,[FAILURECD]
,[INCOMINGDATE]
,[EXECUTIONDATE]
,[EXECUTED])
VALUES
(1, '005488', 1, 1, '2019-12-17', '2019-12-20', 1),
(2, '115705', 3, 1, '2019-08-07', '2019-08-12', 1),
(3, '015527', 1, 12, '2020-02-28', '2020-03-08', 0),
(5, '080270', 4, 1, '2019-12-31', NULL, 0),
(6, '080613', 1, 6, '2019-06-16', '2019-06-24', 1),
(7, '080047', 3, 2, '2020-10-20', '2020-10-24', 1),
(9, '136169', 2, 1, '2019-11-06', '2019-11-08', 1),
(10, '136159', 3, 12, '2019-04-01', '2019-04-03', 0),
(11, '136160', 1, 6, '2021-01-12', '2021-01-12', 1),
(12, '443069', 5, 2, '2019-08-08', '2019-08-10', 1),
(13, '005488', 5, 8, '2018-09-04', '2018-12-05', 1),
(14, '005488', 4, 6, '2021-04-04', '2021-04-13', 1),
(15, '115705', 4, 5, '2018-09-20', '2018-09-23', 1),
(16, '115705', NULL, 3, '2019-12-28', NULL, 0),
(17, '115705', 1, 5, '2019-08-15', '2019-09-06', 1),
(18, '115705', 2, 3, '2020-12-28', '2021-01-04', 1),
(19, '080270', 4, 8, '2019-12-17', '2019-12-27', 1),
(20, '080047', 3, 2, '2019-10-11', '2019-10-11', 1),
(21, '443069', 1, 2, '2019-09-13', '2019-09-14', 1),
(22, '136160', 1, 7, '2019-05-18', '2019-05-25', 1),
(23, '136169', 5, 7, '2019-05-07', '2019-05-08', 1);
GO

/*****************************************************************/
/* Triggers */
/*****************************************************************/
CREATE TRIGGER dbo.TD_ABONENT ON ABONENT
INSTEAD OF DELETE
AS
BEGIN
IF (SELECT COUNT(*) AS NUMROWS FROM NACHISLSUMMA
WHERE NACHISLSUMMA.ACCOUNTCD IN (SELECT Deleted.ACCOUNTCD FROM deleted)) > 0 BEGIN
raiserror (50001, 16,- 1)
END
IF (SELECT COUNT(*) AS NUMROWS FROM PAYSUMMA
WHERE PAYSUMMA.ACCOUNTCD IN (SELECT Deleted.ACCOUNTCD FROM deleted)) > 0 BEGIN
RAISERROR (50001, 16, - 1)
END;
END;
GO

CREATE TRIGGER TD_SERVICES ON SERVICES
INSTEAD OF DELETE
AS
BEGIN
IF (SELECT COUNT(*) FROM NACHISLSUMMA
WHERE NACHISLSUMMA.SERVICECD IN (SELECT Deleted.SERVICECD FROM deleted)) > 0 BEGIN
RAISERROR (50001, 16,- 1)
END
IF (SELECT COUNT(*) FROM PAYSUMMA
WHERE PAYSUMMA.SERVICECD IN (SELECT Deleted.SERVICECD FROM deleted)) > 0 BEGIN
RAISERROR (50001, 16,- 1)
END;
END;

ALTER TABLE REQUEST ADD CONSTRAINT DATENOW DEFAULT GETDATE() FOR INCOMINGDATE;
GO

UPDATE Nachislsumma SET NachislSum=NachislSum+500 WHERE Servicecd=1;
GO
UPDATE Nachislsumma SET NachislSum=NachislSum+600 WHERE Servicecd=2;
GO
UPDATE Nachislsumma SET NachislSum=NachislSum+2000 WHERE Servicecd=3;
GO
UPDATE Nachislsumma SET NachislSum=NachislSum-300 WHERE Servicecd=4;
GO
UPDATE Paysumma SET PaySum=PaySum+500 WHERE Servicecd=1;
GO
UPDATE Paysumma SET PaySum=PaySum+600 WHERE Servicecd=2;
GO
UPDATE Paysumma SET Paysum=Paysum+2000 WHERE Servicecd=3;
GO
UPDATE Paysumma SET Paysum=Paysum-300 WHERE Servicecd=4;
GO

Последнее изменение: четверг, 1 сентября 2022, 22:01