Using Adventureworks Database Implement Test History Data Auditing Model Update Delete Ac Q30205817

. Using the AdventureWorks databaseimplement and test the History data auditing model for UPDATE andDELETE actions on any table

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

Description

Street address information for customers, employees, andvendors.

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 Addressrecords.
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 forthe state or province. Foreign key to StateProvince table.
PostalCode nvarchar(15) 15 60 no no Postal code for the streetaddress.
rowguid uniqueidentifier 36 16 (newid()) no no ROWGUIDCOL number uniquelyidentifying the record. Used to support a merge replicationsample.
ModifiedDate datetime 23 8 (getdate()) no no Date and time the record was lastupdated.

Indexes

name description column comment
AK_Address_rowguid nonclustered, unique located onPRIMARY rowguid Unique nonclustered index. Used tosupport replication samples.
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode nonclustered, unique located onPRIMARY AddressLine1, AddressLine2, City,StateProvinceID, PostalCode Nonclustered index.
IX_Address_StateProvinceID nonclustered located onPRIMARY StateProvinceID Nonclustered index.
PK_Address_AddressID clustered, unique, primary keylocated on PRIMARY AddressID Clustered index created by aprimary 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 referencingStateProvince.StateProvinceID.

Defaults

default name column comment
DF_Address_rowguid rowguid Default constraint value ofNEWID()
DF_Address_ModifiedDate ModifiedDate Default constraint value ofGETDATE()

Dependency graph

insert at least two records in theAddress table

update the first record

delete the first record

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *