locked
how to call a function from inside a trigger..with a cursor? RRS feed

  • Question

  • I'm looking for an example or some advice on how to execute a function inside of a trigger with a cursor. I'm thinking of using a cursor because I can't think of any other way to call the function, which returns an application-generated unique number, during a multi-row insert trigger. E.g. if I am performing 10 inserts, I want this trigger to call this function 10 time, retrieve the value it is generating (let's call it @value), and place that value in a table. I hope that makes sense. Thanks!

    • Changed type Naomi N Monday, June 11, 2012 2:13 AM Question rather than discussion
    Monday, June 11, 2012 2:11 AM

Answers

  • Yes, it's possible. 

    select *, row_number() over (order by PK) as Rn into #Temp from Inserted

    Then use #Temp table to write the cursor and do the needed work. Take a look at this blog post as how to write the efficient cursor's code:

    The Truth about Cursors - Part 1

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, June 11, 2012 2:27 AM

All replies

  • May be you can simply use a default value for the column? Alternatively, how about:

    select dbo.myFunction() as NewValue from Inserted

    ?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, June 11, 2012 2:14 AM
  • Unfortunately the application controls the schema, and we cannot alter the column definition. The goal here is to add records outside of the application. Here is the call to function statement

    EXEC [func] 2, 1, @id OUTPUT, @num_ids OUTPUT;

    Is it possible to wrap that call in a loop inside  trigger, where it loops n times, retrieves n different @id's, and writes each id to n unique rows, n being the number of inserted rows?

    Monday, June 11, 2012 2:24 AM
  • Yes, it's possible. 

    select *, row_number() over (order by PK) as Rn into #Temp from Inserted

    Then use #Temp table to write the cursor and do the needed work. Take a look at this blog post as how to write the efficient cursor's code:

    The Truth about Cursors - Part 1

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, June 11, 2012 2:27 AM
  • Hi Naomi, that link was a great resource! Very educating! Per the discussion examples, I tried

    ALTER TRIGGER   [dbo].[trg_Events_NON_GIS_Insert]
    ON  [dbo].[tbl_Events]
    
    
    INSTEAD OF INSERT 
    AS  
    BEGIN 
       SET NOCOUNT ON; 
       --declarations needed for function 
        DECLARE @id INT, 
    			@num_ids INT,
    	--declarations needed for cursor
    			@Location_ID uniqueidentifier,
    			@Event_Group_ID uniqueidentifier,
    			@Protocol_Name nvarchar(100),
    			@Start_Date datetime2(7),
    			@ObjectID int
        
        declare c cursor static
        for select Location_ID, Event_Group_ID, Protocol_Name, Start_Date, ObjectID
    		from inserted
    		order by ObjectID
        Open c
        while 1=1
        begin
        Fetch c into @Location_ID,@Event_Group_ID,@Protocol_Name,@Start_Date,@ObjectID 
        If @@FETCH_STATUS<>0 break
        EXEC [dbo].[i14_get_ids] 2, 1, @id OUTPUT, @num_ids OUTPUT;  
        INSERT dbo.tbl_Events
                             (Location_ID, Event_Group_ID, Protocol_Name, Start_Date, ObjectID) 
    		SELECT  
    			  Location_ID, Event_Group_ID, Protocol_Name, Start_Date, ObjectID = @id 
    		FROM inserted; 
    END 
    close c
    deallocate c
    end
    
    GO

    which works on single row, but not multi-row inserts. Not sure how I'd use a temp table here, or if I'm using ObjectID correctly. THe problem is, ObjectID is populated at insert time, but I need for the ObjectID to be replaced with the one published by the application, hence the call to the function.

    Monday, June 11, 2012 5:42 PM
  • I don't understand what are you doing and what do you want to insert into tbl_Events. Wy do you use ObjectID = @id instead of ObjectID = @ObjectID?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, June 11, 2012 8:52 PM
  • nice example.
    Tuesday, September 9, 2014 7:28 AM