Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
GrouperGuy
Starting Member
2 Posts |
 Posted - 2011-08-21 : 15:53:50
|
| Hello all,I have an unusual need to exclude certain columns from an update. The business rule is as follows:If an admin user (as specified in a custom user table) has updated a particular column in a table, then a non-admin user is not allowed to update that column, but may update other columns. This is in a mostly disconnected environment, so the UI will not know for sure if a particular column has been updated by an admin user until that data is sync'd at a later time.I was thinking of using dynamic SQL in the update trigger to get a list of all the columns in the given table (there are a lot 100+) that have not been previously updated by an admin, then build dynamic sql to update only those columns. That sounds nasty, but I can't think of any other way to do it at the moment. And yes, I anticipate having to duplicate every column to include a "last modified by" or perhaps a separate table to only include those records and columns that were last updated by an admin (populated by the update trigger). I suppose the latter would be more efficient.Your suggestions would be most appreciated! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-21 : 21:37:12
|
| are you capturing the update details in a audit table/column to understand which of updates are performed by admin users?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
GrouperGuy
Starting Member
2 Posts |
Posted - 2011-08-21 : 22:16:49
|
| Yes, that is my plan. This is a new app & DB design. It’s basically just a large one-page form, mostly check boxes, dates and text input… very flat structure, so a single table makes sense to store it in. What I thought of doing was have the update trigger store the unique ID of form and the column name in a separate “admin_updated” table that would be inserted into whenever an admin made a change to a column.Then the data in that “admin_updated” table would allow me to conditionally build the dynamic SQL to perform the updates by non admin users, omitting any fields that had been previously updated by an admin. Odd business need, but it’s what they want.So I guess the pseudo-code for update trigger would be:1. Get a list of all columns in the table and put them in a temp table where they do not exist in the “admin_updated” for that record.2. Cursor through each column, building the dynamic SQL to perform the updates.3. Execute the dynamic SQL to do the single update.I suppose I could get the list of columns from the system tables that way I won’t have to change the trigger if the table ever changes.Your thoughts? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-21 : 23:43:52
|
| you can dispense with dynamic sql i think. you can put a conditional logic on the columns using CASE... WHEN and check if they had been part of admin_updated table and if yes retain their value or do the actual update.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Dynamic SQL in trigger to exclude some columns...