Click here to Skip to main content
16,022,309 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to insert records into my table named "CBM_Count_Daily" using trigger upon data insert in table named "Shipment_alloc_request".
Below is the trigger I have implemented in my DB.


But when a new record is inserted in the "Shipment_alloc_request" table, all the old records are also being inserted in my new table "CBM_Count_Daily" which should not be the case.
I hope you guys got my question. I want only the newly records created to be inserted in my query. Can someone please let me know or modify the trigger design above accordingly as to what i am missing


What I have tried:

SQL
create TRIGGER [dbo].[CBM_Out] ON [dbo].[shipment_alloc_request]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
insert into CBM_Count_Daily
(
Trans_Date,
Company,
Warehouse,
CBM_In,
CBM_Out,
Total_CBM,
Branch,
Group_Company
)
SELECT
	SAR.DATE_TIME_STAMP,
	SAR.COMPANY,
	SH.WAREHOUSE,
	NULL,
    SUM(SAR.ALLOCATED_QTY*IUOM.LENGTH) AS CBM_Out,
	NULL,
	CASE when SH.warehouse IN ('C','D') THEN 'DIP' ELSE 'DIC'
	END AS BRANCH,
	CM.IP_ADDRESS
FROM SHIPMENT_ALLOC_REQUEST SAR	
INNER JOIN ITEM_UNIT_OF_MEASURE IUOM ON SAR.ITEM = IUOM.ITEM
INNER JOIN SHIPMENT_HEADER SH ON SAR.INTERNAL_SHIPMENT_NUM = SH.INTERNAL_SHIPMENT_NUM
INNER JOIN COMPANY CM ON SAR.COMPANY = CM.COMPANY
GROUP BY SAR.DATE_TIME_STAMP,SAR.COMPANY,SH.warehouse,CM.IP_ADDRESS;
END
Posted

1 solution

Use the inserted virtual table to get the list of records which were inserted.

Learn how to use the inserted and deleted tables with DML triggers to inspect changes. - SQL Server | Microsoft Learn[^]
SQL
INSERT INTO CBM_Count_Daily
(
    ...
)
SELECT
    ...
FROM 
    inserted SAR	
    INNER JOIN ITEM_UNIT_OF_MEASURE IUOM ON SAR.ITEM = IUOM.ITEM
    INNER JOIN SHIPMENT_HEADER SH ON SAR.INTERNAL_SHIPMENT_NUM = SH.INTERNAL_SHIPMENT_NUM
    INNER JOIN COMPANY CM ON SAR.COMPANY = CM.COMPANY
GROUP BY 
    SAR.DATE_TIME_STAMP,
    SAR.COMPANY,
    SH.warehouse,
    CM.IP_ADDRESS
;
 
Share this answer
 
Comments
Rajath Savanth 18-Sep-24 7:28am    
Hi Richard, thanks for the reply. I did try the query provided by you. But I am not getting the expected result in my CBM_Out column. For a better understanding, I just ran the select query and provided the result of the same V/S the result I am getting after replacing the query with yours.

Expected result -
DATE_TIME_STAMP COMPANY WAREHOUSE (No column name) CBM_Out (No column name) BRANCH IP_ADDRESS INTERNAL_SHIPMENT_NUM
2024-09-18 11:04:14.510 Federal Foods G NULL 0.0336400000 NULL DIC Federal Foods-Grp 11254624
2024-09-18 11:04:14.713 Federal Foods G NULL 0.9082800000 NULL DIC Federal Foods-Grp 11254624
2024-09-18 11:04:14.727 Federal Foods G NULL 0.9923800000 NULL DIC Federal Foods-Grp 11254624


Current result -
Trans_Date Company Warehouse CBM_In CBM_Out Total_CBM Branch Group_Company ISN
2024-09-18 11:04:14.510 Federal Foods G NULL 0 NULL DIC Federal Foods-Grp 11254624
2024-09-18 11:04:14.713 Federal Foods G NULL 1 NULL DIC Federal Foods-Grp 11254624
2024-09-18 11:04:14.727 Federal Foods G NULL 1 NULL DIC Federal Foods-Grp 11254624


The column CBM_out is actually decimal values which is a result of multiplication seen above in the select query.
It would be really helpful if you could help me get this resolved. Thanks
Richard Deeming 18-Sep-24 7:54am    
The difference in the CBM_Out column looks like you're multiplying integer values rather than floating-point values.

But without seeing the data you're inserting, and the related data in the other tables, it's hard to tell.
Rajath Savanth 18-Sep-24 7:59am    
Ah ok.
Then here is an example of the data -

ALLOCATED_QTY LENGTH RESULT
6 0.03234 0.19404
5 0.145 0.725

It's basically
6 * 0.03234 = 0.19404

And data_type of CBM_Out column is DECIMAL. Please let me know if you need any more info.
Thanks
Richard Deeming 18-Sep-24 8:14am    
There must be something else going on. I've just tried a simplified example[^], and it works as expected - the calculated CBM returns 0.19404, not 0.
Rajath Savanth 18-Sep-24 8:15am    
Richard. Sorry it was my mistake. Your solution works like a charm. Thank you so much. You are amazing.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900