· 5 years ago · Mar 08, 2020, 08:20 PM
1-- Check if DB exists --
2IF db_id('kejans_softdelete') IS NOT NULL
3 BEGIN
4 USE master
5 DROP DATABASE "kejans_softdelete"
6 END
7GO
8
9CREATE DATABASE "kejans_softdelete"
10GO
11
12USE "kejans_softdelete"
13GO
14
15
16
17/*
18Create Tables
19*/
20CREATE TABLE Person
21(
22 PersonId INT NOT NULL,
23 FirstName VARCHAR(256) NOT NULL,
24 LastName VARCHAR(256) NOT NULL,
25 PictureId INT,
26 PictureDeletedAt DATETIME2,
27 CreatedAt DATETIME2,
28 DeletedAt DATETIME2,
29 CONSTRAINT PK_Person PRIMARY KEY (PersonId, DeletedAt),
30);
31
32
33CREATE TABLE Contact
34(
35 ContactId INT NOT NULL,
36 PersonId INT NOT NULL,
37 PersonDeletedAt DATETIME2,
38 ContactName VARCHAR(256) NOT NULL,
39 CreatedAt DATETIME2,
40 DeletedAt DATETIME2,
41 CONSTRAINT PK_Contact PRIMARY KEY (ContactId, DeletedAt),
42 CONSTRAINT Fk_Contact_Person FOREIGN KEY (PersonId, PersonDeletedAt) REFERENCES Person (PersonId, DeletedAt)
43 ON UPDATE CASCADE
44);
45
46CREATE TABLE Picture
47(
48 PictureId INT NOT NULL,
49 PersonId INT NOT NULL,
50 PictureUrl varchar(256) NOT NULL,
51 CreatedAt DATETIME2,
52 DeletedAt DATETIME2,
53 PersonDeletedAt DATETIME2 NOT NULL,
54 CONSTRAINT PK_Picture PRIMARY KEY (PictureId, DeletedAt),
55 CONSTRAINT FK_Person_Contact FOREIGN KEY (PersonId, PersonDeletedAt) REFERENCES Contact (ContactId, DeletedAt)
56 ON UPDATE CASCADE
57);
58
59
60--Create variables
61DECLARE @Time1 DATETIME2
62DECLARE @Time2 DATETIME2
63DECLARE @Time3 DATETIME2
64
65SET @Time1 = '2020-03-08'
66SET @Time2 = '2020-04-09'
67SET @Time3 = '9999-12-31'
68
69
70INSERT INTO Person(PersonId, FirstName, LastName, PictureId, PictureDeletedAt, CreatedAt, DeletedAt)
71VALUES (1, 'Edgar', 'Savisaar', null, @Time3, @Time1, @Time3),
72 (2, 'Lennart', 'Meri', null, @Time3, @Time1, @Time3),
73 (3, 'Hannes', 'Hanso', null, @Time3, @Time1, @Time3),
74 (4, 'Hendrik', 'Tiido', null, @Time3, @Time1, @Time3);
75
76DECLARE @Time1 DATETIME2
77DECLARE @Time2 DATETIME2
78DECLARE @Time3 DATETIME2
79
80SET @Time1 = '2020-03-08'
81SET @Time2 = '2020-04-09'
82SET @Time3 = '9999-12-31'
83INSERT INTO Contact(ContactId, PersonId, PersonDeletedAt, ContactName, CreatedAt, DeletedAt)
84VALUES (1, 3, @Time3, 'skype:kejans', @Time1, @Time3),
85 (2, 3, @Time3, 'msn:kejans', @Time1, @Time3),
86 (3, 4, @Time3, 'nr:56343223', @Time1, @Time3),
87 (4, 4, @Time3, 'email:kejans', @Time1, @Time3);
88
89DECLARE @Time1 DATETIME2
90DECLARE @Time2 DATETIME2
91DECLARE @Time3 DATETIME2
92
93SET @Time1 = '2020-03-08'
94SET @Time2 = '2020-04-09'
95SET @Time3 = '9999-12-31'
96INSERT INTO Picture(PictureId, PersonId, PictureUrl, CreatedAt, DeletedAt, PersonDeletedAt)
97VALUES (1, 1, 'myurlf0r44200.ru', @Time1, @Time3, @Time3),
98 (2, 2, 'uwuwuewue.ru', @Time1, @Time3, @Time3),
99 (3, 3, 'samueuewueue.net', @Time1, @Time3, @Time3),
100 (4, 4, 'kalamari.lv', @Time1, @Time3, @Time3);
101
102
103SELECT *
104from Person
105 LEFT JOIN Contact ON Person.PersonId = Contact.PersonId
106 LEFT JOIN Picture ON Person.PersonId = Picture.PersonId
107
108
109--Soft delete Hannes
110UPDATE Person
111SET DeletedAt = '2020-04-09'
112WHERE FirstName LIKE 'Hannes'
113
114--Show results
115SELECT *
116from Contact
117WHERE ContactId = 3
118
119--Soft delete Hannes's Contact
120UPDATE Contact
121SET PersonDeletedAt = '2020-04-09'
122WHERE PersonId = 3
123
124--Show results
125SELECT *
126from Contact
127WHERE ContactId = 3
128
129--Soft delete picture
130UPDATE Picture
131SET DeletedAt = '2020-04-09'
132WHERE PersonId = 3
133
134--Show results
135SELECT *
136from Contact
137WHERE ContactId = 3
138
139
140-- soft delete in 1->1-0 relationship between Picture and Person table
141-- (Person is deleted and Picture is deleted as well)
142UPDATE Person
143SET DeletedAt = '2020-04-09'
144WHERE PersonId = 3
145UPDATE Picture
146SET DeletedAt = '2020-04-09'
147WHERE PersonID = 3
148
149
150-- soft update paret
151
152UPDATE Person
153SET FirstName = 'Kevin'
154WHERE PersonId = 3
155 AND DeletedAt = '2020-04-09'
156
157--Show results
158SELECT *
159from Person
160WHERE PersonId = 3