1

I have a table with 6 columns program_id, a, b, c, d and e (there are actually other columns as well, but for the moment/question this is all that is needed). There is also a reference table with the following fields. id, program_id, a, b, c, d and e.

Table 1:

program_id, a, b, c, d, e
1,           ,  ,  ,  ,  
2,           ,  ,  ,  ,
1,           ,  ,  ,  ,
3,           ,  ,  ,  ,

Table 2 (ref):

id, program_id, a,     b,     c,     d,    e
1,  1,          NULL,  Y,     NULL,  Y,    NULL
2,  2,          Y,     NULL,  Y,     NULL, NULL
3,  4,          Y,     NULL,  Y,     NULL, NULL
4,  5,          NULL,  Y,     Y,     NULL, Y

In my reference table the a-e columns are on/off or yes/no.

What I need to happen is that table 1 will only allow updates to fields that have a NULL value for the corresponding program_id. So in the case of program_id 1 lets say. column a, c and e will allow updates, column b and d will not.

in the case of program_id 2 columns and and c will not allow any updates and column b, d and e will allow them.

I have a sql table with a trigger - I was thinking of building that logic into an insert trigger (although I'm not sure if that is the best place).

The plan is to run this code to enter the 50 or so programs into table 1 yearly. (there is also a year field to distinguish the data further). Once the data is inserted what is the best way to say can't update this data instead pull the values already calculated from a third (different table ... table 3).

This data will be displayed via infoamker/sybase into a third party software. But the big challenge is to get it into the code first then once its there how do i not allow the user to update those fields in infomaker and only update the NULL ones.

is this possible? (and how do i do it).

Thanks.

Daniel A. Thompson
  • 1,904
  • 1
  • 17
  • 26
Elizabeth
  • 719
  • 1
  • 14
  • 27

1 Answers1

1

Here is a possible solution using a table constraint, I did it in SQL Server, but Sybase is similar

I thought that the reference table should look like this:

ProgramId   ColName ColLock
1           Col_A   0
2           Col_A   1
1           Col_B   0
2           Col_B   0

ColLock is a bit/Boolean

Access the reference table with a function:

CREATE FUNCTION [dbo].[CheckColumn] 
(
    @ProgramId int, @ColName varchar(10)
)
RETURNS bit
AS
BEGIN
    DECLARE @LockCol bit
    SET @LockCol= (SELECT ColLock FROM Program WHERE ProgramId = @ProgramId AND  ColName = @Colname)
    RETURN @LockCol
END

Add a Table Constraint for each column

ALTER TABLE [dbo].[Test_Table]  WITH CHECK 
   ADD  CONSTRAINT [CK_Test_Table] CHECK  (([dbo].[CheckColumn]([ProgramId],'Col_A')=(0)))

You can see that I changed the reference table to better lookup using the column name. I had occurred to me that the constraint violation message is fairly ugly and that it would be nicer to call the function from the client interface before submitting the update. Hope this helps, good luck with your solution.

MikeAinOz
  • 126
  • 1
  • 10
  • 24
  • Hi thank you for your help, I'm stumped and trying to take apart your solution to apply it to my project - I'm sorry to sound so dense -- in your begin block the select statement -- what is select `ColLock` From Program. Do I have to have a select statement for each column (a-e)? Or What exactly is ColLock in that case? Thank you again! – Elizabeth Jul 11 '16 at 18:30
  • Hi That's OK, my fault, I just refactored the Program table to have a single Boolean value returned as in ColLock ("Column Locked"). Define a row for each Program/Column pair in the program table with the appropriate ColLock value. Then do an "ADD CONSTRAINT" to the table for each column. As I mentioned before it would be nice if you could also call the function from the front end as well to lock the field in the UI. – MikeAinOz Jul 11 '16 at 22:49
  • I hoped that answered your question, the user defined "CheckColumn" function is the key to all of this, you might want to call it something more distinctive like "udfCheckColumn". I've written every thing around it, remember the function only returns one value. – MikeAinOz Jul 11 '16 at 22:54
  • Hello, I totally get it and I got it to work, my only issue is that I can't really restructure my reference column - the powers that be, want it somewhat user friendly so that they can have front end users modify those as needed. With that said, any suggestions about adjusting the functions to pass a column name -- so it looks at column A and returns Locked/or Not ... if not, I guess then i would have to build out 5 functions. Thanks again for the advice. – Elizabeth Jul 19 '16 at 14:42
  • Elizabeth, maybe you could use UNPIVOT to create a view. Check put this question, which looks a bit like your table http://stackoverflow.com/questions/19055902/unpivot-with-column-name . Happy SQLing! – MikeAinOz Jul 24 '16 at 01:03