Update Trigger problem, please help!
-
Hi!
I have created an after update trigger on one of my tables (OrderLine). When a new record is inserted in this table, I would like to pass the unique filed OrderLineId as an input parameter to a stored procedure I have created (KVtest). I guess I'm missing something because nothing happens, that is I dont get any value for the OrderLineID into the stored procedure.
Help appreciated..here is my trigger code and also the stored procedure code:
CREATE TRIGGER [AfterInsertOrderLine] ON [dbo].[OrderLine]
AFTER INSERT
AS
DECLARE @OrderLineID as nvarchar(20)
select @OrderLineID = OrderLineID from inserted
exec KVtest @OrderLineID
the stored procedure :
CREATE PROCEDURE [KVtest]
@OrderLineID as nvarchar(20)
AS
update OrderLine set TransType='xyz' where rderLineID=@OrderLineID">OrderLineID=@OrderLineID
return
GO
-
CREATE TRIGGER [AfterInsertOrderLine] ON [dbo].[OrderLine]
AFTER INSERT
AS
SET NOCOUNT ON
UPDATE O SET TransType = 'xyz' from dbo.OrderLine O inner join Inserted I on O.OrderLine = I.OrderLine
-
You created nested triggerd.
Your UPDATE trigger invokes sp KVtest which updates the table and fires the trigger which invokes the sp which fires the trigger which ...
Read BOL about Recursive Triggers and Nested Triggers.
Must help.
_____________
Code for TallyGenerator -
One more reason to not use sp in triggers
. -
Your script does not use sp, but it will cause the same problem.
There's nothing wrong with sp, it's about updting table from its own trigger.
_____________
Code for TallyGenerator -
Are you sure??
AFTER INSERT
AS
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply