-- убить все соединения
USE master;
GO
DECLARE @kill varchar(8000) = '';

SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), c.session_id) + ';'
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id <> @@SPID
ORDER BY c.connect_time ASC

EXEC(@kill)
GO

USE master;
GO
--удаление БД
IF DB_ID (N'Lab9') IS NOT NULL
DROP DATABASE Lab9;
GO

--создание БД
CREATE DATABASE Lab9
ON ( NAME = Lab9_dat,
FILENAME = '/tmp/data/lab9dat.mdf',
SIZE = 10, MAXSIZE = 150, FILEGROWTH = 5% )
LOG ON ( NAME = Lab9_log,
FILENAME = '/tmp/data/lab9dat.ldf',
SIZE = 5MB, MAXSIZE = 150MB, FILEGROWTH = 5MB )
GO

USE Lab9;
GO

IF OBJECT_ID(N'Actor') IS NOT NULL
DROP TABLE Actor;
go

CREATE TABLE Actor
(ActorId int IDENTITY(1,1) PRIMARY KEY with (ignore_dup_key=on) NOT NULL,
    LastName VARCHAR (100) NOT NULL,
    FirstName VARCHAR (100) NOT NULL,
    BirthYear int NULL,
    Country VARCHAR(50) NULL,
)
GO

INSERT Actor (LastName, FirstName, BirthYear, Country)
VALUES ('Hanks', 'Thomas', 1956, 'U.S.');
INSERT Actor (LastName, FirstName, BirthYear, Country)
VALUES ( 'Sherwood', 'Dominic', 1990, 'England');
INSERT Actor ( LastName, FirstName, BirthYear, Country)
VALUES ('McNamara', 'Katherine', 1995, 'U.S.');
INSERT Actor ( LastName, FirstName, BirthYear, Country)
VALUES ( 'Amell', 'Stephen', 1981, 'Canada');
GO

SELECT N'Table with actors', * FROM Actor

IF OBJECT_ID(N'Film') IS NOT NULL
DROP TABLE Film;
go

CREATE TABLE Film
(FilmId int IDENTITY(1,1) PRIMARY KEY with (ignore_dup_key=on) NOT NULL,
    FilmName VARCHAR (50) NOT NULL,
    Year int NULL,
    Genre VARCHAR (100) NULL,
    Country VARCHAR(50) NULL,
    Actor_Id int NULL DEFAULT (3),
    CONSTRAINT FK_Actor_Film
        FOREIGN KEY (Actor_Id) REFERENCES Actor (ActorId)
        --ON DELETE CASCADE --Если из родительской таблицы удаляется строка, соответствующие ей строки удаляются и из ссылающейся таблицы.
        --ON UPDATE CASCADE
        --ON DELETE NO ACTION --Компонент Компонент SQL Server Database Engine формирует ошибку, и выполняется откат операции удаления строки из родительской таблицы.
        --ON UPDATE NO ACTION
        ON DELETE SET NULL --Все значения, составляющие внешний ключ, при удалении соответствующей строки родительской таблицы устанавливаются в значение NULL.
        ON UPDATE SET NULL
        --ON DELETE SET DEFAULT --Все значения, содержащие внешний ключ, при удалении соответствующей строки родительской таблицы устанавливаются в значения по умолчанию. Для выполнения этого ограничения все внешние ключевые столбцы должны иметь определения по умолчанию. Если столбец допускает значения NULL и значение по умолчанию явно не определено, значением столбца по умолчанию становится NULL.
        --ON UPDATE SET DEFAULT,

)
GO

INSERT Film (FilmName, Year, Genre, Country, Actor_Id)
VALUES ('Inferno', 2016, 'Mystery action thriller film', 'United States', 1);
INSERT Film (FilmName, Year, Genre, Country, Actor_Id)
VALUES ('Teenage Mutant Ninja Turtles: Out of the Shadows', 2016, 'Fantasy', 'United States', 4);
INSERT Film (FilmName, Year, Genre, Country, Actor_Id)
VALUES ('Vampire Academy', 2014, 'Fantasy comedy horror film', 'United States, United Kingdom', 2);
INSERT Film (FilmName, Year, Genre, Country, Actor_Id)
VALUES ('Maze Runner: The Death Cure', 2018, ' Dystopian science fiction film', 'United States', 3);
GO

SELECT N'Table with films', * FROM Film
GO


IF OBJECT_ID(N'Actor_Film') is not NULL
DROP VIEW Actor_Film;
GO

--Создать представление на основе полей обеих связанных таблиц задания 6
CREATE VIEW Actor_Film AS
    SELECT
        f.FilmName, f.Country, a.LastName, a.FirstName, a.BirthYear
    FROM Film f INNER JOIN Actor a
        ON f.Actor_Id = a.ActorId
GO

SELECT 'View Table', * FROM Actor_Film;
GO

--Для одной из таблиц пункта 2 задания 7 создать триггеры на вставку, удаление и добавление, при
--выполнении заданных условий один из триггеров должен инициировать возникновение ошибки
--(RAISERROR / THROW).

UPDATE Actor_Film
Set FilmName = FilmName + 'x'
GO

--Тригер на удаление
IF OBJECT_ID ('TableDeleteTrig','TR') IS NOT NULL
   DROP TRIGGER TableDeleteTrig;
GO

CREATE TRIGGER TableDeleteTrig ON Actor
    AFTER DELETE AS
    PRINT 'Film was deleted'
    --UPDATE Actor
    --SET IsDeleted = 1
    --WHERE ActorId = (SELECT ActorId FROM deleted) --Country = 'U.S.'
GO

DELETE FROM Actor
WHERE FirstName='Thomas'
or (FirstName = 'Katherine');
GO
/*
DELETE FROM Actor
WHERE --FirstName='Thomas'
 (BirthYear > 1989);
GO*/

SELECT 'Delete Trigger', * FROM Actor;
GO

INSERT Actor (LastName, FirstName, BirthYear, Country)
VALUES ( 'Hanks', 'Thomas', 1956, 'U.S.');
INSERT Actor ( LastName, FirstName, BirthYear, Country)
VALUES ( 'McNamara', 'Katherine', 1995, 'U.S.');

--Тригер на вставку
IF OBJECT_ID ('NewFilm','TR') IS NOT NULL
   DROP TRIGGER NewFilm;
GO

CREATE TRIGGER NewFilm ON Film
AFTER INSERT
AS
IF UPDATE(FilmId)
        PRINT 'New film was inserted'
IF EXISTS (SELECT *
           FROM Film p
           JOIN inserted AS i
           ON p.FilmId = i.FilmId
           JOIN Actor AS v
           ON v.ActorId = p.Actor_Id
           WHERE v.BirthYear != 1990
          )
BEGIN
--RaISERROR(15600, -1, -1, N'The year isnt a BirthYear of our Actor');
--print 'np'
--ROLLBACK TRANSACTION;  --Откатывает явные или неявные транзакции до начала или до точки сохранения транзакции.
RETURN
END;
GO

INSERT INTO Film (FilmName, Year, Genre, Country, Actor_Id)
VALUES (
'ShadowHunters',
2016,
'Fantasy',
'United States',
2);
INSERT INTO Film (FilmName, Year, Genre, Country, Actor_Id)
VALUES (
    'Inserted Film: Arrow',
    2012,
    'Crime, Mystery',
    'United States',
4
);
GO

INSERT INTO Film (FilmName, Year, Genre, Country, Actor_Id)
VALUES (
'ShadowHunters season 2',
2017,
'Fantasy',
'United States',
2);
GO

SELECT 'Insert trigger', * FROM Film;
GO

--Тригер на добавление
IF OBJECT_ID ('TableUpdateTrig','TR') IS NOT NULL
   DROP TRIGGER TableUpdateTrig;
GO

--CREATE TRIGGER TableUpdateTrig ON Actor
--FOR UPDATE AS


--SELECT * FROM Actor.Inserted;
--GO


UPDATE Actor
SET BirthYear = BirthYear + 5508
WHERE ActorId = 4;
GO

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

CREATE TRIGGER my_trig
ON Actor
AFTER INSERT
AS
IF UPDATE(BirthYear)
PRINT 'Actor was inserted'
 UPDATE Actor
SET BirthYear = inserted.BirthYear + 5508
FROM inserted
WHERE inserted.ActorId = 4;
GO

SELECT N'Table with trigger for strings', * FROM Actor
GO

/*CREATE TRIGGER TableUpdateTrig
ON Actor
AFTER UPDATE
AS THROW 51000, 'Attempt to modify a column', 0;
GO */

