June 10, 2012 at 7:40 pm
I am trying to get working a multirow insert trigger that calls a function. It works great on single-row inserts! The function is an application controlled bit of code, thus changing it or altering the behavior of the application is not an option, unfortunately. We're trying to perform multiple inserts OUTSIDE of the application, hence the trigger. Almost every similar multi-row insert trigger seems to deal with moving values from one table to another, I just can't wrap my head around how to populate the objectid column in this table with the function output. As a last-ditch effort, I've tried a cursor, no luck. As we are very limited in schema design options, the objectid column CANNOT be changed to an identity specification with auto increment. Perhaps there's a way to pass @id to objectid in a loop somewhere, or as a computed column? I'm not married to the trigger...
ALTER TRIGGER [dbo].[trg_Events_NON_GIS_Insert]
ON [dbo].[tbl_Events]
INSTEAD OF INSERT
AS
set nocount on
DECLARE @id INT, @num_ids INT, @location_id uniqueidentifier;
--call to function that generates next id
--IAW application rules
EXEC [dbo].[i14_get_ids] 2, 1, @id OUTPUT, @num_ids OUTPUT;
declare cur_event cursor read_only for
select location_ID from inserted
open cur_event
fetch next from cur_event into @location_id
WHILE @@FETCH_STATUS = 0
begin
INSERT dbo.tbl_Events
(
Location_ID, ObjectID
)
SELECT
--this is there the function output gets pushed into the ObjectID column
--works on single insert, not multiple!
i.Location_ID, ObjectID = @id
FROM inserted i
fetch next from cur_event into @location_id
end;
close cur_event
deallocate cur_event
go
June 10, 2012 at 9:48 pm
Do you need to execute i14_get_ids for every row that is inserted into tblEvents?
Or maybe a better question is: exactly what are you trying to achieve?
For example, I mocked up a simple test using your code as a framework and was able to successfully insert records into the table with the ID generated from i14_get_ids (which appears to be a stored procedure, not a function, correct?).
USE master
GO
CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE PROCEDURE i14_get_ids (@val1 INT, @val2 INT, @id INT OUTPUT, @num_ids INT OUTPUT)
AS
SET @id = 100
SET @num_ids = 1
GO
CREATE TABLE tbl_Events (location_id UNIQUEIDENTIFIER, objectID INT)
GO
CREATE TRIGGER [dbo].[trg_Events_NON_GIS_Insert]
ON [dbo].[tbl_Events]
INSTEAD OF INSERT
AS
SET NOCOUNT ON
DECLARE
@id INT,
@num_ids INT,
@location_id uniqueidentifier;
--call to function that generates next id
--IAW application rules
EXEC [dbo].[i14_get_ids] 2, 1, @id OUTPUT, @num_ids OUTPUT;
INSERT tbl_Events (location_id, objectID)
SELECT
location_id,
@id
FROM inserted
GO
INSERT tbl_Events
SELECT NEWID(), 1
UNION ALL
SELECT NEWID(), 2
GO
SELECT * FROM tbl_Events
GO
So this leads to believe there must be more to the problem...
June 11, 2012 at 12:22 pm
Shooting in the dark here - but this looks to be an "ID reservation system". As in, if you were to look at the parameters, you can reserve more than one ID at a time.
EXEC [dbo].[i14_get_ids] 2,
1, --I am guessing if you were to increase this number you would reserve more than one ID at a time.
@id OUTPUT, @num_ids OUTPUT;
Assuming that's correct you should be able to use a set-based solution:
ALTER TRIGGER [dbo].[trg_Events_NON_GIS_Insert]
ON [dbo].[tbl_Events]
INSTEAD OF INSERT
AS
set nocount on
DECLARE @id INT, @num_ids INT, @location_id uniqueidentifier;
declare @rows_from_Inserted int;
select @rows_from_Inserted=count(*) from inserted;
--call to function that generates next id
--IAW application rules
EXEC [dbo].[i14_get_ids] 2,
--1,
@rows_from_Inserted, --replace to reserve more than one.
@id OUTPUT, @num_ids OUTPUT;
--disable the cursor - no longer needed
--declare cur_event cursor read_only for
--select location_ID from inserted
--open cur_event
--fetch next from cur_event into @location_id
--WHILE @@FETCH_STATUS = 0
--begin
--INSERT dbo.tbl_Events
--(
--Location_ID, ObjectID
--)
--SELECT
----this is there the function output gets pushed into the ObjectID column
----works on single insert, not multiple!
--i.Location_ID, ObjectID = @id
--FROM inserted i
--fetch next from cur_event into @location_id
--end;
--close cur_event
--deallocate cur_event
; with InsertedCTE as (
Select Row_number() over (order by (select null)) RN,*
from inserted)
INSERT dbo.tbl_Events
(
Location_ID, ObjectID
)
select location_ID, @id+(RN-1) from insertedCTE
go
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 11, 2012 at 12:38 pm
What I am trying to accomplish is to call [dbo].[i14_get_ids] for each row insert, where the number of inserts could be 1 or n, and write the output of that call to function to the ObjectID field during the insert. ObjectID gets populated when the user enters a new record, however, it does not get populated with the ObjectID the application is looking for, hence the call to the SP (yes it's an SP not function, sorry). This is all related to a user requirement to add rows to the table outside of the application. I tried your code snippet, thanks for that, it does write two rows to the test table, but it calls @ID only only once, thus the same value gets written twice for the ObjectID field. We're looking to call @ID uniquely for each row insert.
June 11, 2012 at 1:03 pm
You should probably rewrite your stored procedure to return a range of ids, using a cursor in a trigger is not the best way to process data.
Here is a rewrite of your code, as I noticed another problem in the trigger, you didn't limit your insert to just the location_id in the cursor.
ALTER TRIGGER [dbo].[trg_Events_NON_GIS_Insert]
ON [dbo].[tbl_Events]
INSTEAD OF INSERT
AS
set nocount on
DECLARE @id INT,
@num_ids INT,
@location_id uniqueidentifier;
--call to function that generates next id
--IAW application rules
-- EXEC [dbo].[i14_get_ids] 2, 1, @id OUTPUT, @num_ids OUTPUT;
declare cur_event cursor read_only for
select location_ID from inserted
open cur_event
fetch next from cur_event into @location_id
WHILE @@FETCH_STATUS = 0
begin
EXEC [dbo].[i14_get_ids] 2, 1, @id OUTPUT, @num_ids OUTPUT;
INSERT dbo.tbl_Events
(
Location_ID,
ObjectID
)
SELECT
--this is there the function output gets pushed into the ObjectID column
--works on single insert, not multiple!
i.Location_ID,
ObjectID = @id
FROM
inserted i
WHERE
i.Location_ID = @location_id
fetch next from cur_event into @location_id
end;
close cur_event
deallocate cur_event
go
June 11, 2012 at 1:20 pm
THat did the trick! THanks!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply