QZVp7J30

1
2-- stap 1
3CREATE TABLE [dbo].[ProductAudit2](
4	Id [int] NOT NULL PRIMARY KEY IDENTITY,
5	UserName [nvarchar](128) NOT NULL,
6	DatumEnTijd DATETIME NULL,
7	Operation NCHAR(6) NULL
8	)
9
10-- stap 2
11ALTER TRIGGER t_oef_extra_2 
12ON Product -- tiggering tbl
13FOR INSERT, DELETE, UPDATE
14AS BEGIN
15	-- code
16	DECLARE @varOperation NCHAR(6)
17
18	-- weten wat er gebeurd is (i, u, d)? ==> condtioneel (IF ...)
19	IF NOT EXISTS (SELECT * FROM inserted) BEGIN
20		-- het is DELETE
21		SET @varOperation = 'DELETE'
22	END  
23	ELSE IF NOT EXISTS (SELECT * FROM deleted) BEGIN
24		-- het is DELETE
25		SET @varOperation = 'INSERT'
26	END 
27	ELSE SET @varOperation = 'UPDATE'
28		
29	-- wegschrijven in de ProductAudit tbl (INSERT doen)
30	INSERT INTO ProductAudit2(UserName, DatumEnTijd, Operation)
31	VALUES (USER_NAME(), GETDATE(), @varOperation)
32
33END
34
35----- TESTEN VAN DE TRIGGER
36INSERT INTO Product(ProductID, ProductName) VALUES (452, 'mijn product');
37
38UPDATE Product
39SET ProductName = 'naam gewijzigd'
40WHERE ProductID = 452;
41
42DELETE Product
43WHERE ProductID = 452;
44
45SELECT * FROM ProductAudit2;