SQL Server : AFTER INSERT trigger and INSERT INTO

Question

I want to create a trigger that will fill up my sales history base after firing in the ORDER table.

I am creating a specific order in regular data base and after that this order automatically goes to sales_history database.

Below part works properly.

When I create a new order in regular database my sales_history database is growing with new ID_ORDERS, hooray! :)

ALTER TRIGGER [dbo].[InsertTrig]
ON [dbo].[ORDER]
AFTER INSERT 
AS
    IF EXISTS (SELECT * 
               FROM inserted i
               WHERE i.ID_TYPE = 1)   -- specific order type
    BEGIN
        INSERT INTO id.dbo.sales_history (id_order)
            SELECT i.ID_ORDER FROM inserted i
    END

The problem arises when I want join another table. The trigger stops working

ALTER TRIGGER [dbo].[InsertTrig]
ON [dbo].[ORDER]
AFTER INSERT 
AS
    IF EXISTS (SELECT * 
               FROM inserted i
               WHERE i.ID_TYPE = 1)   -- specific order type
    BEGIN
        INSERT INTO id.dbo.sales_history (id_order, id_item)
            SELECT
                inserted.ID_ORDER, ORDER_DETAILS.ID_ITEM
            FROM
                inserted
            INNER JOIN
                ORDER_DETAILS ON ORDER_DETAILS.ID_ORDER = inserted.ID_ORDER
    END

I also tried this way, and still nothing :(

ALTER TRIGGER [dbo].[InsertTrig]
ON [dbo].[ORDER]
AFTER INSERT 
AS
    IF EXISTS (SELECT * 
               FROM inserted i
               WHERE i.ID_TYPE = 1)   -- specific order type
    BEGIN
        DECLARE @xyz AS numeric(18, 0)

        SET @xyz = (SELECT inserted.ID_ORDER FROM inserted)

        INSERT INTO id.dbo.sales_history (id_order, id_item)
            SELECT
                ORDER.ID_ORDER, ORDER_DETAILS.ID_ITEM
            FROM
                ORDER
            INNER JOIN
                ORDER_DETAILS ON ORDER_DETAILS.ID_ORDER = ORDER.ID_ORDER
            WHERE
                ORDER.ID_ORDER = @xyz
    END

I want to create a trigger that will automatically fill up my sales history base after firing in ORDER table.

Answer 1

Your trigger is on the Order table, meaning SQL Server fires it after you insert records into the Order table. At which point, the relevant records in the Order_Details table couldn't have been inserted yet, because they have a foreign key to the Order table.
This is why an inner join between your inserted table and the Order_details table returns 0 rows.

If you want your sales_history from the order_details table, you have to populate it after you insert the records to the order_details table.

CREATE OR ALTER TRIGGER [dbo].[OrderDetails_AfterInsert]
ON [dbo].[ORDER_DETAILS]
AFTER INSERT 
AS
    INSERT INTO id.dbo.sales_history (id_order, id_item)
    SELECT
        inserted.ID_ORDER, inserted.ID_ITEM
    FROM
        inserted
    INNER JOIN
        [ORDER] ON [ORDER].ID_ORDER = inserted.ID_ORDER
    WHERE [ORDER].ID_TYPE = 1  -- specific order type

As a side note: InsertTrig is bad name. Note the name of the trigger in my answer - it tells you exactly what this trigger is for, and on what table.