February 9, 2009 at 6:55 am
Hi,
I am just starting to learn about Triggers and I'm beginning to write one that will be activated AFTER a row is INSERTED.
I have a table that contains a list of roles, each role can have a parent role so my table structure is this:-
dbo.roles
id = PK, int
name = varchar
parent_id = FK, int
the data i have so far is this:-
id = 1
name = role1
parent_id = null
id = 2
name = role2
parent_id = 1
id = 3
name = role3
parent_id = 2
as you can see role 1 has no parent, role 2 has role 1 as its parent, role 3 has role 2 and role 1 as its parents.
I have another table called roles_matrix which stores the role id and its parent id:-
role_id = 2
parent_id = 1
role_id = 3
parent_id = 2
I need a trigger to insert rows into the roles_matrix table whenever a row is inserted into the roles table, and if that particular role has a parent then we also need to insert details of that parent into the roles_matrix table.
So for example (using the data above) if I insert a new role into the roles table:-
id = 4
name = role 4
parent_id = 3
Then in the roles_matrix table I need the following rows inserted:-
role_id = 4
parent_id = 3
role_id = 4
parent_id = 2
role_id = 4
parent_id = 1
Does anyone have any advice as to how I can write a trigger to perform this? Would it be best if the trigger called a SP that would do the main bit of sql (i.e the search loop)?
Any help would be greatly appreciated,
Laura
February 9, 2009 at 7:10 am
You can use a recursive CTE to generate the data for your trigger. Would look something like this:
;with
Parents (ID, PID) as
(select ID, Parent_ID
from inserted
where Parent_ID is not null
union all
select Roles.ID, Roles.Parent_ID
from dbo.Roles
inner join Parents
on Roles.ID = Parents.PID)
insert into dbo.Roles_Matrix (Role_ID, Parent_ID)
select ID, PID
from Parents;
But I have to ask, why bother storing that in that way? It's likely to cause more problems than it solves, because it's going to be very complex to maintain and it's going to have a performance hit on your database.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 9, 2009 at 2:13 pm
[font="Verdana"]Hi Laura,
Can you explain why you need to do it with a trigger? Is this just a thing to try so you can learn how to write triggers?
If not, you would be better using a query as mentioned above.
[/font]
February 9, 2009 at 3:07 pm
Let me agree with Bruce. I'm not sure why you need the data in 2 places. You either store the parent in the roles table and walk the tree to get all the levels or store it in the roles_matrix table and walk the tree to get all the levels.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 10, 2009 at 5:47 am
Thank you for your replies.
There is a reason we are storing the details this way as this is just a small part of a much larger authorisation system we are writing and the way we have designed the system means we need to store the data this way so we DONT have to go through the whole parent tree for each role.
I will look into using a CTE, thanks for the help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply