Street address information for customers, employees, and vendors. Table properti
ID: 3912649 • Letter: S
Question
Street address information for customers, employees, and vendors.
Table properties
Columns
Indexes
References
Referenced by
Create a trigger in SQL
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
insert at least two records in the Address table
update the first record
delete the first record
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 bytesExplanation / Answer
Assumptions : We are working in MS SQL Server.
History table name - [Person].[AddressHistory]
History table has all columns same as [Address] table with SystemUser & CreateDate as additional columns.
Change History table name and columns in the script, If it is different from my assumptions.
Explanation :
A trigger is a special type of stored procedure that automatically executes when an event occurs in the database server.
A trigger executes when a user tries to INSERT, UPDATE, or DELETE statements on a table or view.
Here we are creating a trigger which will execute after any UPDATE or DELETE on Address table.
Server internally use "deleted" table to hold old record of the table,
it automatically structures the "deleted" table similar to the table on which the trigger is defined.
Script :
CREATE TRIGGER [Person].[AddressTrigger]
ON [Person].[Address]
AFTER UPDATE, DELETE
AS
Insert Into [Person].[AddressHistory] ( AddressID,
AddressLine1,
AddressLine2,
City,
StateProvinceID,
PostalCode,
rowguid,
ModifiedDate,
SystemUser,
CreateDate)
Select AddressID,
AddressLine1,
AddressLine2,
City,
StateProvinceID,
PostalCode,
rowguid,
ModifiedDate,
SYSTEM_USER,
GETDATE()
From deleted
Post Script: You have to Insert 2 records in Address table, Update one and delete the other one.
Then check "AddressHistory" table, it should have 2 records same as you Inserted at first(i.e. Data before update).