0

I have a scenario. I have a property table having many fields including property size. so if some property have size 5 so in property table it will show 5 but If a user think that its size is not 5 but its 6. so i want to handle that that user save its size to 6 but not change original value that is 5. Also if that users get that property record it will show 6 size for that user but for all other users it will show 5 size.

Can any one help me how to handle this scenario in table structure or in displaying values user specific.? My database is in sql server 2012

1 Answer 1

1

You can do something along these lines, if the users are identifiable down at the database level:

You create you usual table plus a table containing user customizations:

create table dbo.T (
    ID varchar(19) not null primary key,
    PropertySize int not null
)
go
create table dbo.T_User (
    ID varchar(19) not null,
    [User] sysname not null,
    PropertySize int null
)

You then create a view through which all interactions should now occur, instead of the table:

create view dbo.V
with schemabinding
as
    select
        t.ID,
        ISNULL(u.PropertySize,t.PropertySize) as PropertySize
    from
        dbo.T t
            left join
        dbo.T_User u
            on
                t.ID = u.ID and
                u.[User] = SUSER_NAME()

And then a couple of triggers to make sure things are appropriately maintained:

create trigger T_V_I on dbo.V instead of insert
as
    insert into dbo.T (ID,PropertySize) select ID,PropertySize from inserted
go
create trigger T_V_U on dbo.V instead of update
as
    merge into dbo.T_User u
    using inserted s
    on u.ID = s.ID
    when matched then update set PropertySize= s.PropertySize
    when not matched then insert (ID,[User],PropertySize) values (s.ID,SUSER_NAME(),s.PropertySize);

So basically, each users customizations are stored as separate rows and the base table contains the "default" value that users will see unless they've applied a customization.

Depending on what authentication options are being used, SUSER_NAME may not be the correct function but one of the other various USER functions will be.

Sign up to request clarification or add additional context in comments.

1 Comment

Is this the only way or we can do it some other way instead of triggers?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.