Table auditing using triggers

The company has decided to keep track of changes made to all the most important tables. One of those tables is Orders.

Any modification made to the content of Orders should be inserted into TablesAudit.

Bu egzersiz

Building and Optimizing Triggers in SQL Server

kursunun bir parçasıdır

Kursu Görüntüle

Egzersiz talimatları

Create a new AFTER trigger on the Orders table.
Set the trigger to fire for INSERT, UPDATE, and DELETE statements.

Uygulamalı interaktif egzersiz

Bu örnek kodu tamamlayarak bu egzersizi bitirin.

-- Add a trigger that tracks table changes
___ ___ OrdersAudit
ON Orders
___ INSERT, ___, ___
AS
	DECLARE @Insert BIT = 0;
	DECLARE @Delete BIT = 0;
	IF EXISTS (SELECT * FROM inserted) SET @Insert = 1;
	IF EXISTS (SELECT * FROM deleted) SET @Delete = 1;
	INSERT INTO ___ (TableName, EventType, UserAccount, EventDate)
	SELECT 'Orders' AS TableName
	       ,CASE WHEN @Insert = 1 AND @Delete = 0 THEN 'INSERT'
				 WHEN @Insert = 1 AND @Delete = 1 THEN 'UPDATE'
				 WHEN @Insert = 0 AND @Delete = 1 THEN 'DELETE'
				 END AS Event
		   ,ORIGINAL_LOGIN() AS UserAccount
		   ,GETDATE() AS EventDate;

Kodu Düzenle ve Çalıştır