We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL 2005: trigger not being triggered

malraff
malraff asked
on
Medium Priority
231 Views
Last Modified: 2012-05-11
hi all

i have an after insert trigger on a table, that basically writes status changes off to another table.
depending on how i change the status in our erp system the trigger does not fire!

eg 1 form of changing the order status fires the trigger but another does not - a sql update always fires the trigger

but regardless of the method of changing the  status - the same status field is getting updated in the table


can anyone advise of potential reasons for this? im trying to see what is happening differently using profiler but so far i cant pinpoint the problem

i have attached my trigger in case anyone sees an issue with it


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER Trigger [Suspense_Orders] on [dbo].[SorMaster] after Update
as
DECLARE @SalesOrder VarChar(8)

DECLARE STATUSORDER CURSOR FOR
  SELECT ins.SalesOrder from inserted ins inner join deleted del on ins.SalesOrder = del.SalesOrder
     where (del.OrderStatus = 'S' and ins.OrderStatus = '1')or (del.OrderStatus = '1' and ins.OrderStatus = 'S')

OPEN STATUSORDER

FETCH NEXT FROM STATUSORDER into
  @SalesOrder

While @@FETCH_STATUS = 0
  BEGIN


    Insert into WLND_Order_Status (SalesOrder,OrderStatus,Date_Created,Previous_Status)
      select s.SalesOrder,s.OrderStatus,getdate(),d.OrderStatus from SorMaster s 		 
		left join deleted d on d.SalesOrder = s.SalesOrder
		where s.SalesOrder = @SalesOrder


      FETCH NEXT FROM STATUSORDER INTO
         @SalesOrder
  END

CLOSE STATUSORDER
DEALLOCATE STATUSORDER

Open in new window

Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
what about not using a cursor at all :)
ALTER Trigger [Suspense_Orders] on [dbo].[SorMaster] after Update
as

  Insert into WLND_Order_Status 
   ( SalesOrder, OrderStatus, Date_Created, Previous_Status)
  select ins.SalesOrder , ins.OrderStatus ,getdate() ,del.OrderStatus 
    from inserted ins 
    join deleted del 
      on ins.SalesOrder = del.SalesOrder
   where (del.OrderStatus = 'S' and ins.OrderStatus = '1')
      or (del.OrderStatus = '1' and ins.OrderStatus = 'S')

Open in new window

Author

Commented:
hi angell

yea my first effort had no cursor either :) i thought maybe i was missing transactions hence the cursor - which made no difference

oh and this did not fire the trigger either
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
can you show that with a sample script? aka how do you see that the trigger is not fired?
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Close the thread.

Author

Commented:
Found error myself
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.