Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Updating Addresses

    Hi all I have two tables one is update table and the other is the People_tbl what I am trying to do is make it so that when a person changes addresses that they will have history of it and I dont know if I'm doing this correctly'
    Here is what I have so far

    Code:
    CREATE PROCEDURE [UpdateAddress]
    
    INSERT INTO People_tbl
                          ([Address], City, [State], Zip)
    SELECT     [Address], City, [State], Zip
    FROM         PeopleUpdate_tbl
    where [Update] = 'Yes'
    RETURN 
    GO


    PeopleUpdate_tbl

    Code:
    CREATE TABLE [dbo].[PeopleUpdate_tbl](
    	[Parent ID] [nvarchar](50) NOT NULL,
    	[Telephone #] [nvarchar](50) NULL,
    	[Message #] [nvarchar](50) NULL,
    	[Address] [nvarchar](50) NULL,
    	[City] [nvarchar](50) NULL,
    	[State] [nvarchar](50) NULL,
    	[Zip] [nvarchar](50) NULL,
    	[E Mail Address] [nvarchar](50) NULL,
    	[DateOfUpdate] [datetime] NULL,
    	[UpdateID] [int] IDENTITY(1,1) NOT NULL,
    	[Update] [nvarchar](50) NULL,
     CONSTRAINT [PK_PeopleUpdate_tbl] PRIMARY KEY CLUSTERED 
    (
    	[UpdateID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO



    People_tbl

    Code:
    CREATE TABLE [dbo].[People_tbl](
    	[Parent ID] [nvarchar](50) NOT NULL,
    	[Family ID] [nvarchar](50) NULL,
    	[StudtID] [nvarchar](50) NULL,
    	[Date of Referral] [nvarchar](50) NULL,
    	[FirstName] [nvarchar](50) NULL,
    	[LastName] [nvarchar](50) NULL,
    	[SS#] [nvarchar](50) NULL,
    	[DOB] [datetime] NULL,
    	[Sex] [nvarchar](50) NULL,
    	[Telephone #] [nvarchar](50) NULL,
    	[Message #] [nvarchar](50) NULL,
    	[Address] [nvarchar](50) NULL,
    	[City] [nvarchar](50) NULL,
    	[State] [nvarchar](50) NULL,
    	[Zip] [nvarchar](50) NULL,
    	[E Mail Address] [nvarchar](50) NULL CONSTRAINT [DF_People_tbl_E Mail Address]  DEFAULT (N'Email Address'),
    	[Tribal Affiliation] [nvarchar](50) NULL,
    	[Event ID] [nvarchar](50) NULL,
    	[TANF staff making Referral] [nvarchar](50) NULL,
    	[ReferralLocation] [nvarchar](50) NULL,
    	[RegistrationDate] [datetime] NULL,
    	[Type Participant] [nvarchar](50) NULL,
    	[ScannedDocuments] [ntext] NULL CONSTRAINT [DF_People_tbl_ScannedDocuments]  DEFAULT (N'ScannedDocuments'),
    	[EnrollmentStatus] [nvarchar](50) NULL,
    	[Grade] [nvarchar](50) NULL,
    	[Age] [int] NULL,
    	[WPH] [nvarchar](50) NULL,
    	[ReferralStatus] [nvarchar](50) NULL,
    	[ServiceArea] [nvarchar](50) NULL,
    	[Month] [nvarchar](50) NULL,
    	[Week] [nvarchar](50) NULL,
    	[UpdateID] [int] NULL,
     CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED 
    (
    	[Parent ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This will create an archive table and triggers for saving all changes to a table. You could comment out the columns you don't want, if you really only need to archive address changes.
    sqlblindman private pastebin - collaborative debugging tool
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •