USE MASTER;
GO
IF db_id (N'lab9') IS NOT NULL
DROP DATABASE lab9;
go
CREATE DATABASE lab9
ON (NAME = lab9_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab9.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = lab9_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
USE lab9;
GO
CREATE TABLE country (
countryid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
namec char(30));
GO
CREATE TABLE ruler (
rulerid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
name char(30),
ruler_country int FOREIGN KEY REFERENCES country(countryid)
ON DELETE CASCADE
ON UPDATE CASCADE
);
GO
INSERT country (namec) VALUES ('Rome');
INSERT country (namec) VALUES ('Greece');
INSERT ruler (ruler_country, name) VALUES (1, 'Caesar');
INSERT ruler (ruler_country, name) VALUES (1, 'Octavian');
INSERT ruler (ruler_country, name) VALUES (2, 'Pericles');
GO
CREATE VIEW C_lView WITH SCHEMABINDING AS
SELECT a.namec, b.name FROM
dbo.country a INNER JOIN dbo.ruler b ON a.countryid = b.ruler_country
GO
-- вставка
CREATE TRIGGER insert_country ON country FOR INSERT
AS PRINT 'insert';
SELECT * FROM country;
GO
-- пример
/*INSERT country (countryid, nameс) VALUES (5, 'China');
GO*/
-- изменение
CREATE TRIGGER update_country ON country AFTER UPDATE
AS PRINT 'update';
SELECT * FROM country;
GO
-- пример
/*UPDATE country SET countryid = 10 WHERE countryid = 5;
GO*/
-- удаление
CREATE TRIGGER delete_country ON country INSTEAD OF DELETE
AS THROW 51000, 'Cant delete', 1;
GO
-- пример
/*DELETE FROM country WHERE countryid = 1;
GO*/
DROP TRIGGER insert_country;
DROP TRIGGER update_country;
DROP TRIGGER delete_country;
GO
-- вставка через представление
CREATE TRIGGER insert_C_lView ON C_lView INSTEAD OF INSERT
AS
BEGIN
INSERT country(namec) SELECT namec FROM inserted WHERE namec NOT IN (SELECT namec FROM country);
CREATE TABLE table1 (ins_namec char(30), ins_name char(30),);
INSERT table1 (ins_namec, ins_name) SELECT namec, name FROM inserted;
INSERT ruler(ruler_country, name) SELECT DISTINCT a.countryid, b.ins_name FROM dbo.country a INNER JOIN dbo.table1 b ON a.namec = b.ins_namec WHERE b.ins_name NOT IN (SELECT name FROM ruler);
DROP TABLE table1;
END;
GO
-- пример
INSERT C_lView(namec, name) VALUES ('Egypt', 'Ramses');
INSERT C_lView(namec, name) VALUES ('Egypt', 'Ramses II');
INSERT C_lView(namec, name) VALUES ('Egypt', 'Ramses III'), ('Laplandia', 'Santa'), ('Egypt', 'Ramses V'), ('Egypt', 'Ramses V');
GO
SELECT * FROM country;
GO
SELECT * FROM ruler;
GO
SELECT * FROM C_lView;
GO
-- удаление через представление
CREATE TRIGGER delete_C_lView ON C_lView INSTEAD OF DELETE
AS
DELETE FROM ruler WHERE name IN (SELECT name FROM deleted);
DELETE FROM country WHERE countryid NOT IN (SELECT ruler_country FROM ruler);
GO
-- пример
DELETE FROM C_lView WHERE name = 'Caesar';
DELETE FROM C_lView WHERE namec = 'Egypt';
GO
SELECT * FROM country;
GO
SELECT * FROM ruler;
GO
SELECT * FROM C_lView;
GO
-- изменение через представление
CREATE TRIGGER update_C_lView ON C_lView INSTEAD OF UPDATE
AS IF (UPDATE(namec))
BEGIN
IF EXISTS (SELECT * FROM country JOIN inserted ON inserted.namec = country.namec)
BEGIN
THROW 51000, 'Cant update', 1;
END
ELSE
BEGIN
CREATE TABLE table1 (id1 int IDENTITY PRIMARY KEY, new_namec char(30));
CREATE TABLE table2 (id2 int IDENTITY PRIMARY KEY, old_namec char(30));
INSERT table1 (new_namec) SELECT namec FROM inserted;
INSERT table2 (old_namec) SELECT namec FROM deleted;
UPDATE country SET namec = new_namec
FROM table1 INNER JOIN table2 ON table1.id1 = table2.id2
WHERE country.namec = table2.old_namec;
DROP TABLE table1;
DROP TABLE table2;
END;
END;
IF (UPDATE(name))
BEGIN
IF EXISTS (SELECT * FROM ruler JOIN inserted ON inserted.name = ruler.name)
BEGIN
THROW 51000, 'Cant update', 1;
END
ELSE
BEGIN
CREATE TABLE table1 (id1 int IDENTITY PRIMARY KEY, new_name char(30));
CREATE TABLE table2 (id2 int IDENTITY PRIMARY KEY, old_name char(30));
INSERT table1 (new_name) SELECT name FROM inserted;
INSERT table2 (old_name) SELECT name FROM deleted;
UPDATE ruler SET name = new_name
FROM table1 INNER JOIN table2 ON table1.id1 = table2.id2
WHERE ruler.name = table2.old_name;
DROP TABLE table1;
DROP TABLE table2;
END;
END;
GO
-- пример
UPDATE C_lView SET namec = 'Rome II' WHERE namec = 'Rome';
UPDATE C_lView SET name = 'Caesar II' WHERE name = 'Santa';
GO
SELECT * FROM country;
GO
SELECT * FROM ruler;
GO
SELECT * FROM C_lView;
GO
DROP TRIGGER insert_C_lView;
DROP TRIGGER delete_C_lView;
DROP TRIGGER update_C_lView;
GO