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
For clarity - what I want to achieve is to set the field
saledate1 is not null.
If there are better ways to achieve this then a
trigger I am open for suggestions :)
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
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