Trigger doesnt work when using sp_executesql

Question

Trigger doesnt work when using sp_executesql

Andreas Håkansson

Old Hand

Points: 368
More actions
October 21, 2010 at 8:28 am

#388065

Hi
I have a .net application that uses sp_excutesql to insert and update posts in my db. I also have a trigger that works as a charm when i manually updates or insert posts in my db. The trigger doesent work when posting is done from my .net application. The posts is fine, the only thing thats not working is the trigger... Does anyone know if there is a difference between using the sp and manually posting in the db? The SQL-server version is 2008.
Thanks!
Andreas

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

BULK INSERT behaves differently for triggers, but not sp_executesql;

it might be the way your trigger is written...lets look at the trigger itself and see if there might be a clue to the behavior difference. maybe the trigger is checking specific values, and the sp_executesql is not sending them or something.

can you post the trigger?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Andreas Håkansson Old Hand Points: 368 More actions · Answer 2

The trigger is very simple...

ALTER TRIGGER [dbo].[YrRef]

ON [dbo].[Ord]

for INSERT,update

AS

declare

@R1 int,

@YrRef varchar(50),

@ordno int

select @R1=R1 from inserted

select @ordno=ordno from inserted

select @YrRef=Inf2 from R1 where Rno=@R1

update ord set Inf2=@YrRef where ordno=@ordno

Andreas Håkansson Old Hand Points: 368 More actions · Answer 3

Found the answer here after you wrote the correct name of this operatin... Bulk insert... Wouldnt have understood this fenomenon without your help!

http://msdn.microsoft.com/en-us/library/ms187640.aspx

Thanx! 😀

Lowell SSC Guru Points: 323504 More actions · Answer 4

Andreas your trigger is currently designed to handle only one row being inserted at a time...

if you are bulk inserting, only one row would be affected when you trip the trigger with the insert/update.

i would recommend rewriting it to something like this instead, which handles multiple rows gracefully:

ALTER TRIGGER [dbo].[YrRef]

ON [dbo].[Ord]

for INSERT,update

AS

update [dbo].[Ord]

set [Ord].Inf2 = R1.Inf2

FROM INSERTED

INNER JOIN R1 ON INSERTED.R1 = R1.Rno

WHERE [Ord].ordno = INSERTED.ordno

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!