9

USE master;
GO

IF db_id (N'Bank_lab9') IS NOT NULL
DROP DATABASE Bank_lab9;
go

CREATE DATABASE Bank_lab9 
	ON (NAME = Bank_dat_lab9, FILENAME = 'C:\data\Bankdatlab9.mdf',
	SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
	LOG ON (NAME = Bank_log_lab9, FILENAME = 'C:\data\Bankloglab9.ldf',
	SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO

USE Bank_lab9;
GO

CREATE TABLE Human (idofperson int IDENTITY(0, 1) PRIMARY KEY,
					firstname char(50),
					secondname char(50),
					location char(150),
					phone int);
GO

ALTER TABLE Human ADD age int CHECK (age > 0);
GO

ALTER TABLE Human ADD email char(25) DEFAULT ('н/д');
GO

ALTER TABLE Human ADD dateofbirth datetime DEFAULT (getdate());
GO

CREATE TABLE Employee (uniqid uniqueidentifier PRIMARY KEY ROWGUIDCOL DEFAULT (newid()),
						post char(50),
						passnumber int,
						experience int);
GO

CREATE TABLE Client (clientid int PRIMARY KEY,
					accountnumber int);
GO

CREATE SEQUENCE ForClient START WITH 0 INCREMENT BY 1;
GO

INSERT Client (clientid, accountnumber)
VALUES (NEXT VALUE FOR ForClient, 1);
INSERT Client (clientid, accountnumber)
VALUES (NEXT VALUE FOR ForClient, 2);
GO

/*
SELECT * FROM Client;
GO
*/

CREATE TABLE Operation (idoper int PRIMARY KEY,
						dt datetime);
GO

CREATE TABLE Credit (ido int FOREIGN KEY REFERENCES Operation(idoper) 
						ON DELETE CASCADE
						ON UPDATE CASCADE,
					sumofmoney money,
					procent decimal DEFAULT (15) NOT NULL);
GO


INSERT Operation (idoper, dt) VALUES (1, '20131201');
INSERT Operation (idoper, dt) VALUES (2, '20131222');
INSERT Operation (idoper, dt) VALUES (3, '20140111');
INSERT Operation (idoper, dt) VALUES (4, '20140112');
INSERT Credit(ido, sumofmoney, procent) VALUES (1, $25000, 5.0);
INSERT Credit(ido, sumofmoney, procent) VALUES (2, $1000000, 15.0);
INSERT Credit(ido, sumofmoney, procent) VALUES (3, $1000, 5.0);
GO

--Create view with schemabinding.
IF OBJECT_ID ('IndexView', 'view') IS NOT NULL
DROP VIEW IndexView;
GO

CREATE VIEW IndexView WITH SCHEMABINDING AS
	SELECT a.idoper, a.dt, b.sumofmoney FROM
	dbo.Operation a INNER JOIN dbo.Credit b ON a.idoper = b.ido; 
GO

/*SELECT * FROM IndexView;
GO
*/
-- LAB 9
/*
Для одной из таблиц пункта 2 задания 7 
создать триггеры на вставку, удаление и 
добавление, при выполнении заданных 
условий один из триггеров должен 
инициировать возникновение ошибки 
(RAISERROR / THROW).
*/
--1.1
IF OBJECT_ID ('insert_in_Operation','TR') IS NOT NULL
   DROP TRIGGER insert_in_Operation;
GO

CREATE TRIGGER insert_in_Operation ON Operation FOR INSERT 
	AS PRINT N'New values have inserted in Operation.';
GO
--1.2
IF OBJECT_ID ('update_on_Operation','TR') IS NOT NULL
   DROP TRIGGER update_on_Operation;
GO

CREATE TRIGGER update_on_Operation ON Operation AFTER UPDATE
	AS SELECT * FROM Operation;
GO
--1.3
IF OBJECT_ID ('delete_in_Operation','TR') IS NOT NULL
   DROP TRIGGER delete_in_Operation;
GO

CREATE TRIGGER delete_in_Operation ON Operation INSTEAD OF DELETE
	AS THROW 51000, 'Cant delete.', 1;
GO

INSERT Operation (idoper, dt) VALUES (6, '20130405');
--UPDATE Operation SET idoper = 5 WHERE idoper = 6;
--DELETE FROM Operation WHERE dt = 5;
GO

DROP TRIGGER insert_in_Operation;
DROP TRIGGER update_on_Operation;
DROP TRIGGER delete_in_Operation;
GO
/*
Для представления пункта 2 задания 7 
создать триггеры на вставку, удаление и 
добавление, обеспечивающие возможность 
выполнения операций с данными 
непосредственно через представление.
*/
--2.1
IF OBJECT_ID ('insert_IndexView','TR') IS NOT NULL
   DROP TRIGGER insert_IndexView;
GO

CREATE TRIGGER insert_IndexView ON IndexView INSTEAD OF INSERT 
	AS IF NOT EXISTS(SELECT * FROM Operation JOIN inserted ON Operation.idoper = inserted.idoper)
		BEGIN
			INSERT Operation(idoper, dt) SELECT idoper, dt FROM inserted;
			INSERT Credit(ido, sumofmoney) SELECT idoper, sumofmoney FROM inserted;
		END;
GO
--2.2
IF OBJECT_ID ('delete_IndexView','TR') IS NOT NULL
   DROP TRIGGER delete_IndexView;
GO

CREATE TRIGGER delete_IndexView ON IndexView INSTEAD OF DELETE
	AS DELETE FROM Operation WHERE idoper IN (SELECT idoper FROM deleted);
	   DELETE FROM Credit WHERE ido IN (SELECT idoper FROM deleted);
GO
--2.3
IF OBJECT_ID ('update_IndexView','TR') IS NOT NULL
   DROP TRIGGER update_IndexView;
GO

CREATE TRIGGER update_IndexView ON IndexView INSTEAD OF UPDATE
	AS 
		IF (UPDATE(idoper)) 
			BEGIN
				IF EXISTS (SELECT * FROM Operation JOIN inserted ON inserted.idoper = Operation.idoper)
				BEGIN
					RAISERROR('PK exists', 16, 1);
				END
				ELSE
				BEGIN
					CREATE TABLE curtab1 (id1 int IDENTITY PRIMARY KEY, idnew int); 
					CREATE TABLE curtab2 (id2 int IDENTITY PRIMARY KEY, idold int);
				
					INSERT curtab1 (idnew) SELECT idoper FROM inserted;
					INSERT curtab2 (idold) SELECT idoper FROM deleted;

					UPDATE Operation SET idoper = idnew 
					FROM curtab1 INNER JOIN curtab2 ON curtab1.id1 = curtab2.id2
					WHERE Operation.idoper = curtab2.idold;
				
					UPDATE Credit SET ido = idnew 
					FROM curtab1 INNER JOIN curtab2 ON curtab1.id1 = curtab2.id2
					WHERE Credit.ido = curtab2.idold;
					DROP TABLE curtab1;
					DROP TABLE curtab2;
				END;
			END;
GO
INSERT IndexView(idoper, dt, sumofmoney) VALUES (1, '20130908', $4000);
INSERT IndexView(idoper, dt, sumofmoney) VALUES (144, '20130908', $4000);
GO
SELECT * FROM Operation;
GO
SELECT * FROM Credit;
GO
DELETE FROM IndexView WHERE idoper = 144;
GO
SELECT * FROM Operation;
GO
SELECT * FROM Credit;
GO
UPDATE IndexView SET idoper = 100 WHERE idoper = 2;
GO
SELECT * FROM Operation;
GO
SELECT * FROM Credit;
GO

IF OBJECT_ID ('IndexView', 'view') IS NOT NULL
DROP VIEW IndexView;
GO

CREATE VIEW IndexView WITH SCHEMABINDING AS
	SELECT a.idoper, a.dt, b.sumofmoney FROM
	dbo.Operation a INNER JOIN dbo.Credit b ON a.idoper = b.ido; 
GO

SELECT * FROM IndexView;
GO