December 19, 2008 at 6:32 pm
I am new to triggers and I thought I knew what I was doing until I started playing with multiple record updates/inserts. I have a trigger that calls a stored procedure to add records to another table. Everything works great if only one record gets inserted/updated but when there is multiples it doesnt. Can somebody help? My Trigger and Storeproc are bellow
ALTER TRIGGER Vehicle_A_IU
ON Vehicles
AFTER INSERT, UPDATE
AS
BEGIN
DECLARE
@VehID int,
@lRepLocID int,
Select @VehID = GlobalVehicleId, @lRepLocID = lRepLocationID from inserted
if @lRepLocID = 0
EXEC sp_CreateQueue 'Vehicles', 'GlobalVehicleId', @VehID
END
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE sp_CreateQueue
@sTableName nvarchar(50),
@sFieldName nvarchar(50),
@lReferenceID int
AS
INSERT INTO ReplicationQueue (sTableName, sFieldName, lReferenceID, lLocationID, dtCreated, bTag)
Select @sTableName, @sFieldName, @lReferenceID, lLocationID, GETDATE(), 0 from ReplicationLocations
December 19, 2008 at 8:15 pm
What do you mean "it doesn't work?" If you are getting an error, we cannot see it from here.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 19, 2008 at 10:10 pm
I should have been a little more clear. I meant it only works for the first record.
RBarryYoung (12/19/2008)
What do you mean "it doesn't work?" If you are getting an error, we cannot see it from here.
December 19, 2008 at 10:26 pm
You will have to open a cursor on the inserted table and execute the procedure once for each row returned by the cursor.
This looks like something that would be better handled by the stored procedure you are calling to insert the rows in the table. Putting complex application logic in a trigger usually turns out to be a very bad idea.
December 22, 2008 at 1:14 am
Hi,
I'd like to warn you that calling a stored procedure from trigger is one of the worst possible solutions. I should know it, the ERP system we are using does that (it even calls several stored procedures from one trigger), and performance suffers a lot. Besides, precisely because of that trigger, all actions on the respective table have to be done row-by-row - otherwise only the first row would be processed correctly, as you have realized.
Try to find some other solution if possible. This would only give you more headaches in the future.
December 22, 2008 at 8:03 am
Triggers have to handle multi-row changes, whereas stored procedures typically can only deal with a single row change at a time, so it can be clumsy (at best) to use them from triggers. I think that it would be best to rewrite you trigger to inline your stored procedures logic like this:
ALTER TRIGGER Vehicle_A_IU
ON Vehicles
AFTER INSERT, UPDATE
AS
BEGIN
INSERT INTO ReplicationQueue
(sTableName, sFieldName, lReferenceID, lLocationID, dtCreated, bTag)
Select 'Vehicles', 'GlobalVehicleId', I.GlobalVehicleId, L.lLocationID, GETDATE(), 0
From inserted I
Cross Join ReplicationLocations L
Where I.lRepLocationID = 0
END
this way is both better(correct) and easier.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply