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)
AS
BEGIN

WITH RecursiveEntry (ParentId,EntryId,ParentType)
AS
(
-- Anchor member definition
SELECT ParentId,EntryId,ParentType from ParentEntryRelation
where ParentEntryRelation.EntryId = @entryId
UNION ALL
-- Recursive member definition
SELECT ParentEntryRelation.ParentId,
ParentEntryRelation.EntryId,
ParentEntryRelation.ParentType
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
OPTION (MAXRECURSION 32767)

RETURN
END

Answers


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.