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