[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

SQL 2005: trigger not being triggered

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

0
malraff
Asked:
malraff
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
malraffAuthor 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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you show that with a sample script? aka how do you see that the trigger is not fired?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
malraffAuthor Commented:
i found the problem!

my where statement...
where (del.OrderStatus = 'S' and ins.OrderStatus = '1')or (del.OrderStatus = '1' and ins.OrderStatus = 'S')

when i highlighted this out, i got records being added to my custom table - when i change an order from S to 1 the erp system actually in the background changes the status from S to 0 then 0 to 1!

strange this had me baffled this day or more and its only when i post the question i found the issue!!

SalesOrder      OrderStatus      Date_Created      Completed      Previous_Status
026512      0      2011-04-27 16:23:43.763      NULL      1
026512      0      2011-04-27 16:23:51.327      NULL      0
026512      0      2011-04-27 16:23:55.280      NULL      0
026512      S      2011-04-27 16:23:55.903      NULL      0
026512      0      2011-04-27 16:29:10.627      NULL      S
026512      0      2011-04-27 16:29:19.517      NULL      0
026512      0      2011-04-27 16:29:23.517      NULL      0
026512      1      2011-04-27 16:29:24.047      NULL      0
0
 
radcaesarCommented:
Close the thread.
0
 
malraffAuthor Commented:
Found error myself
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now