Maybe someone is having better luck with this then me --
How do I write out the syntax (and where) for a system table i'm creating. In the system table a user will pick from a dropdown in tr_reference_column a season linked to tr_season. Once that is selected and the save button is clicked I want the second column to populate with a fiscal year also from tr_season.
We can do this with constraints for created date, by, location etc. How do we do this with other fields? Is there syntax I can add/write in tr_reference_column for the fyear field? If there isn't what kind of constraint (and what syntax would i use) to build the table.
Ideally I'd like to avoid triggers - yes, we can do this, but the prference is to code it into set up somehow or into a constraint. Also we'd like to avoid giving the user the option to pick the fyear from the drop down because the fyear has to link to the season and user can pick it incorrectly.
Please advise. Thank you.
-Lisa
Hi Lisa
There's an interesting question underneath this one - Why do you want to duplicate the season fyear value into your local table?
If it's for some process/reporting task, you can always just link back to TR_SEASON and pick up the fyear value from there - in fact that's a more reliable way to do it, because it will then always be the current fyear value, if TR_SEASON ever gets corrected or otherwise changed.
Or do you just want it to be visible in your local table, for confirmation or usability reasons?
I'd be interested to know....
Ken
Hello Mr. McSwain, the reason for this is that - this data needs to be passed (displayed) and the user doesn't have access to tr_season so we need to display/provide this information. There are other columns as well and this field is just to show.
I was hoping to allieviate having the user enter the data because there is no error checking. We need to make sure a season matches a fiscal year.
I wanted to do either put something into tr_reference_column or add a constraint like we do on create_dt -- when i build a table i tend to add this line:
ALTER TABLE [dbo].[LTR_PERF_DATES] ADD CONSTRAINT [DF_LTR_PERF_DATES_create_dt_1__13] DEFAULT (getdate()) FOR [create_dt]
There has to be aline of code to add a constraint or a foreign key or something onto the table to say -- hey when season is selected, update fyear.
any and all ideas are appreicated.