(Solved) : 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
namevaluename[Person].[Address]createdMar 19 2009 9:08PMmodifiedMar 19 2009 9:08PMansi nullsonquoted identifieronrow count19,614Size of data2,240 kbSize of indexes2,504 kbMaximum size of a single row692 bytes
Columns
columndatatypelengthbytesdefaultnullsPKFKUQcomputedcommentAddressIDint identity(1,1)104noyesnoPrimary key for Addressrecords.AddressLine1nvarchar(60)60240nonoFirst street address line.AddressLine2nvarchar(60)60240yesnoSecond street address line.Citynvarchar(30)30120nonoName of the city.StateProvinceIDint104noStateProvince.StateProvinceIDnoUnique identification number forthe state or province. Foreign key to StateProvince table.PostalCodenvarchar(15)1560nonoPostal code for the streetaddress.rowguiduniqueidentifier3616(newid())nonoROWGUIDCOL number uniquelyidentifying the record. Used to support a merge replicationsample.ModifiedDatedatetime238(getdate())nonoDate and time the record was lastupdated.
Indexes
namedescriptioncolumncommentAK_Address_rowguidnonclustered, unique located onPRIMARYrowguidUnique nonclustered index. Used tosupport replication samples.IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCodenonclustered, unique located onPRIMARYAddressLine1, AddressLine2, City,StateProvinceID, PostalCodeNonclustered index.IX_Address_StateProvinceIDnonclustered located onPRIMARYStateProvinceIDNonclustered index.PK_Address_AddressIDclustered, unique, primary keylocated on PRIMARYAddressIDClustered index created by aprimary key constraint.
References
nameStateProvince
Referenced by
nameEmployeeAddressVendorAddressCustomerAddressSalesOrderHeaderSalesOrderHeader
Foreign key graph
Foreign keys
namecolumnsforeign columnstypecommentFK_Address_StateProvince_StateProvinceIDStateProvinceIDStateProvince.StateProvinceIDForeign key constraint referencingStateProvince.StateProvinceID.
Defaults
default namecolumncommentDF_Address_rowguidrowguidDefault constraint value ofNEWID()DF_Address_ModifiedDateModifiedDateDefault constraint value ofGETDATE()
Dependency graph
insert at least two records in theAddress table
update the first record
delete the first record
Expert Answer
Answer to Using Adventureworks Database Implement Test History Data Auditing Model Update Delete Ac Q30205817 . . .
OR