labtrigger_BD

GO
USE master
GO
IF EXISTS (SELECT * from sys.databases where name='mybase')
        DROP DATABASE mybase
GO
CREATE DATABASE mybase
GO
ALTER DATABASE mybase SET MULTI_USER
GO
USE mybase
GO
CREATE TABLE Street
(StreetId int NOT NULL PRIMARY KEY,
Name nvarchar(25) NOT NULL)
GO
INSERT INTO Street(StreetId,Name)
                 VALUES (1,'Пушкинская')
INSERT INTO Street(StreetId,Name)
                 VALUES (2,'Лермонтовская')
INSERT INTO Street(StreetId,Name)
                 VALUES (3,'Чуковская')
INSERT INTO Street(StreetId,Name)
                 VALUES (4,'Толкиенская')
GO
--SELECT * FROM Street
--GO
CREATE TABLE Fam
(FamId int NOT NULL PRIMARY KEY,
Name nvarchar(25) NOT NULL)
GO
INSERT INTO Fam(FamId,Name)
         VALUES (1,'Яблочных')
INSERT INTO Fam(FamId,Name)
         VALUES (2,'Апельсинов')
INSERT INTO Fam(FamId,Name)
         VALUES (3,'Виноградов')
INSERT INTO Fam(FamId,Name)
         VALUES (4,'Грушев')
INSERT INTO Fam(FamId,Name)
         VALUES (5,'Арбузова')