--Триггер на добавление/изменение

/*CREATE TRIGGER TableUpdateTrig
ON Actor
AFTER UPDATE
AS
IF UPDATE (ActorId)
BEGIN
    THROW 51000, 'Attempt to modify a column', 0
    ROLLBACK TRANSACTION
END
IF UPDATE(BirthYear)
PRINT ('Column BirthYear ModifActorIdied')
GO
*/


CREATE TRIGGER TableUpdateTrig
ON Actor
AFTER UPDATE
AS
IF UPDATE (ActorId)

THROW 51000, 'Attempt to modify a column', 0
--ROLLBACK TRANSACTION

IF UPDATE(BirthYear) OR UPDATE(Country)
PRINT ('Column BirthYear or Country Modified')

INSERT INTO Film (Actor_ID, FilmName, Country)
SELECT ActorId, 'Added film Deadpool', Country
FROM INSERTED
GO

INSERT INTO Actor(FirstName, LastName, BirthYear, Country)
VALUES ('Ryan', 'Reynolds', 1976, 'United States');
GO

UPDATE Actor
SET BirthYear = BirthYear + 5508
WHERE ActorId = 4;
GO

UPDATE Film SET Country = 'UK'
WHERE Country = 'United States, United Kingdom';
GO

UPDATE Actor
SET Country = 'America'
WHERE FirstName = 'Rayn';
GO

SELECT N'Table UpdateTrigger films 5', * FROM Film
GO

SELECT N'Table UpdateTrigger films', * FROM Film
GO
SELECT N'Table UpdateTrigger actors', * FROM Actor
GO
SELECT N'Table UpdateTrigger actors', * FROM Film
GO

-------------------------------------------------------------------------
--Для представления пункта 2 задания 7 создать триггеры на вставку, удаление и добавление,
--обеспечивающие возможность выполнения операций с данными непосредственно через представление.

--Триггер на удаление для представления
IF OBJECT_ID ('TriggerViewDelete','TR') IS NOT NULL
   DROP TRIGGER TriggerViewDelete;
GO
/*
CREATE TRIGGER TriggerViewDelete
ON Actor_Film
INSTEAD OF DELETE
AS
    IF EXISTS(SELECT * FROM deleted)
    BEGIN
        SET NOCOUNT ON;
        DELETE O
        FROM deleted d
        JOIN Actor O ON d.ActorId IN (O.ActorId, O.BirthYear);
        DELETE Actor_Film
        FROM deleted
        --WHERE deleted.ActorId = Actor_Film.ActorId;
        WHERE deleted.FirstName='Ryan';

    END;
GO*/

CREATE TRIGGER trig_DELETE_task2
ON Actor_Film
INSTEAD OF DELETE
AS
    /*MERGE Actor AS Actor
    USING (SELECT ActorId FROM deleted) AS ActorView (ID)
    ON Actor.ActorId = ActorView.ID
    WHEN MATCHED THEN DELETE;*/
    delete from Actor where LastName in (select LastName from deleted)
GO
SELECT * FROM Actor_Film
GO
/*
CREATE TRIGGER TriggerViewDelete ON Actor_Film
    INSTEAD OF DELETE AS
    UPDATE Actor
    SET IsDeleted = 1
    --WHERE ActorId = ActorId + (SELECT ActorId FROM deleted); --Country = 'U.S.'
GO
*/
DELETE FROM Actor
WHERE BirthYear = 1995
or (FirstName = 'Katherine');
GO
SELECT * FROM Actor_Film
GO

INSERT Actor (LastName, FirstName, BirthYear, Country)
VALUES ('McNamara', 'Katherine', 1995, 'U.S.');

/*
DELETE FROM Actor
WHERE (FirstName = 'Ryan')
GO
SELECT * FROM Actor_Film
GO*/

/*CREATE TRIGGER [dbo].[DeletedHardware] ON  [dbo].[Hardware] INSTEAD OF DELETE
AS
 IF EXISTS(SELECT * FROM deleted)
 BEGIN
  SET NOCOUNT ON;
  DELETE O
  FROM deleted d
  JOIN dbo.Orders O ON d.[IDHardware] IN(O.[IDHardware1],O.[IDHardware2],O.[IDHardware3]);
  DELETE dbo.Hardware
  FROM deleted
  WHERE deleted.[IDHardware]=Hardware.[IDHardware];
 END;
 */
