· 8 years ago · Jun 06, 2018, 08:12 PM
1create database test23
2
3use test23
4
5create table Kupci (
6KupacID int primary key identity(1,1),
7Ime nvarchar(10),
8Prezime nvarchar(20),
9Adresa nvarchar(20)
10)
11
12create table KupciAudit
13(
14AuditID int primary key identity(1,1),
15KupacID int ,
16Ime nvarchar(10),
17Prezime nvarchar(20),
18Adresa nvarchar(20),
19Komanda nvarchar(20),
20Korisnik nvarchar(50),
21Datum DATETIME
22)
23
24create trigger tr_Kupci_Insert
25on Kupci AFTER INSERT AS
26INSERT INTO KupciAudit
27(KupacID,Ime, Prezime,Adresa, Komanda, Korisnik, Datum)
28SELECT i.KupacID,
29 i.Ime,
30 i.Prezime,
31 i.Adresa,
32 'INSERT',
33 SYSTEM_USER,
34 GETDATE()
35FROM inserted as i
36
37INSERT INTO Kupci(Ime, Prezime, Adresa) values ('Jasmin', 'Azemovic', 'FIT'),
38('Admir', 'Sehidic', 'FIT')
39
40SELECT * FROM Kupci
41SELECT * FROM KupciAudit
42
43create trigger tr_Kupci_Update
44on Kupci AFTER UPDATE AS
45INSERT INTO KupciAudit
46(KupacID,Ime, Prezime,Adresa, Komanda, Korisnik, Datum)
47SELECT d.KupacID,
48 d.Ime,
49 d.Prezime,
50 d.Adresa,
51 'UPDATE',
52 SYSTEM_USER,
53 GETDATE()
54FROM deleted as d
55
56update Kupci set Ime='Neki2' where KupacID=2
57
58select*from Kupci
59select*from KupciAudit
60
61create trigger tr_Kupci_Delete
62on Kupci after delete as
63insert into KupciAudit
64(KupacID,Ime, Prezime,Adresa, Komanda, Korisnik, Datum)
65SELECT d.KupacID,
66 d.Ime,
67 d.Prezime,
68 d.Adresa,
69 'DELETE',
70 SYSTEM_USER,
71 GETDATE()
72FROM deleted as d
73
74DELETE FROM Kupci where KupacID=2
75
76select*from KupciAudit
77
78--sve zajedno
79
80create trigger tr_Kupci_IUD
81on Kupci after insert,update, delete
82as
83if exists (select*from inserted) and not exists (select*from deleted)
84begin
85INSERT INTO KupciAudit
86(KupacID,Ime, Prezime,Adresa, Komanda, Korisnik, Datum)
87SELECT i.KupacID,
88 i.Ime,
89 i.Prezime,
90 i.Adresa,
91 'INSERT',
92 SYSTEM_USER,
93 GETDATE()
94FROM inserted as i
95end
96if exists (select*from inserted) and exists (select*from deleted)
97begin
98insert into KupciAudit
99(KupacID,Ime,Prezime, Adresa, Komanda,Korisnik,Datum)
100select d.KupacID,
101 d.Ime,
102 d.Prezime,
103 d.Adresa,
104 'UPDATE',
105 SYSTEM_USER,
106 GETDATE()
107from deleted as d
108END
109IF EXISTS (SELECT*FROM deleted) AND NOT EXISTS (SELECT*FROM inserted)
110BEGIN
111INSERT INTO KupciAudit
112(KupacID,Ime,Prezime, Adresa, Komanda,Korisnik,Datum)
113select d.KupacID,
114 d.Ime,
115 d.Prezime,
116 d.Adresa,
117 'DELETE',
118 SYSTEM_USER,
119 GETDATE()
120from deleted as d
121END
122
123DELETE FROM KupciAudit
124select*from KupciAudit
125
126insert into Kupci(Ime,Prezime,Adresa) values ('Neko', 'Nekic', 'Mostar')
127insert into Kupci(Ime, Prezime, Adresa) values ('Neko2', 'Nekic2', 'Mostar')
128
129update Kupci set Ime='Neka' where KupacID=4
130
131Delete from Kupci where KupacID=4
132
133select*from Kupci
134
135--instead of
136
137create trigger tr_Kupci_io_delete
138on Kupci instead of delete as
139insert into KupciAudit
140(KupacID,Ime,Prezime,Adresa,Komanda,Korisnik,Datum)
141select d.KupacID,
142 d.Ime,
143 d.Prezime,
144 d.Adresa,
145 'TRYING TO DELETE',
146 SYSTEM_USER,
147 GETDATE()
148from deleted as d
149
150DELETE FROM Kupci WHERE KupacID=3
151
152SELECT * FROM KupciAudit