Lab

Creating Audit table and Audit Trigger

You will copy and paste everything that is in green. Deliverable: Screenshots of query with results.

1) get current date and current user

select getDate();

select suser_sname();

2) Create an audit table

CREATE TABLE UpdateProductAudit (date_updated date, who varchar(50));

3) Create a trigger that inserts a row into the AuditProductChanges every time the Products table is updated

create trigger AuditProductChanges on Products

after update

as

begin

insert into dbo.UpdateProductAudit values(getDate(), suser_sname());

end

go

4) udate the Products table

UPDATE Products set quantity = quantity * 1.1;

5) view the update in the audit table

SELECT * from dbo.UdateProductAudit;

6) Do the exercises in the link below. Your professor will discuss the link in class.

https://www.mssqltips.com/sqlservertip/4055/create-a-simple-sql-server-trigger-to-build-an-audit-trail/

Submit the screenshots of the results. Create the table inside your own database. Alter each table name and trigger name to have your initials. Consequently , you also need to order each reference to the table to have your initials also.

7) Go to https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/create-a-server-audit-and-database-audit-specification?view=sql-server-2017

And create a server audit specification (either in SSMS or SQL)