GO
--SELECT * FROM Fam
--GO
CREATE TABLE Subscriber
(SubscriberId int NOT NULL Primary Key,
PhoneNumber nvarchar(9) NOT NULL
check(PhoneNumber like'[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'or PhoneNumber like'[0-9][0-9]-[0-9][0-9]-[0-9][0-9]'),
FamId int NOT NULL,
Iniz nvarchar(4) NOT NULL,
Indeks nvarchar(5) NOT NULL,
StreetId int NOT NULL,
HouseNumber int NOT NULL,
FlatNumber int NOT NULL,
Sex nvarchar(1) NOT NULL,
FOREIGN KEY (FamId) REFERENCES Fam(FamId),
FOREIGN KEY (StreetId) REFERENCES Street(StreetId))
GO
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (1,'745-34-19',1, 'А.А.','61005',2, 13,3,'М')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (2,'234-43-53',2, 'Р.В', '61005', 1,1,45,'М')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (3,'654-02-51',3, 'В.И.','61005', 3,11,2,'Ж')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (4,'350-10-20',4, 'Ф.И.', '61005', 4, 7,223,'М')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (5,'787-34-66',1, 'А.С.', '61005', 3,13,89,'Ж')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (6,'787-37-56',2, 'А.С.', '61005', 2,112,13,'М')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (7,'777-34-66',2, 'К.В.', '61005', 1,2,13,'М')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (8,'787-34-12',4, 'М.С.', '61005', 2,112,13,'Ж')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (9,'49-72-34',4, 'К.М.', '61005', 4,132,13,'М')
INSERT INTO Subscriber(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES (10,'49-72-34',5, 'К.М.', '61005', 4,132,13,'М')
GO
--SELECT *
--FROM Subscriber
--GO

--CREATE TABLE SubscriberCopy
--(SubscriberId int NOT NULL Primary Key,
--PhoneNumber nvarchar(9) NOT NULL,
--FamId int NOT NULL,
--Iniz nvarchar(4) NOT NULL,
--Indeks nvarchar(5) NOT NULL,
--StreetId int NOT NULL,
--HouseNumber int NOT NULL,
--FlatNumber int NOT NULL,
--Sex nvarchar(1) NOT NULL,
--FOREIGN KEY (FamID) REFERENCES Fam(FamId),
--FOREIGN KEY (StreetId) REFERENCES Street(StreetId))
--GO
--INSERT INTO SubscriberCopy(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
--         VALUES (1,'745-34-19',1, 'А.А.','61005',2, 13,3,'М')
--INSERT INTO SubscriberCopy(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
--         VALUES (2,'234-43-53',2, 'Р.В', '61005', 1,1,45,'М')
--INSERT INTO SubscriberCopy(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
--         VALUES (3,'654-02-51',3, 'В.И.','61005', 3,11,2,'Ж')
--INSERT INTO SubscriberCopy(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
--         VALUES (4,'350-10-20',4, 'Ф.И.', '61005', 4, 7,223,'М')
--INSERT INTO SubscriberCopy(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
--         VALUES (5,'787-34-66',1, 'А.С.', '61005', 3,13,89,'Ж')
--INSERT INTO SubscriberCopy(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
--         VALUES (6,'787-37-56',2, 'А.С.', '61005', 2,112,13,'М')
--INSERT INTO SubscriberCopy(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
--         VALUES (7,'777-34-66',2, 'К.В.', '61005', 1,2,13,'М')
--INSERT INTO SubscriberCopy(SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
--         VALUES (8,'787-34-12',4, 'М.С.', '61005', 2,112,13,'Ж')
go
--create table SubscriberCopy like Subscriber
--insert SubscriberCopy select * from Subscriber
select SubscriberId, PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex
into SubscriberCopy from Subscriber
GO
select * from SubscriberCopy
go
-----------1
UPDATE SubscriberCopy
SET HouseNumber=5
WHERE SubscriberId=4
GO
---------2
SELECT SubscriberId, PhoneNumber, Fam.Name, Iniz,Indeks,Street.Name, HouseNumber, FlatNumber, Sex
FROM Subscriber,Fam,Street
WHERE Subscriber.FamId=Fam.FamId and Subscriber.StreetId=Street.StreetId and Fam.Name NOT IN
(SELECT Fam.Name FROM Fam WHERE Fam.Name like '[АЕЁИОУЭЮЯ]%')
--(SELECT Fam.Name FROM Fam WHERE Fam.Name LIKE('А%') or Fam.Name LIKE ('О%')  or Fam.Name like('И%') or Fam.Name like('Е%') or Fam.Name like('У%') or Fam.Name like('Э%') or Fam.Name like('Я%'))
UNION SELECT SubscriberId, PhoneNumber, Fam.Name, Iniz,Indeks,Street.Name, HouseNumber, FlatNumber, Sex
FROM SubscriberCopy,Fam,Street
WHERE SubscriberCopy.FamId=Fam.FamId and SubscriberCopy.StreetId=Street.StreetId and Fam.Name NOT IN
--SELECT Fam.Name FROM Fam WHERE Fam.Name LIKE('А%') or Fam.Name LIKE ('О%')  or Fam.Name like('И%') or Fam.Name like('Е%') or Fam.Name like('У%') or Fam.Name like('Э%') or Fam.Name like('Я%'))
(SELECT Fam.Name FROM Fam WHERE Fam.Name like '[АЕЁИОУЭЮЯ]%')
ORDER BY Street.Name
GO
-------------3
IF OBJECT_ID('MyDelete1','TRIGGER') IS NOT NULL
DROP TRIGGER MyDelete1
GO
CREATE TRIGGER MyDelete1
ON Subscriber
instead of DELETE
AS
    delete from Subscriber where Subscriber.SubscriberId in(select SubscriberId from deleted)
    and PhoneNumber  like '[0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
go
delete from SubscriberCopy where SubscriberId=8
go
select * from SubscriberCopy
go
------------4
IF OBJECT_ID('MyInsert1','TRIGGER') IS NOT NULL
DROP TRIGGER MyInsert1
GO
CREATE TRIGGER MyInsert1
ON Fam
INSTEAD OF INSERT
AS
DECLARE @s1 nvarchar(25)
IF NOT EXISTS(SELECT * FROM Inserted
WHERE Name IN
(SELECT Name FROM Fam))
BEGIN
    INSERT INTO Fam(Name) SELECT
    Name FROM Inserted
    print('Subject added')
END
ELSE
    PRINT('Unable to add')
GO
INSERT INTO Fam(FamId,Name)
         VALUES (6,'Яблочных')
SELECT * FROM Fam
-----------------5
IF OBJECT_ID('MyDelete2','TRIGGER') IS NOT NULL
DROP TRIGGER MyDelete2
GO
CREATE TRIGGER MyDelete2
ON Fam
INSTEAD OF DELETE
AS
    DELETE FROM Subscriber WHERE Subscriber.FamId IN (SELECT FamId FROM deleted)
    DELETE FROM Fam WHERE Fam.FamId IN(SELECT FamId FROM deleted)
GO
DELETE FROM Fam
WHERE Name='Арбузова'
GO
--------------------6
IF OBJECT_ID('UpdateInsert','TRIGGER') IS NOT NULL
DROP TRIGGER UpdateInsert
GO
CREATE TRIGGER UpdateInsert
ON Subscriber
FOR UPDATE,INSERT
AS
    --if exists((select * from Subscriber union select * from inserted) group by StreetId having count(*)>3)
    if exists(select * from Subscriber
    group by StreetId having count(*)>3)
    begin
        print ('Unable to add')
        rollback transaction
    end
    else
        begin
            print('Added')
        end
GO
SELECT *
FROM SubscriberCopy
GO