CODEWITHSUNDEEP
sqlsql-server
Jargo
Jargo

Reputation: 365

storing sql column value in another table

We are doing some database refactoring and are moving some data from one table to another. Previously we had an "offerrequest"-table that had address information(city ,streetaddress,postalcode etc.) stored directly in it. Now we are moving address data to separate "address"-table that contains the specific address fields an are referencing that table in the "offerrequest"-table. The problem is that some legacy applications still use the fields in the offerrequest-table to access data. Is there any way to make insert/update queries made against the offerrequest-table to save address data to the address table when the legacy addressfields are used in the query. SQL-server we use mssql-server 2008

Upvotes: 0

Views: 292

Answers (4)

A ツ
A ツ

Reputation: 1267

you could replace the offerrequest-table with a view. something like that:

   create view offerrequest 
    as 
    select new_offerrequest.data, adress.data 
    from 
             new_offerrequest 
    join 
             adress 
          on 
             new_offerrequest.adress_id=adress.id

Upvotes: 1

Fabian Bigler
Fabian Bigler

Reputation: 10895

You could use an after insert trigger to achieve that. This would look something like this:

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test] 
FOR INSERT
AS
    declare @empid int;
    declare @empname varchar(100);
    declare @empsal decimal(10,2);
    declare @audit_action varchar(100);

    select @empid=i.Emp_ID from inserted i; 
    select @empname=i.Emp_Name from inserted i; 
    select @empsal=i.Emp_Sal from inserted i;   
    set @audit_action='Inserted Record -- After Insert Trigger.';

    insert into Employee_Test_Audit
           (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
    values(@empid,@empname,@empsal,@audit_action,getdate());

    PRINT 'AFTER INSERT trigger fired.'
GO

SQL example extracted from Codeproject.

EDIT: Please use t-clausen.dk's answer since it also supports multiple rows.

Upvotes: -1

Dudi Konfino
Dudi Konfino

Reputation: 1136

do something like

create trigger replacetrg on offerrequest
**instead of** insert,update,delete
as ...---instead of insert/update/delete on offerrequest
       --do it on address.

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44326

If you want to use a trigger like described by @FabianBigler, here is the syntax:

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test] 
FOR INSERT
AS
INSERT Employee_Test_Audit
       (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) 
SELECT Emp_ID, Emp_Name,Emp_Sal,'Created by insert trigger',getdate()
FROM INSERTED

GO

Upvotes: 2

Related Questions