0

I have a column of order_id in billing table once this order_id exists (means not NULL) , then it must never be updateable again to NULL or to any other value.

I have a huge bug that somehow NULLing this value for some records.

1.I am doing QA for 1 month now and cannot find what sp is updating the field to NULL. I covered all the UI and business logic and found nothing. is there any better way to find what updating certain field to null?

2.Is there an SQL way like constraint that puts a guard on field once it has value and will throw an error if anything will try to update it.

Alexxx
  • 299
  • 1
  • 2
  • 9
  • I think you need a trigger to enforce this. – Gordon Linoff Feb 23 '17 at 20:47
  • Why is the field nullable then? – Giorgos Altanis Feb 23 '17 at 20:49
  • the website is huge, i dont even know where to put this trigger, i cannot find what command updates the values to null, if i knew i would solve it . how can i find where to trigger ? – Alexxx Feb 23 '17 at 20:49
  • the field is null at default. once it linked to bill then order must always be linked to bill. what happends is that it null at default, than when its attached it gets value instead of null, BUT than something unknown happends (after some days usually) and its updated to null again – Alexxx Feb 23 '17 at 20:51
  • Bad design, in my opinion – Giorgos Altanis Feb 23 '17 at 21:01
  • Aren't you trying to fix the wrong issue? Yes the field should not allow null values (should be identity column?), but there will still be weird behavior even if you restrain that particular field. – VDWWD Feb 23 '17 at 21:02
  • its null by default , so how it can not allow null values? – Alexxx Feb 23 '17 at 21:11

2 Answers2

0

Assuming SQL Server, can you try set up a Profiler trace? See this link for example if you don't know how to do it:

Community
  • 1
  • 1
Giorgos Altanis
  • 2,742
  • 1
  • 13
  • 14
  • I dont know when will be the next time that this update will hit, it can be days, the table being updated all the time like milions of updates every week. i need to protect only one field(column) from being updatable after it has a value – Alexxx Feb 23 '17 at 21:05
  • where? how? maybe i am missing something that i dont understand, to set up a trigger i need to know where in code the update operation is done, right? and i dont know what being triggered, i dont know what sp updates the field, there are more than 1000 sp in project – Alexxx Feb 23 '17 at 21:07
  • No! See this for example: https://msdn.microsoft.com/en-us/library/ms189799.aspx A table trigger is something like an event: when an update occurs, the code executes automatically. I am not sure that you will be able to find the problematic procedure, but at least you can safeguard your data. – Giorgos Altanis Feb 23 '17 at 21:09
  • ok i will go with trigger , and what you recommend to do once field being updated? to log the sp name? can you share recommended way to trigger for this issue, i mean how to set the trigger to act and what to do when triggered. I am using sql 2008 r2 – Alexxx Feb 23 '17 at 21:18
  • I am not sure if you can do this, sorry. (And I am not very proficient with triggers either.) I think the best you can do is "lock" your field from being updated, I am trying to come up with an example. – Giorgos Altanis Feb 23 '17 at 21:21
0
create table test2 (id int identity primary key, name varchar(20) not null, somevalue varchar(20))
GO

create trigger test2_upd_trigger 
on test2
after update as 
begin
    update test2 set somevalue = coalesce(d.somevalue, i.somevalue) 
    from inserted i join deleted d on i.id = d.id
    join test2 on test2.id = i.id
end
GO

insert test2 (name) values ('Demo1')
update test2 set somevalue = 'A value' where id = 1 -- This is allowed
select * from test2 -- [somevalue] has been assigned a value

update test2 set somevalue = null where id = 1 -- This is "rejected" by the trigger
select * from test2 -- [somevalue] retains its value
Giorgos Altanis
  • 2,742
  • 1
  • 13
  • 14
  • I think that this code will effectively "lock" the field [somevalue] once it has a value: it only allows an update only if its current value is NULL. But I am not sure about its performance if your table is a large one. – Giorgos Altanis Feb 23 '17 at 21:37
  • between 1723 SP there must be only 1 or 2 that updates this filed. is there a way you suggest to search this sp? like some query that searches for any operation over certain table and certain field – Alexxx Feb 23 '17 at 22:24
  • Check for table dependencies (Sql Server Management Studio -> Right click on your table -> View dependencies), this will list the stored procedures (among other dependencies) which depend on this table, so if they are not too many you might be lucky. You could also try something like this: select * from information_schema.routines where routine_definition like '%update%' and routine_definition like '%table_name%' and routine_definition like '%field_name%' but this is not guaranteed to work. And perhaps try the trigger as well, after all I am curious to see how it goes :-) – Giorgos Altanis Feb 23 '17 at 22:32