aGbthDcY

1create table Logs(
2	logid int identity(1,1) not null,
3	date date not null,
4	tablename varchar(50) not null,
5	operationdetails varchar(200) not null
6	)
7go 
8
9create trigger t_categ_insert
10on Production.Categories
11instead of insert 
12as 
13   begin
14	   if not exists(select * from Production.Categories pc, inserted where inserted.categoryname = pc.categoryname)
15			begin
16			insert into Production.Categories (categoryname, description) 
17			values ((select categoryname from inserted),(select description from inserted))
18			insert into Logs (date, tablename, operationdetails)
19			values (getdate(), 'Production.Categories', 'COMPLETED - adding new category')
20			select * from inserted
21			end
22		else
23			begin
24			insert into Logs (date, tablename, operationdetails)
25			values (getdate(), 'Production.Categories', 'ERROR category already exists - adding new category: ')
26			select 'Category already exists'
27			end
28	end
29go 
30
31insert into Production.Categories (categoryname, description)
32values ('Boxes', 'paper boxes')
33
34insert into Production.Categories (categoryname, description)
35values ('Cars', 'best cars ever!')
36
37select * from Production.Categories
38select * from Logs
39go
40
41create trigger t_categ_update
42on Production.Categories
43instead of update
44as
45begin
46	if exists(select categoryname from Production.Categories where categoryname in (select categoryname from inserted))
47		begin
48		insert into Logs (date, tablename, operationdetails)
49		values (getdate(), 'Production.Categories', 'ERROR already exists - updating category name to: ' + (select categoryname from inserted))
50		select 'Category already exists'
51		end
52	else
53		begin
54		update Production.Categories set categoryname = (select categoryname from inserted)
55		where categoryname = (select categoryname from deleted)
56		insert into Logs (date, tablename, operationdetails) 
57		values (getdate(), 'Production.Categories', 'COMPLETED - updating category name')
58		select * from inserted
59		end
60end
61go
62
63UPDATE Production.Categories SET categoryname = 'Boxy Boxes' WHERE categoryname = 'Boxes'
64UPDATE Production.Categories SET categoryname = 'Supercars' WHERE categoryname = 'Cars'
65UPDATE Production.Categories SET categoryname = 'Boxy Boxes' WHERE categoryname = 'Supercars'
66
67select * from Production.Categories
68select * from Logs
69go
70
71create trigger t_categ_delete
72on Production.Categories
73instead of delete
74as
75begin
76	if not exists (select categoryname from deleted)
77		begin
78		insert into Logs (date, tablename, operationdetails)
79		values (getdate(), 'Production.Categories', 'ERROR doen`t exists - deleting category')
80		select 'Category doen`t exists'
81		end
82	else if exists( select productname from Production.Products where categoryid in (select categoryid from deleted))
83		begin
84		insert into Logs (date, tablename, operationdetails)
85		values (getdate(), 'Production.Categories', 'ERROR used in other tables - deleting category')
86		select 'Category is used in other tables'
87		end
88	else
89		begin
90		delete from Production.Categories where categoryname = (select categoryname from deleted);
91		insert into Logs (date, tablename, operationdetails)
92		values (getdate(), 'Production.Categories', 'COMPLETED - deleting category')
93		select *, 'deleted' from deleted
94		end
95end
96go
97
98delete from  Production.Categories where categoryname ='Seafood'
99delete from  Production.Categories where categoryname ='Supercars'
100
101select * from Production.Categories
102select * from Production.Products
103select *from Logs