How to create a trigger that uses the results from a UDF to insert into another table

I'm using SQL Server R2.

Two tables:

UserEntryAccess (EntryId, UserId)
ParentEntryRelation(Id, ParentType, ParentId, EntryId)

When a record is inserted into ParentEntryRelation, a trigger needs to get the list of user ids that currently have access by walking up the ParentEntryRelation table, and insert the EntryId and UserId into UserEntryAccess. I have a function with a recursive common table expression that retrieves these user ids. Works great, yippee.

The question: What single sql query can I use to call the function for every record in the INSERTED table, and insert THOSE results with the EntryId into UserEntryAccess in a single sql statement.

Here is the recursive CTE:

CREATE FUNCTION [dbo].[GetUserAccessIds](@entryId as uniqueidentifier)
RETURNS @tempUserids table (userId uniqueidentifier)

WITH RecursiveEntry (ParentId,EntryId,ParentType)
-- Anchor member definition
SELECT ParentId,EntryId,ParentType from ParentEntryRelation
where ParentEntryRelation.EntryId = @entryId
-- Recursive member definition
SELECT ParentEntryRelation.ParentId,
from ParentEntryRelation
inner join RecursiveEntry on RecursiveEntry.ParentId = ParentEntryRelation.EntryId

insert into @tempUserids
SELECT distinct ParentId
from RecursiveEntry
where ((ParentType & 32) = 32) --32 is the ServerUser type



Sounds like you'd want to use a CROSS APPLY.

insert into UserEntryAccess 
    (EntryId, UserId)
    select i.EntryId, f.userId
        from inserted i
            cross apply [dbo].[GetUserAccessIds](i.EntryId) f

Need Your Help

AES encrpted database entry size

android ios database encryption aes

Hello guys I am gonna use 256bit AES encrypted data on a mobile database.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.