SQL Server：AFTER INSERT触发器和INSERT INTO

huangapple
go评论80阅读模式
英文:

SQL Server : AFTER INSERT trigger and INSERT INTO

问题

我想创建一个触发器，在ORDER表中触发后，将填充我的销售历史记录基表。

我正在创建一个特定订单在常规数据库中，之后此订单会自动进入sales_history数据库。

下面的部分正常运行。

当我在常规数据库中创建新订单时，我的销售历史数据库会随着新的ID_ORDERS而增长，太棒了！ SQL Server：AFTER INSERT触发器和INSERT INTO

  1. ALTER TRIGGER [dbo].[InsertTrig]
  2. ON [dbo].[ORDER]
  3. AFTER INSERT 
  4. AS
  5.     IF EXISTS (SELECT * 
  6.                FROM inserted i
  7.                WHERE i.ID_TYPE = 1)   -- 特定订单类型
  8.     BEGIN
  9.     	INSERT INTO id.dbo.sales_history (id_order)
  10.             SELECT i.ID_ORDER FROM inserted i
  11.     END

问题出现在我想要连接另一个表时。触发器停止工作

  1. ALTER TRIGGER [dbo].[InsertTrig]
  2. ON [dbo].[ORDER]
  3. AFTER INSERT 
  4. AS
  5.     IF EXISTS (SELECT * 
  6.                FROM inserted i
  7.                WHERE i.ID_TYPE = 1)   -- 特定订单类型
  8.     BEGIN
  9.         INSERT INTO id.dbo.sales_history (id_order, id_item)
  10.         	SELECT
  11.              	inserted.ID_ORDER, ORDER_DETAILS.ID_ITEM
  12.         	FROM
  13.                 inserted
  14.          	INNER JOIN
  15.                 ORDER_DETAILS ON ORDER_DETAILS.ID_ORDER = inserted.ID_ORDER
  16.     END

我还尝试过这种方式，但仍然没有效果：)

  1. ALTER TRIGGER [dbo].[InsertTrig]
  2. ON [dbo].[ORDER]
  3. AFTER INSERT 
  4. AS
  5.     IF EXISTS (SELECT * 
  6.                FROM inserted i
  7.                WHERE i.ID_TYPE = 1)   -- 特定订单类型
  8.     BEGIN
  9.     	DECLARE @xyz AS numeric(18, 0)
  11.      	SET @xyz = (SELECT inserted.ID_ORDER FROM inserted)
  13.         INSERT INTO id.dbo.sales_history (id_order, id_item)
  14.     		SELECT
  15.          		ORDER.ID_ORDER, ORDER_DETAILS.ID_ITEM
  16.      		FROM
  17.                 ORDER
  18.     		INNER JOIN
  19.                 ORDER_DETAILS ON ORDER_DETAILS.ID_ORDER = ORDER.ID_ORDER
  20. 			WHERE
  21.                 ORDER.ID_ORDER = @xyz
  22.     END

我想创建一个触发器，将在ORDER表中触发后自动填充我的销售历史记录基表。

英文:

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! SQL Server：AFTER INSERT触发器和INSERT INTO

  1. ALTER TRIGGER [dbo].[InsertTrig]
  2. ON [dbo].[ORDER]
  3. AFTER INSERT 
  4. AS
  5.     IF EXISTS (SELECT * 
  6.                FROM inserted i
  7.                WHERE i.ID_TYPE = 1)   -- specific order type
  8.     BEGIN
  9.     	INSERT INTO id.dbo.sales_history (id_order)
  10.             SELECT i.ID_ORDER FROM inserted i
  11.     END

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

  1. ALTER TRIGGER [dbo].[InsertTrig]
  2. ON [dbo].[ORDER]
  3. AFTER INSERT 
  4. AS
  5.     IF EXISTS (SELECT * 
  6.                FROM inserted i
  7.                WHERE i.ID_TYPE = 1)   -- specific order type
  8.     BEGIN
  9.         INSERT INTO id.dbo.sales_history (id_order, id_item)
  10.         	SELECT
  11.              	inserted.ID_ORDER, ORDER_DETAILS.ID_ITEM
  12.         	FROM
  13.                 inserted
  14.          	INNER JOIN
  15.                 ORDER_DETAILS ON ORDER_DETAILS.ID_ORDER = inserted.ID_ORDER
  16.     END

I also tried this way, and still nothing SQL Server：AFTER INSERT触发器和INSERT INTO

  1. ALTER TRIGGER [dbo].[InsertTrig]
  2. ON [dbo].[ORDER]
  3. AFTER INSERT 
  4. AS
  5.     IF EXISTS (SELECT * 
  6.                FROM inserted i
  7.                WHERE i.ID_TYPE = 1)   -- specific order type
  8.     BEGIN
  9.     	DECLARE @xyz AS numeric(18, 0)
  11.      	SET @xyz = (SELECT inserted.ID_ORDER FROM inserted)
  13.         INSERT INTO id.dbo.sales_history (id_order, id_item)
  14.     		SELECT
  15.          		ORDER.ID_ORDER, ORDER_DETAILS.ID_ITEM
  16.      		FROM
  17.                 ORDER
  18.     		INNER JOIN
  19.                 ORDER_DETAILS ON ORDER_DETAILS.ID_ORDER = ORDER.ID_ORDER
  20. 			WHERE
  21.                 ORDER.ID_ORDER = @xyz
  22.     END

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

答案1

得分: 1

触发器是在Order表上的，这意味着SQL Server会在你向Order表插入记录后触发它。此时，Order_Details表中的相关记录可能尚未插入，因为它们具有对Order表的外键。这就是为什么在inserted表和Order_Details表之间进行内连接会返回0行记录。

如果你想要从order_details表中获取你的sales_history，你必须在向order_details表插入记录之后填充它。

作为一个附注：InsertTrig是一个不好的命名。请注意我的回答中触发器的名称 - 它告诉你这个触发器是用于什么目的，以及在哪个表上。

英文:

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.

  1. CREATE OR ALTER TRIGGER [dbo].[OrderDetails_AfterInsert]
  2. ON [dbo].[ORDER_DETAILS]
  3. AFTER INSERT 
  4. AS
  5.     INSERT INTO id.dbo.sales_history (id_order, id_item)
  6.     SELECT
  7.         inserted.ID_ORDER, inserted.ID_ITEM
  8.     FROM
  9.         inserted
  10.     INNER JOIN
  11.         [ORDER] ON [ORDER].ID_ORDER = inserted.ID_ORDER
  12.     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.

huangapple
  • 本文由 发表于 2023年2月19日 20:46:50
  • 转载请务必保留本文链接：https://go.coder-hub.com/75500239.html
  • sql-server
  • t-sql
  • triggers
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定