. Using the AdventureWorks database implement and test the History data auditing model for UPDATE and DELETE actions on any table

Create a trigger the trigger should select column values from deleted add system_user and date and insert values into history table. It does not matter if the table was updated or record was deleted, in both cases old data will be kept in deleted

Description

Street address information for customers, employees, and vendors.

Table properties

name	value
name	[Person].[Address]
created	Mar 19 2009 9:08PM
modified	Mar 19 2009 9:08PM
ansi nulls	on
quoted identifier	on
row count	19,614
Size of data	2,240 kb
Size of indexes	2,504 kb
Maximum size of a single row	692 bytes

Columns

column	datatype	length	bytes	default	nulls	PK	FK	UQ	computed	comment
AddressID	int identity(1,1)	10	4		no	yes			no	Primary key for Address records.
AddressLine1	nvarchar(60)	60	240		no				no	First street address line.
AddressLine2	nvarchar(60)	60	240		yes				no	Second street address line.
City	nvarchar(30)	30	120		no				no	Name of the city.
StateProvinceID	int	10	4		no		StateProvince.StateProvinceID		no	Unique identification number for the state or province. Foreign key to StateProvince table.
PostalCode	nvarchar(15)	15	60		no				no	Postal code for the street address.
rowguid	uniqueidentifier	36	16	(newid())	no				no	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime	23	8	(getdate())	no				no	Date and time the record was last updated.

Indexes

name	description	column	comment
AK_Address_rowguid	nonclustered, unique located on PRIMARY	rowguid	Unique nonclustered index. Used to support replication samples.
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	nonclustered, unique located on PRIMARY	AddressLine1, AddressLine2, City, StateProvinceID, PostalCode	Nonclustered index.
IX_Address_StateProvinceID	nonclustered located on PRIMARY	StateProvinceID	Nonclustered index.
PK_Address_AddressID	clustered, unique, primary key located on PRIMARY	AddressID	Clustered index created by a primary key constraint.

References

name

StateProvince

Referenced by

name

EmployeeAddress

VendorAddress

CustomerAddress

SalesOrderHeader

SalesOrderHeader

Foreign key graph

Foreign keys

name	columns	foreign columns	type	comment
FK_Address_StateProvince_StateProvinceID	StateProvinceID	StateProvince.StateProvinceID		Foreign key constraint referencing StateProvince.StateProvinceID.

Defaults

default name	column	comment
DF_Address_rowguid	rowguid	Default constraint value of NEWID()
DF_Address_ModifiedDate	ModifiedDate	Default constraint value of GETDATE()

Dependency graph

insert at least two records in the Address table

update the first record

delete the first record