Go Premium for a chance to win a PS4. Enter to Win

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

SQL Trigger with condition

I have a trigger on a table that has serial numbers, and I want to track when someone deletes a serial number from an order or voids the whole order.  I have an action flag that if the order is voided I want it to put a V in the action Column and if the serial number is deleted it puts a D in that column. My problem is that that it puts a V in that column for both Voids and Deletes.

 Audit-trigger.jpg
ALTER TRIGGER [dbo].[SSG_Audit_SOP10201_trigger] ON [dbo].[SOP10201]
AFTER INSERT
	,UPDATE
	,DELETE
AS
---
DECLARE @inserted_ct AS INT
DECLARE @deleted_ct AS INT
--DECLARE @Void_ct AS CHAR(25)
DECLARE @action AS CHAR(3)
DECLARE @a_ACTION AS CHAR(3)
DECLARE @a_CSREP AS CHAR(10)
DECLARE @a_DEX_ROW_ID AS INT

SET @a_CSREP = SYSTEM_USER
SET @inserted_ct = 0
SET @deleted_ct = 0
--SET @Void_ct = 0
SET @inserted_ct = (
		SELECT count(*)
		FROM inserted
		)
SET @deleted_ct = (
		SELECT count(*)
		FROM deleted
		)

IF (EXISTS (SELECT COUNT(DISTINCT S.SOPNUMBE) AS SOP_COUNT 
FROM         SOP30200 AS S INNER JOIN
                      SSG_Audit_SOP10201 AS V ON S.SOPNUMBE = V.SOPNUMBE
WHERE     (S.VOIDSTTS = 1)))
BEGIN
INSERT INTO SSG_Audit_SOP10201
	SELECT *
		,'V'
		,GetDate()
		,@a_CSREP
	FROM deleted
END
IF(NOT EXISTS (SELECT COUNT(DISTINCT S.SOPNUMBE) AS SOP_COUNT 
FROM         SOP30200 AS S INNER JOIN
                      SSG_Audit_SOP10201 AS V ON S.SOPNUMBE = V.SOPNUMBE
WHERE     (S.VOIDSTTS = 1)))  
BEGIN
INSERT INTO SSG_Audit_SOP10201
	SELECT *
		,'D'
		,GetDate()
		,@a_CSREP
	FROM deleted
END

IF @inserted_ct > 0
	SET @action = 'I' 

--IF @deleted_ct > 0
--	SET @action = 'D'

IF (@inserted_ct > 0)
	AND (@deleted_ct > 0)
	SET @action = 'U'


--IF (@action = 'D')
--BEGIN
--	INSERT INTO SSG_Audit_SOP10201
--	SELECT *
--		,'D'
--		,GetDate()
--		,@a_CSREP
--	FROM deleted
--END

IF (@action = 'U')
BEGIN
	INSERT INTO SSG_Audit_SOP10201
	SELECT *
		,'U_D'
		,GetDate()
		,@a_CSREP
	FROM deleted

	INSERT INTO SSG_Audit_SOP10201
	SELECT *
		,'U_I'
		,GetDate()
		,@a_CSREP
	FROM inserted
END

IF (@action = 'I')
BEGIN
	INSERT INTO SSG_Audit_SOP10201
	SELECT *
		,'I'
		,GetDate()
		,@a_CSREP
	FROM inserted
END
GO

Open in new window

0
skull52
Asked:
skull52
  • 18
  • 7
  • 5
  • +1
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
use inserted and deleted to determine if there was a change.

IF EXISTS (
    SELECT
        *
    FROM
        INSERTED I
        JOIN
        DELETED D ON D.SerialNumber<> I.SerialNumber
      -- and i.SerialNumber = 'deleted value' or is null?
)
   -- set your d

    )
0
 
