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

All replies

• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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

  • Proposed as answer by Manoj Pandey (manub22)Microsoft employee Monday, June 11, 2012 5:27 AM
  • Marked as answer by Kalman Toth Sunday, June 17, 2012 7:12 PM

  Monday, June 11, 2012 2:27 AM
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  nice example.
  

  Tuesday, September 9, 2014 7:28 AM