mQMVFBjy

1IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'IC_StockUpdate') > 0)
2    DROP TRIGGER [dbo].[IC_StockUpdate]
3GO
4CREATE TRIGGER [dbo].[IC_StockUpdate] ON [dbo].[WarehouseItem]
5AFTER UPDATE
6AS
7BEGIN
8
9    -- Get Product Id
10    DECLARE @StockItemID INT = (SELECT ItemID FROM INSERTED);
11    DECLARE @WarehouseID INT = (SELECT WarehouseID FROM INSERTED);
12
13    -- Proceed If This Product Is Syncable
14    IF (dbo.IC_CanSyncProduct(@StockItemID) = 1)
15    BEGIN
16
17        -- Proceed If This Warehouse Is Syncable
18        IF (dbo.IC_CanSyncStock(@WarehouseID) = 1)
19        BEGIN
20
21            -- Check If Product Is Synced
22            IF ((SELECT COUNT(*) FROM IC_ProductCreateQueue WHERE StockItemID = @StockItemID) > 0)
23            BEGIN
24
25                -- Check If Stock Update Queue Entry Already Exists
26                IF ((SELECT COUNT(*) FROM IC_StockUpdateQueue WHERE StockItemID = @StockItemID) > 0)
27                BEGIN
28
29                    -- Reset [StockUpdate] Queue Entry
30                    UPDATE IC_StockUpdateQueue SET Synced = 0
31                    WHERE StockItemID = @StockItemID;
32
33                END
34                ELSE
35                BEGIN
36
37                    -- Insert [StockUpdate] Queue Entry
38                    INSERT INTO IC_StockUpdateQueue (StockItemID, Synced) VALUES
39                    (@StockItemID, 0);
40
41                END
42
43            END
44            ELSE
45            BEGIN
46
47                -- Insert [ProductCreate] Queue Entry
48                INSERT INTO IC_ProductCreateQueue (StockItemID, Synced) VALUES
49                (@StockItemID, 0);
50
51                -- Insert [StockUpdate] Queue Entry
52                INSERT INTO IC_StockUpdateQueue (StockItemID, Synced) VALUES
53                (@StockItemID, 0);
54
55            END
56
57        END
58
59    END
60
61END
62GO
63	
64-- Get Product Id
65DECLARE @StockItemID INT = (SELECT ItemID FROM INSERTED);
66DECLARE @WarehouseID INT = (SELECT WarehouseID FROM INSERTED);
67	
68-- Reset [StockUpdate] Queue Entry
69UPDATE IC_StockUpdateQueue SET Synced = 0
70FROM inserted 
71WHERE inserted.itemID = StockItemID;
72	
73DECLARE @inserted TABLE (StockItemID INT, WarehouseID INT)
74
75INSERT INTO @inserted (StockItemID, WarehouseID)
76SELECT StockItemID, WarehouseID
77FROM INSERTED i
78WHERE dbo.IC_CanSyncProduct(i.StockItemID)=1
79AND dbo.IC_CanSyncStock(i.WarehouseID)=1
80	
81;WITH ResetQueueEntry
82(
83    SELECT StockItemID
84    FROM @inserted i
85    WHERE EXISTS(SELECT 1 FROM IC_ProductCreateQueue q WHERE q.StockItemID = i.StockItemID)
86    AND EXISTS(SELECT 1 FROM IC_StockUpdateQueue q WHERE q.StockItemID = i.StockItemID))
87)
88
89-- Reset [StockUpdate] Queue Entry
90UPDATE IC_StockUpdateQueue 
91SET Synced = 0
92WHERE StockItemID IN (SELECT StockItemID FROM ResetStockUpdate);
93
94WITH InsertQueueEntry
95(
96     SELECT StockItemId, 0 Synced
97     FROM @inserted
98     WHERE EXISTS(SELECT 1 FROM IC_ProductCreateQueue q WHERE q.StockItemID = i.StockItemID)       
99     AND NOT EXISTS(SELECT 1 FROM IC_StockUpdateQueue q WHERE q.StockItemID = i.StockItemID))
100)
101-- Insert [StockUpdate] Queue Entry
102INSERT INTO IC_StockUpdateQueue (StockItemID, Synced)
103SELECT StockItemID, Synced
104FROM InsertQueueEntry
105
106WITH CreateProductEntry
107(
108     SELECT StockItemId, 0 Synced
109     FROM @inserted
110     WHERE NOT EXISTS(SELECT 1 FROM IC_ProductCreateQueue q WHERE q.StockItemID = i.StockItemID)
111)
112-- Insert [ProductCreate] Queue Entry
113INSERT INTO IC_ProductCreateQueue (StockItemID, Synced)
114SELECT StockItemId, Synced
115FROM CreateProductEntry
116
117WITH CreateStockEntry
118(
119     SELECT StockItemId, 0 Synced
120     FROM @inserted
121     WHERE NOT EXISTS(SELECT 1 FROM IC_ProductCreateQueue q WHERE q.StockItemID = i.StockItemID)
122)
123-- Insert [StockUpdate] Queue Entry
124INSERT INTO IC_StockUpdateQueue (StockItemID, Synced)
125SELECT StockItemId, Synced
126FROM CreateProductEntry
127	
128IF (SELECT COUNT(*) FROM Deleted) > 1
129   BEGIN
130        Return
131   END