USE lab9
GO
------------------------ 1 ------------------------
------------------- Table Init --------------------
IF OBJECT_ID('[dbo].[Authors]', 'U') IS NOT NULL
DROP TABLE [dbo].[Authors]
GO
CREATE TABLE [dbo].[Authors]
(
[Id] int NOT NULL IDENTITY(1, 1),
[FirstName] varchar(50) NOT NULL,
[LastName] varchar(50) NOT NULL,
CONSTRAINT PK_Authors PRIMARY KEY CLUSTERED ([Id]),
);
GO
-------------------- Triggers ---------------------
---------------------- Insert -----------------------
IF OBJECT_ID('[dbo].[AuthorsInsertTrig]', 'U') IS NOT NULL
DROP TRIGGER [dbo].[AuthorsInsertTrig]
GO
CREATE TRIGGER [dbo].[AuthorsInsertTrig]
ON [dbo].[Authors]
AFTER INSERT
AS
IF EXISTS(
SELECT * FROM inserted
WHERE [FirstName] = '' or [LastName] = ''
)
BEGIN
RAISERROR('Empty name is not allowed', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
PRINT 'Generated keys:'
SELECT [Id] as [GeneratedId] from Inserted
GO
---------------------- Update -----------------------
IF OBJECT_ID('[dbo].[AuthorsUpdateTrig]', 'U') IS NOT NULL
DROP TRIGGER [dbo].[AuthorsUpdateTrig]
GO
CREATE TRIGGER [dbo].[AuthorsUpdateTrig]
ON [dbo].[Authors]
AFTER UPDATE
AS
IF UPDATE([FirstName]) OR UPDATE([LastName])
BEGIN
IF EXISTS(
SELECT * FROM inserted
WHERE [FirstName] = '' or [LastName] = ''
)
BEGIN
RAISERROR('Empty name is not allowed', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
END
GO
---------------------- Delete -----------------------
IF OBJECT_ID('[dbo].[AuthorsDeleteTrig]', 'U') IS NOT NULL
DROP TRIGGER [dbo].[AuthorsDeleteTrig]
GO
CREATE TRIGGER [dbo].[AuthorsDeleteTrig]
ON [dbo].[Authors]
AFTER DELETE
AS
DECLARE @delCursor CURSOR
SET @delCursor = CURSOR
FORWARD_ONLY STATIC FOR
SELECT [Id] FROM Deleted
DECLARE @id int
OPEN @delCursor
FETCH NEXT FROM @delCursor INTO @id
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT 'Deleted line id: ' + CAST(@id AS varchar(13))
FETCH NEXT FROM @delCursor INTO @id
END
GO
---------------------- Tests ----------------------
INSERT INTO [dbo].[Authors]
VALUES ('A', 'A'), ('A', 'B'), ('C', 'C')
GO
-- INSERT INTO [dbo].[Authors]
-- VALUES ('', 'D')
-- GO
-- UPDATE [dbo].[Authors]
-- SET [LastName] = ''
-- WHERE [Id] = 1
-- GO
-- DELETE FROM [dbo].[Authors]
-- WHERE [FirstName] = 'A'
-- GO
------------------------ 2 ------------------------
------------------- Tables Init -------------------
IF OBJECT_ID('[dbo].[Readers]', 'U') IS NOT NULL
BEGIN
IF OBJECT_ID('[dbo].[TakenBooks]', 'U') IS NOT NULL
DROP TABLE [dbo].[TakenBooks]
DROP TABLE [dbo].[Readers]
END
GO
CREATE TABLE [dbo].[Readers]
(
[Id] int NOT NULL IDENTITY(1, 1),
[FirstName] varchar(50) NOT NULL,
[LastName] varchar(50) NOT NULL,
CONSTRAINT PK_Readers PRIMARY KEY CLUSTERED ([Id]),
);
GO
IF OBJECT_ID('[dbo].[Books]', 'U') IS NOT NULL
DROP TABLE [dbo].[Books]
GO
CREATE TABLE [dbo].[Books]
(
[ISBN] varchar(20) NOT NULL,
[Name] varchar(100) NOT NULL,
CONSTRAINT PK_Books PRIMARY KEY CLUSTERED ([ISBN]),
);
GO
IF OBJECT_ID('[dbo].[TakenBooks]', 'U') IS NOT NULL
DROP TABLE [dbo].[TakenBooks]
GO
CREATE TABLE [dbo].[TakenBooks]
(
[Id] int NOT NULL IDENTITY(1, 1),
[ReaderId] int NOT NULL,
[ISBN] varchar(20) NOT NULL,
[TakingDate] date NOT NULL DEFAULT(getDate()),
[DueDate] date NOT NULL,
[Returned] int NOT NULL DEFAULT(0),
CONSTRAINT PK_TakenBooks PRIMARY KEY CLUSTERED ([Id]),
CONSTRAINT FK_TakenBooks_Readers FOREIGN KEY ([ReaderId]) REFERENCES [dbo].[Readers] ([Id]),
CONSTRAINT FK_TakenBooks_Books FOREIGN KEY ([ISBN]) REFERENCES [dbo].[Books] ([ISBN]),
CONSTRAINT CHK_Returned CHECK ([Returned] BETWEEN 0 AND 1)
);
GO
-------------------- View Init --------------------
IF OBJECT_ID('[dbo].[ReadingBooksView]') IS NOT NULL
DROP VIEW [dbo].[ReadingBooksView]
GO
CREATE VIEW [dbo].[ReadingBooksView]
AS
SELECT r.[Id] as [ReaderId], tb.[ISBN], tb.[TakingDate], tb.[DueDate]
FROM [dbo].[Readers] as r INNER JOIN [dbo].[TakenBooks] as tb
ON r.[Id] = tb.[ReaderId]
WHERE tb.[Returned] = 0
GO
-------------------- Triggers ---------------------
---------------------- Insert -----------------------
IF OBJECT_ID('[dbo].[RBViewInsertTrig]', 'U') IS NOT NULL
DROP TRIGGER [dbo].[RBViewInsertTrig]
GO
CREATE TRIGGER [dbo].[RBViewInsertTrig]
ON [dbo].[ReadingBooksView]
INSTEAD OF INSERT
AS
IF EXISTS (SELECT * FROM Inserted WHERE [ReaderId] NOT IN (SELECT [ReaderId] FROM [dbo].[Readers]))
BEGIN
RAISERROR('Nonexistent Reader(s)', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
IF EXISTS (SELECT * FROM Inserted WHERE [ISBN] NOT IN (SELECT [ISBN] FROM [dbo].[Books]))
BEGIN
RAISERROR('Nonexistent Book(s)', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
INSERT INTO [dbo].[TakenBooks]
(ReaderId, ISBN, TakingDate, DueDate)
SELECT [ReaderId], [ISBN], [TakingDate], [DueDate]
FROM Inserted
GO
---------------------- Update -----------------------
IF OBJECT_ID('[dbo].[RBViewUpdateTrig]', 'U') IS NOT NULL
DROP TRIGGER [dbo].[RBViewUpdateTrig]
GO
CREATE TRIGGER [dbo].[RBViewUpdateTrig]
ON [dbo].[ReadingBooksView]
INSTEAD OF UPDATE
AS
IF UPDATE([ReaderId])
BEGIN
RAISERROR('ReaderId cant be modified', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
IF UPDATE([ISBN])
BEGIN
RAISERROR('ISBN cant be modified', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
UPDATE [dbo].[TakenBooks]
SET [TakingDate] = ins.[TakingDate], [DueDate] = ins.[DueDate]
FROM
(SELECT *, row_number() over (order by [ReaderId]) as row_num from inserted) as ins
JOIN
(SELECT *, row_number() over (order by [ReaderId]) as row_num from deleted) as del
ON ins.row_num = del.row_num
WHERE [dbo].[TakenBooks].[ISBN] = del.[ISBN] AND [dbo].[TakenBooks].[TakingDate] = del.[TakingDate]
AND [dbo].[TakenBooks].[ReaderId] = del.[ReaderId]
GO
---------------------- Delete -----------------------
IF OBJECT_ID('[dbo].[RBViewDeleteTrig]', 'U') IS NOT NULL
DROP TRIGGER [dbo].[RBViewDeleteTrig]
GO
CREATE TRIGGER [dbo].[RBViewDeleteTrig]
ON [dbo].[ReadingBooksView]
INSTEAD OF DELETE
AS
UPDATE [dbo].[TakenBooks]
SET [Returned] = 1
FROM deleted as del
WHERE [dbo].[TakenBooks].[ISBN] = del.[ISBN] AND [dbo].[TakenBooks].[TakingDate] = del.[TakingDate]
GO
---------------------- Tests ----------------------
-- INSERT INTO [dbo].[Books]
-- VALUES
-- (1, 'War and Peace'),
-- (2, 'War of worlds')
-- INSERT INTO [dbo].[Readers]
-- VALUES
-- ('Robert', 'B'),
-- ('Victor', 'C')
-- INSERT INTO [dbo].[TakenBooks]
-- VALUES
-- (1, 1, CONVERT(datetime, '4.11.2018', 104), getDate(), 0),
-- (2, 1, CONVERT(datetime, '4.09.2019', 104), getDate(), 1),
-- (2, 1, CONVERT(datetime, '22.11.2018', 104), getDate(), 0),
-- (2, 2, CONVERT(datetime, '7.01.2018', 104), getDate(), 0)
-- SELECT * FROM [dbo].[Readers]
-- SELECT * FROM [dbo].[TakenBooks]
-- SELECT * FROM ReadingBooksView
-- GO
-- INSERT INTO ReadingBooksView
-- VALUES (1, 2, CONVERT(datetime, '4.11.2018', 104), getDate())
-- GO
-- UPDATE ReadingBooksView
-- SET [DueDate] = CONVERT(datetime, '4.11.2020', 104)
-- WHERE [ReaderId] = 1
-- GO
-- DELETE FROM ReadingBooksView
-- WHERE [ReaderId] = 2
-- GO
-- SELECT * FROM [dbo].[Readers]
-- SELECT * FROM [dbo].[TakenBooks]
-- SELECT * FROM ReadingBooksView
-- GO