SQL Server 2000 Exercise 3: Triggers And Views

SQL Server 2000

Exercise 3: Triggers And Views

-----Week 6-----

Please follow those steps to practise:

1. Create 3 triggers to audit the changes to the Orders table.

Tips:

• Create an audit table “aud_Orders” with the same colums as

in the Orders table and 2 more colums AuditType(with

values either ‘I’,’U’,’D’) and DateTimeStamp(which will

record the date time stamp of changes)

• Create Update Triggers (Similar to Insert,Delete) : when a

record in the Orders table is updated the trigger will move

the old record to the audit table(record the date time and

mark it as ‘U’)

2. Create a view that shows all the orders with the following colums:

OrderID,CustomerName,ProductName,DateProcessed,Status

Business rules:

If CustomerName is a null value “New Customer” is returned

If DateProcessed is later than current date return “Pending”, if

DateProcessed is ealier return “History” in Status colum.

Tips:

• Using Case When ...Then statement in the view

• Using Getdate() function to get the current date time