skull52Author Commented:
Kyle, Thanks for the quick response. So would I replace
 IF(NOT EXISTS (SELECT COUNT(DISTINCT S.SOPNUMBE) AS SOP_COUNT
FROM         SOP30200 AS S INNER JOIN
                      SSG_Audit_SOP10201 AS V ON S.SOPNUMBE = V.SOPNUMBE
WHERE     (S.VOIDSTTS = 1)))  
BEGIN
INSERT INTO SSG_Audit_SOP10201
      SELECT *
            ,'D'
            ,GetDate()
            ,@a_CSREP
      FROM deleted
END
With
IF EXISTS (
    SELECT
        *
    FROM
        INSERTED I
        JOIN
        DELETED D ON D.SerialNumber<> I.SerialNumber
      -- and i.SerialNumber = 'deleted value' or is null? <Is this line commented out deliberately?>
BEGIN
INSERT INTO SSG_Audit_SOP10201
      SELECT *
            ,'D'
            ,GetDate()
            ,@a_CSREP
      FROM deleted
END
0
 
Scott PletcherSenior DBACommented:
I've rewritten the query to get rid of unnecessary elements.

I'm not sure what the unique column(s) are for table SOP10201, but my best guess was SOPNUMBE.  If that's not right, change the joins below to use the appropriate column(s).

--
ALTER TRIGGER [dbo].[SSG_Audit_SOP10201_trigger]
ON [dbo].[SOP10201]
AFTER INSERT
      ,UPDATE
      ,DELETE
AS
SET NOCOUNT ON
---
DECLARE @action char(1)
DECLARE @a_CSREP char(10)

SET @a_CSREP = SYSTEM_USER

IF EXISTS(SELECT TOP (1) * FROM inserted)
    IF EXISTS(SELECT TOP (1) * FROM deleted)
        SET @action = 'U'
    ELSE
        SET @action = 'I'
ELSE
    SET @action = 'D'

INSERT INTO SSG_Audit_SOP10201
    SELECT *
          ,'V'
          ,GetDate()
          ,@a_CSREP
      FROM deleted d
      INNER JOIN inserted i ON
          i.SOPNUMBE = d.SOPNUMBE
    WHERE d.VOIDSTTS = 1

INSERT INTO SSG_Audit_SOP10201
    SELECT *
          ,'D'
          ,GetDate()
          ,@a_CSREP
      FROM deleted d
      INNER JOIN inserted i ON
          i.SOPNUMBE = d.SOPNUMBE
    WHERE d.VOIDSTTS IS NULL OR d.VOIDSTTS <> 1

--IF (@action = 'D')
--BEGIN
--      INSERT INTO SSG_Audit_SOP10201
--      SELECT *
--            ,'D'
--            ,GetDate()
--            ,@a_CSREP
--      FROM deleted
--END

IF (@action = 'U')
BEGIN
      INSERT INTO SSG_Audit_SOP10201
      SELECT *
            ,'U_D'
            ,GetDate()
            ,@a_CSREP
    FROM deleted

      INSERT INTO SSG_Audit_SOP10201
      SELECT *
            ,'U_I'
            ,GetDate()
            ,@a_CSREP
      FROM deleted
END

IF (@action = 'I')
BEGIN
      INSERT INTO SSG_Audit_SOP10201
      SELECT *
            ,'I'
            ,GetDate()
            ,@a_CSREP
      FROM inserted
END
GO
0
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.

 
skull52Author Commented:
Scott, thanks for responding, however I am getting an error

Msg 207, Level 16, State 1, Procedure SSG_Audit_SOP10201_trigger, Line 30
Invalid column name 'VOIDSTTS'.
The above one may be caused by the fact that VOIDSTTS is not in the SOP10201 table but in table SOP30200
that is what I used to validate whether the order was voided as that table contains the void status.
IF (EXISTS (SELECT COUNT(DISTINCT S.SOPNUMBE) AS SOP_COUNT
FROM         SOP30200 AS S INNER JOIN
                      SSG_Audit_SOP10201 AS V ON S.SOPNUMBE = V.SOPNUMBE
WHERE     (S.VOIDSTTS = 1)))

 
Msg 8101, Level 16, State 1, Procedure SSG_Audit_SOP10201_trigger, Line 22
An explicit value for the identity column in table 'SSG_Audit_SOP10201' can only be specified when a column list is used and IDENTITY_INSERT is ON.
0
 
Scott PletcherSenior DBACommented:
Sorry, overlooked which table that column was coming from.

As the other error, you really ought to remove the identity property from that column in the audit table.  Unfortunately, there's no easy, direct way to do that.  But you don't really need an identity column in an audit table.

You could for now instead code specific columns to be inserted into the table:


ALTER TRIGGER [dbo].[SSG_Audit_SOP10201_trigger]
 ON [dbo].[SOP10201]
 AFTER INSERT
       ,UPDATE
       ,DELETE
 AS
 SET NOCOUNT ON
 ---
 DECLARE @action char(1)
 DECLARE @a_CSREP char(10)

 SET @a_CSREP = SYSTEM_USER

 IF EXISTS(SELECT TOP (1) * FROM inserted)
     IF EXISTS(SELECT TOP (1) * FROM deleted)
         SET @action = 'U'
     ELSE
         SET @action = 'I'
 ELSE
     SET @action = 'D'

 INSERT INTO SSG_Audit_SOP10201 --( ...column_list... )
     SELECT * --( ...column_list... )
           ,'V'
           ,GetDate()
           ,@a_CSREP
       FROM deleted d
       INNER JOIN (
            SELECT DISTINCT  SOPNUMBE, VOIDSTTS
            FROM SOP30200
       ) AS S ON
           S.SOPNUMBE = d.SOPNUMBE
     WHERE S.VOIDSTTS = 1

 INSERT INTO SSG_Audit_SOP10201 --( ...column_list... )
     SELECT * --( ...column_list... )
           ,'D'
           ,GetDate()
           ,@a_CSREP
       FROM deleted d
       INNER JOIN (
            SELECT DISTINCT  SOPNUMBE, VOIDSTTS
            FROM SOP30200
       ) AS S ON
     WHERE S.VOIDSTTS IS NULL OR S.VOIDSTTS <> 1

 --IF (@action = 'D')
 --BEGIN
 --      INSERT INTO SSG_Audit_SOP10201 --( ...column_list... )
 --      SELECT * --( ...column_list... )
 --            ,'D'
 --            ,GetDate()
 --            ,@a_CSREP
 --      FROM deleted
 --END

 IF (@action = 'U')
 BEGIN
       INSERT INTO SSG_Audit_SOP10201 --( ...column_list... )
       SELECT * --( ...column_list... )
             ,'U_D'
             ,GetDate()
             ,@a_CSREP
     FROM deleted

       INSERT INTO SSG_Audit_SOP10201 --( ...column_list... )
       SELECT * --( ...column_list... )
             ,'U_I'
             ,GetDate()
             ,@a_CSREP
       FROM deleted
 END

 IF (@action = 'I')
 BEGIN
       INSERT INTO SSG_Audit_SOP10201 --( ...column_list... )
       SELECT * --( ...column_list... )
             ,'I'
             ,GetDate()
             ,@a_CSREP
       FROM inserted
 END
 GO
0
 
skull52Author Commented:
OK, I got the errors to go away, but now it won't insert Voided or Deleted records but it does the inserted records
ALTER TRIGGER [dbo].[SSG_Audit_SOP10201_trigger]
 ON [dbo].[SOP10201]
 AFTER INSERT
       ,UPDATE
       ,DELETE
 AS
 SET NOCOUNT ON
 ---
 DECLARE @action char(1)
 DECLARE @a_CSREP char(10)

 SET @a_CSREP = SYSTEM_USER

 IF EXISTS(SELECT TOP (1) * FROM inserted)
     IF EXISTS(SELECT TOP (1) * FROM deleted)
         SET @action = 'U'
     ELSE
         SET @action = 'I'
 ELSE
     SET @action = 'D'

 INSERT INTO SSG_Audit_SOP10201 ( SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ, QTYTYPE, SERLTNUM, SERLTQTY, SLTSQNUM, DATERECD, DTSEQNUM, UNITCOST, ITEMNMBR, TRXSORCE, 
                      POSTED, OVRSERLT, BIN, MFGDATE, EXPNDATE, ORIGINAL_DEX_ROW_ID, ACTION, EVENT_DT_TM, GPUSER )
     SELECT   d.SOPTYPE, d.SOPNUMBE, d.LNITMSEQ, d.CMPNTSEQ, d.QTYTYPE, d.SERLTNUM, d.SERLTQTY, d.SLTSQNUM, d.DATERECD, d.DTSEQNUM, d.UNITCOST, d.ITEMNMBR, d.TRXSORCE, 
                      d.POSTED, d.OVRSERLT, d.BIN, d.MFGDATE, d.EXPNDATE, d.DEX_ROW_ID 
           ,'V'
           ,GetDate()
           ,@a_CSREP
       FROM deleted d
       INNER JOIN (
            SELECT DISTINCT  SOPNUMBE, VOIDSTTS
            FROM SOP30200
       ) AS S ON
           S.SOPNUMBE = d.SOPNUMBE
     WHERE S.VOIDSTTS = 1

 INSERT INTO SSG_Audit_SOP10201 ( SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ, QTYTYPE, SERLTNUM, SERLTQTY, SLTSQNUM, DATERECD, DTSEQNUM, UNITCOST, ITEMNMBR, TRXSORCE, 
                      POSTED, OVRSERLT, BIN, MFGDATE, EXPNDATE, ORIGINAL_DEX_ROW_ID, ACTION, EVENT_DT_TM, GPUSER )
     SELECT d.SOPTYPE, d.SOPNUMBE, d.LNITMSEQ, d.CMPNTSEQ, d.QTYTYPE, d.SERLTNUM, d.SERLTQTY, d.SLTSQNUM, d.DATERECD, d.DTSEQNUM, d.UNITCOST, d.ITEMNMBR, d.TRXSORCE, 
                      d.POSTED, d.OVRSERLT, d.BIN, d.MFGDATE, d.EXPNDATE, d.DEX_ROW_ID  
           ,'D'
           ,GetDate()
           ,@a_CSREP
       FROM deleted d
       INNER JOIN (
            SELECT DISTINCT  SOPNUMBE, VOIDSTTS
            FROM SOP30200
       ) AS S ON
        S.SOPNUMBE = d.SOPNUMBE
     WHERE S.VOIDSTTS IS NULL OR S.VOIDSTTS <> 1

 --IF (@action = 'D')
 --BEGIN
 --      INSERT INTO SSG_Audit_SOP10201 --( ...column_list... )
 --      SELECT * --( ...column_list... )
 --            ,'D'
 --            ,GetDate()
 --            ,@a_CSREP
 --      FROM deleted
 --END

 IF (@action = 'U')
 BEGIN
       INSERT INTO SSG_Audit_SOP10201 --( ...column_list... )
       SELECT * --( ...column_list... )
             ,'U_D'
             ,GetDate()
             ,@a_CSREP
     FROM deleted

       INSERT INTO SSG_Audit_SOP10201 --( ...column_list... )
       SELECT * --( ...column_list... )
             ,'U_I'
             ,GetDate()
             ,@a_CSREP
       FROM deleted
 END

 IF (@action = 'I')
 BEGIN
       INSERT INTO SSG_Audit_SOP10201 --( ...column_list... )
       SELECT * --( ...column_list... )
             ,'I'
             ,GetDate()
             ,@a_CSREP
       FROM inserted
 END
 GO 

Open in new window

0
 
Scott PletcherSenior DBACommented:
Those Void/Delete actions are DELETEs or UPDATEs, right?  That code uses the "deleted" table, which will be empty for INSERTs, of course.

Sorry, no time now.  But check the JOIN results and do normal debugging for missing rows.
0
 
skull52Author Commented:
Yes Void/Deletes are actually deletes
0
 
skull52Author Commented:
I looked through the code and cant find the problem
0
 
skull52Author Commented:
OK, I had been playing with this all day yesterday, I have Modified the code yet again, I can get it to flag the insert and the delete but not the void
 Audit-trigger2.jpg
here is the code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[SSG_Audit_SOP10201_trigger] ON [dbo].[SOP10201]
AFTER INSERT
	,UPDATE
	,DELETE
AS
---
DECLARE @inserted_ct AS INT
DECLARE @deleted_ct AS INT
DECLARE @Void_ct AS INT
DECLARE @action AS CHAR(3)
DECLARE @a_ACTION AS CHAR(3)
DECLARE @a_CSREP AS CHAR(10)
DECLARE @a_DEX_ROW_ID AS INT

SET @a_CSREP = SYSTEM_USER
SET @inserted_ct = 0
SET @deleted_ct = 0
SET @Void_ct = 0
SET @inserted_ct = (
		SELECT count(*)
		FROM inserted
		)
SET @deleted_ct = (
		SELECT count(*)
		FROM deleted
		)

SET @Void_ct = (
		SELECT count(*)
		FROM SOP30200 S INNER JOIN
                      SSG_Audit_SOP10201 AS V ON S.SOPNUMBE = V.SOPNUMBE
WHERE     (S.VOIDSTTS = 1)) 


IF @Void_ct >0
BEGIN
INSERT INTO SSG_Audit_SOP10201
	SELECT *
		,'V'
		,GetDate()
		,@a_CSREP
	FROM deleted
	END
	ELSE
IF @Void_ct <= 0
BEGIN
INSERT INTO SSG_Audit_SOP10201
	SELECT *
		,'D'
		,GetDate()
		,@a_CSREP
	FROM deleted
END

IF @inserted_ct > 0
	SET @action = 'I' 

--IF @deleted_ct > 0
--	SET @action = 'D'

IF (@inserted_ct > 0)
	AND (@deleted_ct > 0)
	SET @action = 'U'


--IF (@action = 'D')
--BEGIN
--	INSERT INTO SSG_Audit_SOP10201
--	SELECT *
--		,'D'
--		,GetDate()
--		,@a_CSREP
--	FROM deleted
--END

IF (@action = 'U')
BEGIN
	INSERT INTO SSG_Audit_SOP10201
	SELECT *
		,'U_D'
		,GetDate()
		,@a_CSREP
	FROM deleted

	INSERT INTO SSG_Audit_SOP10201
	SELECT *
		,'U_I'
		,GetDate()
		,@a_CSREP
	FROM inserted
END

IF (@action = 'I')
BEGIN
	INSERT INTO SSG_Audit_SOP10201
	SELECT *
		,'I'
		,GetDate()
		,@a_CSREP
	FROM inserted
END

Open in new window

0
 
skull52Author Commented:
Hello...
0
 
Scott PletcherSenior DBACommented:
You've reverted to the old code.  Technically you can never count on that code being accurate as you can a mix of Delete and Void rows in the same trigger.  

Hopefully someone else can help you with this.
0
 
skull52Author Commented:
Your code did not work either
0
 
Scott PletcherSenior DBACommented:
I agree.  That's why I'm hoping someone else can help you.
0
 
skull52Author Commented:
Well thanks anyway Scott, I may have approach this another way and just bypass attempting to set the void flag within the the trigger and find a way to update it after.
0
 
Vadim RappCommented:
> I have a trigger on a table that has serial numbers

What is the name of that table in your code? if it's SOP10201, then what is SOP30200?

>  and I want to track when someone deletes a serial number from an order or voids the whole order

For that, there should be two triggers, one of the orders table, to track when it's voided, and another on order lines table, to track when order line is deleted. Please explain what tables you have for the serial numbers and for the orders.
0
 
skull52Author Commented:
The SOP10201 is the table that has the trigger on it and it is the serial table, SOP30200 is the history table and adds an entry with the SOPNUMBE from  SOP10201 along with setting the VOIDSTTS = 1 if the order was Voided it would be the last entry in the audit table (SSG_Audit_SOP10201).  If a serial number or line item was deleted from the order it is not entered into the SOP30200 table. what I am trying to do is differentiate between a voided record and a deleted record.
Audit table
This code joins the audit table SOPNUMBE to the SOP30200 table which indicates a voided order
IF (EXISTS (SELECT COUNT(DISTINCT S.SOPNUMBE) AS SOP_COUNT
FROM         SOP30200 AS S INNER JOIN
                      SSG_Audit_SOP10201 AS V ON S.SOPNUMBE = V.SOPNUMBE
WHERE     (S.VOIDSTTS = 1)))
0
 
Vadim RappCommented:
You say that SOP30200 is "history table and adds an entry with the SOPNUMBE from  SOP10201 along with setting the VOIDSTTS = 1 if the order was Voided"

Then, if you want to capture the event of the order being voided, it probably makes sense to create the trigger on SOP30200?

Then, you did not say what happens and in which table when the order line is deleted.

You also did not say what events occur in the table of serial numbers when either of these two events takes place.

I.e. you create trigger on the table with the serial numbers, but the changes you want to capture occur in other tables. Usually, we create trigger on the table where the change takes place, i.e. if we delete an order line, then we create on-delete trigger on the table with order lines; if the order is voided, then on-update on the table with the orders that has column "void" which was just changed; and so on.
0
 
skull52Author Commented:
<Then, you did not say what happens and in which table when the order line is deleted.>
<You also did not say what events occur in the table of serial numbers when either of these two events takes place.>

If the item has a serial number assigned on the order, the SOPNUMBE (Order Number), Item number, and serial number are added to  SOP10201 which is the serial lines table, when the serial number is deleted from the order or changed it is removed from this table and the trigger fires, which is why I am auditing it to see who removed and when it was removed or changed. If the Item has no serial number assigned to it on the SOPNUMBE (Order Number) and the item is removed from the order the trigger does not fire. it track the insert and deletions of serial numbers just fine, and flags inserts with the I flag and deletions with the D flag, but I want it to flag just voids with the V flag. When an order is voided it adds an entry into the SOP30200 with the  VOIDSTTS = 1,  and will the last entry in the audit table (SSG_Audit_SOP10201) What I was trying to do was compare Item number in SSG_Audit_SOP10201 to the Item number in SOP30200 where the  VOIDSTTS = 1 and change that last Deleted record flag from D to V. If I understand you correctly I should have a separate trigger on the SOP30200 table and have that fire on insert and add a record to SSG_Audit_SOP10201, I only want it to add a record if there is a match on SOPNUMBE (Order Number) in the SSG_Audit_SOP10201 and SOP30200  where the VOIDSTTS = 1 Maybe something like

IF (EXISTS (SELECT (DISTINCT S.SOPNUMBE) AS SOP
FROM         SOP30200 AS S INNER JOIN
                      SSG_Audit_SOP10201 AS V ON S.SOPNUMBE = V.SOPNUMBE
WHERE     (S.VOIDSTTS = 1)))
BEGIN
INSERT INTO SSG_Audit_SOP10201
      SELECT *
            ,'V'
            ,GetDate()
            ,@a_CSREP
      FROM deleted
      END
0
 
Vadim RappCommented:
It's still indirect. If I understand correctly, besides SOP30200 you probably have the actual table X with the orders, where the order is marked as void, and when that happens, a record is added to SOP30200, probably by an update trigger in X. If this is correct, then the most straightforward is to create update trigger not on SOP30200 but directly on X; or in fact, modify the already existing one by adding INSERT INTO SSG_Audit_SOP10201 to it.

Basing trigger that writes into audit table on another record already existing in the audit table is very convoluted.

If I'm wrong and there's no table X, then "When an order is voided it adds an entry into the SOP30200 with the  VOIDSTTS = 1" translates into the following in insert trigger on SOP3200:

insert into SSG_Audit_SOP10201 (..) select 'V',getdate(),@a_csrep
from inserted where VOIDSTTS = 1
0
 
skull52Author Commented:
No... SOP30200 is where the order number and line data is inserted when an order is voided
0
 
Vadim RappCommented:
Right, so you create the trigger on it. (in the prev. post when I typed "SOP3200" I meant "SOP30200").
0
 
skull52Author Commented:
Ok, but I only want SOP30200 trigger to do an insert into SSG_Audit_SOP10201 if there is a SOPNUMBE match in both SSG_Audit_SOP10201 and SOP30200  insert table which means that an order with a serial number has been voided not just updated. So if there is an insert on SOP30200 I need a script to compare the SOPNUMBE in SSG_Audit_SOP10201 to SOPNUMBE in SOP30200 insert table which will then add a record into SSG_Audit_SOP10201
0
 
Vadim RappCommented:
You said "when an order is voided it adds an entry into the SOP30200 with the VOIDSTTS = 1". So, if you want to determine if the order was voided, you look at whether the new record being inserted into SOP30200 has VOIDSTTS = 1.

If I understand correctly, you want that done only when the order line of the order being voided had serial number, and you try to identify that by looking at the prior record in the audit table. This is what I say is unstable, i.e. you better not base new audit record on an old audit record. Whether order line had serial number or not is based on the entry in SOP10201, so it's better to find the match of SOP30200 not with SSG_Audit_SOP10201 but with SOP10201 itself.
0
 
skull52Author Commented:
What makes it unstable? The problem with matching SOP30200 with SOP10201 is that as soon as the order is voided the SOPNUMBE along with the serial is removed from SOP10201thats why I wanted to use SSG_Audit_SOP10201 because that will contain the SOPNUMBE along with the serial that was voided
0
 
Vadim RappCommented:
> What makes it unstable?

The fact that success of your operation will depend on two conditions: (1) order is voided (2) there is a prior record in audit table. While if you make it direct, then there's only (1). Less conditions → less possibilities that something goes wrong → more stability.

>  as soon as the order is voided the SOPNUMBE along with the serial is removed from SOP10201

which is the exact place where it makes the most sense to put writing into audit table:

1. check if serial record exists in SOP10201
2. if it does, then
   2.1 remove it
   2.2 put audit record into SSG_Audit_SOP10201
0
 
skull52Author Commented:
>The fact that success of your operation will depend on two conditions: (1) order is voided (2) there is a prior record in audit table.

I already have a trigger on SOP10201 that fires and enters a record into the audit table, when the order is fulfilled (Insert), if a serial number is deleted (Delete), and if the order is voided (Delete) so there will always be a record in the audit table because of the first insert.
0
 
Vadim RappCommented:
There are many ways to do the same thing, what I say is just a suggestion. If it was my solution, I would add to that  trigger on SOP10201 that you mentioned the code that would also write to the audit table - but certainly you can do it your way.

With that said, are you still experiencing some problem? it's now quite a long thread, so if you still need help, please re-clarify where we are at and what exactly does not work as expected.
0
 
skull52Author Commented:
Thanks Vadimr for you suggestions, I will attempt to write the code for what I want to do and will either post any issues I encounter or close this thread.
0
 
skull52Author Commented:
Since there has been no viable solution for my issue I have taken a different approach and created a trigger on the SOP30200 table which seemed to work. Thanks to all who responded.  especially Vadimr who hung in there with me. I am now closing this thread.
0
 
skull52Author Commented:
No viable solution was provided
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 18
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now