/*
DELETE FROM Actor
FROM Film f INNER JOIN deleted
        ON f.Actor_Id = deleted.ActorId
        and deleted.

WHERE FirstName='Thomas';
GO
*/
SELECT 'DeleteView Trigger', * FROM Actor_Film;
GO
SELECT 'Delete View Trigger Actor', * FROM Actor;
GO

--Триггер на вставку для представления
IF OBJECT_ID ('TriggerViewInsert','TR') IS NOT NULL
   DROP TRIGGER TriggerViewInsert;
GO

CREATE TRIGGER TriggerViewInsert
    ON Actor_Film
    INSTEAD OF INSERT
    AS BEGIN
        INSERT INTO Film (Country, FilmName, Actor_Id)
        SELECT inserted.Country, inserted.FilmName,
        (SELECT ActorId FROM Actor WHERE Actor.LastName = inserted.LastName)
        from inserted
        INSERT INTO Actor (FirstName, LastName, BirthYear)
        SELECT inserted.FirstName, inserted.LastName, inserted.BirthYear
        from inserted
    END
GO

INSERT INTO Actor_Film(FirstName, LastName, BirthYear, Country, FilmName)
VALUES('Katherine', 'McNamara', 1995, 'U. S.', 'Girl vs the monster');
GO
/*
INSERT INTO Actor(ActorId, FirstName, LastName, BirthYear, Country)
VALUES (5, 'Kristen', 'Stewart', 1990, 'United States');
GO

INSERT INTO Film(FilmId, FilmName, Year, Genre, Country, Actor_Id)
VALUES(8, 'Inserted Film: Twillight', 2008, 'fantasy', 'U.S.', 5)
GO
*/
SELECT N'Table with Inserttrigger for view', * FROM Actor_Film
GO
SELECT 'Films', * FROM Film

--Триггер на изменение/добавление для представления
IF OBJECT_ID ('TriggerViewUpdate','TR') IS NOT NULL
   DROP TRIGGER TriggerViewUpdate;
GO

CREATE TRIGGER TriggerViewUpdate
    ON Actor_Film
    INSTEAD OF UPDATE
    AS BEGIN

        If UPDATE(FirstName) or UPDATE (BirthYear)
        PRINT 'FirstName or LastName was changed'
        PRINT 'BirthYear changed'
        Begin
            Update Actor
            Set FirstName = inserted.FirstName, BirthYear = inserted.BirthYear--,LastName = inserted.LastName
            FROM inserted
            Join Actor
            On Actor.LastName = inserted.LastName
        END
        IF UPDATE(Country)
        PRINT 'Country was changed'
        Begin
            Update Film
            Set Country = inserted.Country
            FROM inserted
            Join Film
            On Film.FilmName = inserted.FilmName
        END
        IF UPDATE(FilmName) --or UPDATE (ActorId)
            THROW 51000, 'Cannot change Filmname and ActorId', 0


/*
        INSERT INTO Actor
        SELECT *
        from inserted*/
    END
GO
GO
UPDATE Actor_Film
set BirthYear = BirthYear + 5008
SELECT 'Actors2', * FROM Actor

SELECT 'Films2', * FROM Film
/*
INSERT INTO Actor(FirstName, LastName, BirthYear, Country)
VALUES ('Channing', 'Tatum', 1980, 'U. S.');
GO

INSERT INTO Film(FilmName, Country, Actor_Id)
VALUES ('Updated film: Step Up', 'U. S.', 8);
GO
*/
INSERT INTO Actor_Film(FilmName, Country, FirstName, LastName, BirthYear)
VALUES('Updated film: Step Up', 'U. S.', 'Channing', 'Tatum', 1980)
--update Actor_Film set Filmname = Filmname + 'y', LastName = LastName + 'z'
update Actor_Film SET Country = Country + 'c'

UPDATE Actor SET FirstName = 'Tom'
WHERE FirstName = 'Thomas';
GO

UPDATE Actor SET FirstName = 'Dom'
WHERE FirstName = 'Dominic';
GO

SELECT * FROM Actor
SELECT N'Table with Updatetrigger for view', * FROM Actor_Film
GO

SELECT * FROM Film
GO