Триггеры

CREATE TABLE PurchaseHistory
([Id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[EmpId] INT NOT NULL FOREIGN KEY REFERENCES [Employees]([Id]),
[ClientId] INT NOT NULL FOREIGN KEY REFERENCES [Clients]([Id]),
[Product] nvarchar(MAX) NOT NULL CHECK ([Product] <> N''),
[Price] money NOT NULL CHECK ([Price] >= 0.0) DEFAULT 0.0,
[SDate] DATE NOT NULL CHECK (SDate <= getdate()) DEFAULT getdate())
GO
 
CREATE TRIGGER tg_purchase_history ON Clients
instead OF DELETE
AS
BEGIN
INSERT INTO PurchaseHistory(EmpId,ClientId,Product,Price,SDate)
SELECT EmpId,ClientId,Product,Price,SDate
FROM SaleOfGoods sg JOIN deleted d
ON d.Id=sg.ClientId
DELETE FROM Clients
WHERE Id IN (SELECT Id FROM deleted)
END

USE [master];
GO
 
IF db_id('Sales_db') IS NOT NULL
BEGIN
DROP DATABASE [Sales_db];
END
GO
 
CREATE DATABASE [Sales_db];
GO
 
USE [Sales_db];
GO
CREATE TABLE [Employees]
(
[Id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[Name] nvarchar(MAX) NOT NULL CHECK ([Name] <> N''),
[Surname] nvarchar(MAX) NOT NULL CHECK ([Surname] <> N''),
[Patronymic] nvarchar(MAX) NOT NULL CHECK ([Patronymic] <> N''),
[Email] nvarchar(MAX) NOT NULL CHECK ([Email]<>N''),
[Phone_Number] nvarchar(50) NOT NULL CHECK (Phone_Number <> N''))
GO
INSERT [Employees](Surname,Name,Patronymic,Email,Phone_Number)VALUES
(N'Petrova',N'Maria',N'Leonidovna',N'masha@gmail.com',N'050-255-00-01'),
(N'Fokina',N'Elena',N'Alekseevna',N'lena@gmail.com',N'067-255-00-01'),
(N'Lapina',N'Olga',N'Ivanovna',N'olya@gmail.com',N'063-255-00-01')
GO
CREATE TABLE [Clients]
(
[Id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[Name] nvarchar(MAX) NOT NULL CHECK ([Name] <> N''),
[Surname] nvarchar(MAX) NOT NULL CHECK ([Surname] <> N''),
[Patronymic] nvarchar(MAX) NOT NULL CHECK ([Patronymic] <> N''),
[Email] nvarchar(MAX) NOT NULL CHECK ([Email]<>N''),
[Phone_Number] nvarchar(50) NOT NULL CHECK (Phone_Number <> N''))
GO
INSERT [Clients](Surname,Name,Patronymic,Email,Phone_Number)VALUES
(N'Ivanov',N'Ivan',N'Ivanovich',N'ivan@gmail.com',N'066-356-56-84'),
(N'Petrov',N'Petr',N'Petrovich',N'petr@gmail.com',N'068-351-45-81'),
(N'Sergeev',N'Sergey',N'Sergeevich',N'sergey@gmail.com',N'098-351-45-56'),
(N'Mihaylov',N'Michael',N'Mihaylovich',N'misha@gmail.com',N'095-125-56-87'),
(N'Pupkin',N'Vasya',N'Petrovich',N'vasya@gmail.com',N'050-258-45-54')
GO
CREATE TABLE [SaleOfGoods]
(
[Id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[EmpId] INT NOT NULL FOREIGN KEY REFERENCES [Employees]([Id]) ON DELETE cascade ON UPDATE cascade,
[ClientId] INT NOT NULL FOREIGN KEY REFERENCES [Clients]([Id]) ON DELETE cascade ON UPDATE cascade,
[Product] nvarchar(MAX) NOT NULL CHECK ([Product] <> N''),
[Price] money NOT NULL CHECK ([Price] >= 0.0) DEFAULT 0.0,
[SDate] DATE NOT NULL CHECK (SDate <= getdate()) DEFAULT getdate())
GO
INSERT [SaleOfGoods](EmpId,ClientId,Product,Price,SDate)VALUES
(1,1,N'Laptop',500,'2020-01-02'),(1,5,N'Book',10,'2020-08-22'),(2,3,N'TV',200,'2020-02-23'),
(3,4,N'Sneakers',100,'2020-10-25'),(2,4,N'Jacket',50,'2020-11-01'),(1,4,N'Smartphone',200,'2020-10-01'),
(2,5,N'Chair',100,'2020-09-28'),(1,2,N'Glasses',50,'2020-07-23'),(1,3,N'Dress',100,'2020-03-17')
GO