issue with the trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

issue with the trigger

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-14 : 11:56:28

Here I have a trigger which works fine When i insert a record in to the table .It updates it perfectly.But this trigger is suppose to fire when an application inserts a record and its giving an error..Is there anything I need to set

CREATE TRIGGER [tgr_block] ON [dbo].[tbl_queue]
FOR INSERT
AS

SET XACT_ABORT ON

DECLARE @order int
DECLARE @seq int
DECLARE @emp_number varchar(13)
DECLARE @service_name varchar(20)

SELECT @order=ref_number ,@service_name=service_name,@seq=core, @emp_number=app_number FROM inserted

IF @service_name='EDU'

BEGIN

IF EXISTS (SELECT emp_number FROM SQL_LS.[TOR].[dbo].[tbl_web] WHERE emp_number in (@emp_number))

BEGIN

UPDATE tbl_queue
SET status='IN-DM'
WHERE core=@seq

END

END

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-14 : 12:36:38

what error is it giving you?
do you want the triger to fire after the insert or instead of it??

Go with the flow & have fun! Else fight the flow

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-14 : 12:43:08

The application is inserting one record at a time ..But its not a multiple entry.one by one..its showing an application error..but if u remove the trigger it works fine..

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-14 : 12:47:14

ok so what is the error?

Go with the flow & have fun! Else fight the flow

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-14 : 13:00:44

From the application point of view..Its giving "An unexpected application error has occurred". I don't have access to the code of the application but I know its inserting a record there

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-14 : 13:08:25

ufff...
i don't see anything in the trig that would crash the app... it could be that the app is not handling it well?

Go with the flow & have fun! Else fight the flow

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-14 : 13:11:01

But if we remove the trigger its working and inserting as well..so its just an update happening here and its not even interacting with other tables

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-14 : 13:25:41

well try inserting some data in QA with trigger enabled and see if that works

Go with the flow & have fun! Else fight the flow

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-14 : 13:34:04

it works without the application..if I insert a record the updation happens.......Again the application inserts records if i remove the trigger and works fine...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-14 : 13:47:33

well if the trigger works without the app, then it's nothing wrong with it.
ergo, the problem must be in the app.
if you're inserting the data with datasets maybe there are concurency isuess....
you could comment a part of the trigger to see what crashes it. and when u find that out, see what the app handles that.
as i said i don't see anything wrong in the trigger. maybe someone else will.

Go with the flow & have fun! Else fight the flow

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-09-14 : 16:52:51

I got it..since Iam using linked server to access the database...the user-name tighed to the application and one database didn't have access to select from the other with that particular user-name.Now the permission is provided and now its working fine..Thanks a lot guys for the support

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources