SQL Server – Setting Field Default Value from Another Field

sql serversql-server-2000trigger

I am trying to create a trigger using the below syntax, however SSMS keeps barking at me with the below error.

Msg 208, Level 16, State 4, Procedure test1, Line 1
Invalid object name 'data123'.

And here is my syntax

CREATE TRIGGER test1
ON data123
AFTER UPDATE
AS
begin
  UPDATE data123
  SET saledate = saledate1
  FROM data123
  INNER JOIN INSERTED i ON data123.id = i.id 
  AND i.saledate1 is not null;
end
go

Edit

For clarity – what I want to achieve is to set the field saledate = saledate1 where saledate1 is not null.

If there are better ways to achieve this then a trigger I am open for suggestions 🙂

EDIT #2
I also tried this syntax

ALTER TABLE data123 ADD CONSTRAINT cst123 DEFAULT saledate1 FOR saledate;

But I get an error of

Msg 128, Level 15, State 1, Line 1
The name 'saledate1' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

saledate is a field that is used for multiple SSRS etc and when the database was created etc for data123 the field was improperly named to saledate1. Not viable to try to rename it now as multiple elements are already tied to it. So in order for data123 to be included in SSRS I need the field saledate to hold the value from saledate1

EDIT #3
Here is sample DDL to hopefully clarify what my problem is and my desired output. 

Create Table test1
(
  uqID int IDENTITY(1,1) PRIMARY KEY,
  itemsold varchar(400),
  saledate1 date,
  saledate date
)
Insert Into test1 (itemsold, saledate1) Values
('tree', '03/01/2016')
,('flower', '02/11/2016')
,('chair', '02/25/2016')

Select * from test1

So if you run the above syntax this is your output

uqID    itemsold    saledate1   saledate
 1      tree        3/1/2016    
 2      flower      2/11/2016   
 3      chair       2/25/2016

My desired output (which is why I originally thought a trigger) is this

uqID    itemsold    saledate1   saledate
1       tree        3/1/2016    3/1/2016
2       flower      2/11/2016   2/11/2016
3       chair       2/25/2016   2/25/2016

Best Answer

For a one-off update to make saledate the same as saledate1:

UPDATE dbo.test1
SET saledate = saledate1;

You could also do this with a computed column. This would ensure saledate always matches saledate1 (even when the data changes):

-- Drop the existing column
ALTER TABLE dbo.test1
DROP COLUMN saledate;

-- Add it back as a computed column
ALTER TABLE dbo.test1
ADD saledate AS saledate1;

A third option would be to drop saledate, then rename the saledate1 column:

-- Drop the existing column
ALTER TABLE dbo.test1
DROP COLUMN saledate;

-- Rename saledate1 to saledate
EXECUTE sys.sp_rename 
    @objname = N'dbo.test1.saledate1',
    @newname = N'saledate',
    @objtype = 'COLUMN';

This last approach is more complicated there are dependencies on the column. You would need to remove or update those before performing the rename.

Related Question