?
Solved

After update trigger does not fire

Posted on 2007-10-09
9
Medium Priority
?
2,719 Views
Last Modified: 2008-01-09
SQL Server 2000
VB.NET 2003

I have an after update trigger in one of my table. it works fine when EXECuting a stored proc from Query Analyzer that updates the table.  The prob is that the trigger does not fire when running the SP from my VB.net code using SqlComm.ExecuteNonQuery.

Any idea how to fire the trigger when running my SP from vb.NET?

Thank you.
0
Comment
Question by:cezarF
  • 4
  • 4
9 Comments
 
LVL 14

Accepted Solution

by:
Jai S earned 1600 total points
ID: 20039153
the firing of trigger depends on SQL ...and this has really nothing to do with your FRONT END...
just check one thing...see whether the DATA really gets updated to your table first...if it updates then your trigger has to fire...unless there are some operations that makes the trigger to fail ( like inserting NULL values to non nullable columns)
0
 
LVL 14

Author Comment

by:cezarF
ID: 20039219
thanks!

in both scenario the data is being updated. it's just that the trigger does not fire when the sp is executed from vb.net.
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20039243
can you tell us what is there in the trigger...
just a note on checking

create a dummy table...with one column
insert a record ion your trigger to this dummy table and check after your UPDATE...if there are records in this table then your trigger fires and the problem is with you other content in the trigger...
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 14

Author Comment

by:cezarF
ID: 20039623
the trigger is a bit complecated. the only thing I noticed is that it takes about 30 secs when I exec the sp in query analyzer but a lot faster when called in vb.net. seems to me that the trigger is not being fired at all.  
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20039636
there it goes...may be it just TIMES OUT...try increasing your CONNECTION TIMEOUT in your SERVER
database --right click -- properties -- connection timeout
0
 
LVL 14

Author Comment

by:cezarF
ID: 20039649
contemplating to just convert the trigger to an SP. been trying to solve this the whole day. another strange thing is a similar FOR INSERT trigger is working.  
0
 
LVL 14

Author Comment

by:cezarF
ID: 20039666
i'm setting the time-out to 120 in my vb.net code. besides, if i hit a time-out error, i should get an error message and the table should not be updated.
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20039755
incase of a trigger you wont get an error...becos its a disconnected process from your UPDATE from VB.NEt...
you probably need to handle that error in your SQL - TRIGGER using the error objects that SQL 2005 provides...
0
 
LVL 7

Assisted Solution

by:assyst
assyst earned 400 total points
ID: 20040893
Try doing something simple in the trigger like updating a column and check whether the trigger is getting fired. If that works then try with your trigger logic.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question