Solved

SQL Trigger with condition

Posted on 2014-07-31
31
192 Views
Last Modified: 2014-08-20
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
Comment
Question by:skull52
  • 18
  • 7
  • 5
  • +1
31 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 40231828
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
 

Author Comment

by:skull52
ID: 40231922
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40232152
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
 

Author Comment

by:skull52
ID: 40232278
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40232314
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
 

Author Comment

by:skull52
ID: 40232454
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
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40232492
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
 

Author Comment

by:skull52
ID: 40232519
Yes Void/Deletes are actually deletes
0
 

Author Comment

by:skull52
ID: 40232807
I looked through the code and cant find the problem
0
 

Author Comment

by:skull52
ID: 40234521
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
 

Author Comment

by:skull52
ID: 40234722
Hello...
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40234790
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
 

Author Comment

by:skull52
ID: 40234879
Your code did not work either
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40234914
I agree.  That's why I'm hoping someone else can help you.
0
 

Author Comment

by:skull52
ID: 40234925
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40238836
> 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
 

Author Comment

by:skull52
ID: 40238918
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40239247
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
 

Author Comment

by:skull52
ID: 40239380
<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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40240497
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
 

Author Comment

by:skull52
ID: 40241671
No... SOP30200 is where the order number and line data is inserted when an order is voided
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40241928
Right, so you create the trigger on it. (in the prev. post when I typed "SOP3200" I meant "SOP30200").
0
 

Author Comment

by:skull52
ID: 40242189
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40242223
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
 

Author Comment

by:skull52
ID: 40242323
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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40242449
> 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
 

Author Comment

by:skull52
ID: 40243840
>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
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 40243864
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
 

Author Comment

by:skull52
ID: 40243929
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
 

Accepted Solution

by:
skull52 earned 0 total points
ID: 40263005
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
 

Author Closing Comment

by:skull52
ID: 40272063
No viable solution was provided
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now