USE master
GO
/*
IF DB_ID(N'DataBaseLab9') IS NOT NULL
DROP DATABASE DataBaseLab9;
GO

CREATE DATABASE DataBaseLab9
GO
*/

USE DataBaseLab9;
GO


IF OBJECT_ID (N'BookAuthors') IS NOT NULL
BEGIN
    IF OBJECT_ID (N'LibraryBooks') IS NOT NULL
        DROP TABLE LibraryBooks;
    DROP TABLE BookAuthors;
END
GO

CREATE TABLE BookAuthors
    (AuthorID INT PRIMARY KEY IDENTITY(1,1),
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    YearOfBirth INT,
    City VARCHAR(50),
    );
GO


IF OBJECT_ID (N'LibraryBooks') IS NOT NULL
    DROP TABLE LibraryBooks;
GO

    CREATE TABLE LibraryBooks
    (BookID INT PRIMARY KEY IDENTITY(1,1),
    NameBook VARCHAR(100),
    YearOfPublication INT,
    AuthorID INT
    CONSTRAINT FK_Autors_Books FOREIGN KEY (AuthorID) REFERENCES BookAuthors (AuthorID)
    );
    GO

    INSERT BookAuthors (FirstName, LastName, YearOfBirth, City)
    VALUES ('Nikolay', 'Gogol', 1809, 'Big Sorochintsy'),
    ('Mikhail', 'Lermontov', 1814, 'Moscow'),
    ('Alexander', 'Pushkin', 1799, 'Moscow'),
    ('Taras', 'Shevchenko', 1814, 'Morintsy');

    INSERT LibraryBooks (AuthorID, NameBook, YearOfPublication)
    VALUES (1, 'Dead Souls', 1841),
    (2, 'Dead Souls', 1841),
    (2, 'Overcoat', 1842),
    (1, 'Hero of our time', 1840),
    (3, 'Ruslan and Ludmila', 1820),
    (4, 'Kobzar', 1860);


    SELECT *FROM LibraryBooks;
    GO

    SELECT *FROM BookAuthors
    GO

USE DataBaseLab9;
GO


IF OBJECT_ID ('Books', 'V') IS NOT NULL
    DROP VIEW Books
GO

CREATE VIEW Books AS
    SELECT LibraryBooks.NameBook AS NameBook,
            BookAuthors.FirstName  AS AuthorFirstName,
            BookAuthors.LastName  AS AuthorLastName
    FROM LibraryBooks INNER JOIN BookAuthors ON  LibraryBooks.AuthorID = BookAuthors.AuthorID
GO

SELECT *FROM Books
GO


------------1-----------------
USE DataBaseLab9;
GO

IF OBJECT_ID ('InsertTrigger', 'TR')  IS NOT NULL
    DROP TRIGGER InsertTrigger;
GO

CREATE TRIGGER InsertTrigger ON LibraryBooks
    AFTER INSERT
    AS
    PRINT 'add books'
GO


IF OBJECT_ID ('DeleteTrigger', 'TR')  IS NOT NULL
    DROP TRIGGER DeleteTrigger;
GO

CREATE TRIGGER DeleteTrigger ON LibraryBooks INSTEAD OF DELETE
    AS
    BEGIN
        DELETE LibraryBooks WHERE EXISTS (SELECT AuthorID FROM deleted
            WHERE LibraryBooks.NameBook = deleted.NameBook);
    END
GO


INSERT LibraryBooks (NameBook, YearOfPublication) VALUES ('Evenings on a Farm Near Dikanka', 1831);
SELECT *FROM LibraryBooks
GO

DELETE FROM LibraryBooks WHERE (NameBook = 'Dead Souls')
SELECT *FROM LibraryBooks
GO


IF OBJECT_ID ('UpdateTrigger', 'TR')  IS NOT NULL
    DROP TRIGGER UpdateTrigger;
GO

CREATE TRIGGER UpdateTrigger ON LibraryBooks AFTER UPDATE
    AS
        BEGIN
            DECLARE @name VARCHAR(50) = (SELECT NameBook FROM deleted);
            DECLARE @name_ VARCHAR(50) = (SELECT NameBook FROM inserted);
            IF (UPDATE (NameBook))
            BEGIN
                RAISERROR('ERROR!!!', 16, 1) ROLLBACK TRANSACTION
            END
        END
GO

UPDATE LibraryBooks SET YearOfPublication = 1834 WHERE (AuthorID = 476)
SELECT *FROM LibraryBooks
GO

/*UPDATE LibraryBooks SET NameBook = 'new Name' WHERE (AuthorID = 476)
Select *from LibraryBooks
GO*/



-------------2-----------------
IF OBJECT_ID ('InsertTriggerView', 'TR')  IS NOT NULL
    DROP TRIGGER InsertTriggerView;
GO

CREATE TRIGGER InsertTriggerView ON Books
    INSTEAD OF INSERT
    AS
    /*IF EXISTS (SELECT * FROM inserted WHERE NameBook NOT IN (SELECT NameBook FROM LibraryBooks))
    BEGIN
        RAISERROR('ERROR!!!', 16, 1) ROLLBACK TRANSACTION
    END*/
    BEGIN
        INSERT INTO LibraryBooks (NameBook)
        SELECT inserted.NameBook
        FROM inserted
        INSERT INTO BookAuthors (FirstName, LastName)
        SELECT inserted.AuthorFirstName, inserted.AuthorLastName
        FROM inserted
    END
GO

INSERT INTO Books (NameBook, AuthorFirstName, AuthorLastName)
    VALUES ('tesr', 'T', 'tt')
SELECT * FROM Books
GO

SELECT * FROM LibraryBooks
GO