Changing processing order of update statement

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Changing processing order of update statement

Author

Topic

scullee
Posting Yak Master

103 Posts

Posted - 2004-09-07 : 20:15:14

I have a crazy update statement that works on parent child relationships. I am implementing a NT Like permissions structure (with inheritance) and have it all working fairly well except for 1 problem.

One of my stored procs calculates a list of permissions based on a specific user. To get it to work without cursors i put a list of all the records for the user into a temp table, then i add the appropriate permissions for each user. My problem is that because of the update to add the permissions can update multiple permissions at the same time, my permissions from higher up the tree are being overwritten by permissions further down the tree when it finds multiple matches.

Is there a way to make an update statement process in a certain order so that the permissions that are highest on the tree are processed last and will then overwrite the ones lower on the tree?

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-09-08 : 01:28:05

Not quite sure I understand.. but is this case of an update statement refering to the records updated with-in the same statement. If so, it is not possible. You will have to have multiple update statements to each structure.

can you post a sample model and the update statement.

Hemanth Gorijala
BI Architect / DBA

scullee
Posting Yak Master

103 Posts

Posted - 2004-09-08 : 02:05:27

This is only a small part of the application but i will summarise

We have 2 tables
Resource - This holds the list of resources in a parent child relationship. I have used the model from here http://www.sqlteam.com/item.asp?ItemID=8866 with the lineage field to track the parents and children.
ResourcePermissions - This holds the permissions for the resources (and users and usergroups). A permission can be marked as inheritable where it is passed down the tree to child records. A permission down the tree always overwrites a permission from further up the tree.

Eg

1
|2
||3
|4
|5
||6
||7
||8

So resource 3 has a lineage 1/2/ and 8 has lineage 1/5/

If a permission is defined on 1 it will bubble down to all children. If there is then another permission on 5, everything below 5 has this permission instead. I have written a stored proc to calculate this and what it does is get the list of all the resources in the tree, then it goes and tries tp add the permissions so anything that has 1 in the lineage gets 1's permission ... and everything that has 5 gets 5's permissions etc. The problem occurs that the records are being updated in the wrong order and so that lets say for records 6,7,8 the permissions from 5 is being added to 6 and 8 but the permissions for 1 is being put onto 7. My next option is to just pull the sql and add a cursor but i am worried about performance so i was hoping to go with the set based version first.

The tables are here

create table Resource
(
resourceId int not null identity(1,1),
parentId int not null,
name varchar(80) not null,
description varchar(200) not null,
resourceTypeId varchar(20) not null,
properties varchar(1000) null,
lastUpdatedBy varchar(20) not null,
lastUpdatedDateTime datetime not null default getutcdate(),
isDeleted bit not null default 0,
Lineage varchar(1000) null,
depth int null,
isLocked bit not null default 0,
constraint pk_Resource primary key clustered (resourceId)
);

create table ResourcePermissions
(
resourceId int not null,
userName varchar(20) default 'undefined',
loginGroupId int not null default -1,
canAdd bit not null default 0,
canOpen bit not null default 0,
canEdit bit not null default 0,
canDelete bit not null default 0,
canInherit bit not null default 0,
canChangePermissions bit not null default 0,
lastUpdatedBy varchar(20) not null,
lastUpdatedDateTime datetime not null default getutcdate(),
constraint pk_ResourcePermissions primary key clustered (resourceId, userName, loginGroupId)
);

and the sql i am using in the stored proc is

--Temp table to hold all of the data selected
create table #ResourcesAccessible
(
ResourceId int not null primary key nonclustered,
lineage varchar(900),
PermissionType int,
canAdd bit not null default 0,
canOpen bit not null default 0,
canEdit bit not null default 0,
canDelete bit not null default 0,
canInherit bit not null default 0,
canChangePermissions bit not null default 0
)

--Get the list of resources
select @baseResourceLineage = lineage
from resource where resourceId = @homeResourceId

--Insert the parent resource
insert into #ResourcesAccessible (ResourceId, lineage)
values (@homeResourceId, @baseResourceLineage)

select @baseResourceLineage = '/' + right('00000' + cast(@homeResourceId as varchar),5) + '/%'

--Insert the child resources
insert into #ResourcesAccessible (resource.ResourceId, resource.lineage)
select ResourceId, resource.lineage
from resource
where resource.lineage like @baseResourceLineage and isDeleted = 0

-- THis is the query that is causing the problems

update #ResourcesAccessible
set #ResourcesAccessible.canAdd = ResourcePermissions.canAdd,
#ResourcesAccessible.canOpen = ResourcePermissions.canOpen,
#ResourcesAccessible.canEdit = ResourcePermissions.canEdit,
#ResourcesAccessible.canDelete = ResourcePermissions.canDelete,
#ResourcesAccessible.canInherit = ResourcePermissions.canInherit,
#ResourcesAccessible.canChangePermissions = ResourcePermissions.canChangePermissions
from #ResourcesAccessible
inner join
(select Resource.ResourceId, Resource.lineage,
canAdd, canOpen, canEdit, canDelete, canInherit, canChangePermissions
from ResourcePermissions
inner join Resource on ResourcePermissions.resourceId = resource.resourceId
inner join LoginGroupMember on LoginGroupMember.loginGroupId = ResourcePermissions.loginGroupId
where LoginGroupMember.username = @username)
ResourcePermissions on
#ResourcesAccessible.lineage like ResourcePermissions.lineage + right('0000' + cast(ResourcePermissions.ResourceId as varchar),5) + '/%' and
ResourcePermissions.canInherit = 1

I know the list of records that is being returned is correct and that all the joins are working because if i change it from an update to a insert i get the full set i want. Its just that when the same permission is returned multiple times for the same resource it updates them randomly

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-08 : 10:55:08

Rows are updated in physical order (I don't know if that is documtented, it just appears to be the case) so if you enforce some order on your temp table with an identity or clustered index the updates will occur in that order as long as you don't have a filter in your where clause that causes a different index to be used when scanning the table.

I know that does not answer the question but I don't know the model well enough to tell you how to enforce that ordering.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

scullee
Posting Yak Master

103 Posts

Posted - 2004-09-09 : 02:50:51

I added the clustered index to the lineage which enforces the order but it didnt seem to help

I think i am going to have to try a different approach.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources