从另一个表TRIGGER中的更新行向表中插入行

I have 2 regular tables (Table1 and Table2) and a temporary table (TempTable). I'm updating the rows in Table1 from the rows in Table2:

我有2个常规表(Table1和Table2)和一个临时表(TempTable)。我正在从Table2中的行更新Table1中的行:

update t1
set t1.[Table1] = t2.[Price]
FROM
[Table1] t1
JOIN [Table2] t2 on t1.[KAT_ID] = t2.            
[KAT_ID]
where t1.[Price] != t2.[Price]

I want to create a trigger on Table1 that inserts ALL OF THE UPDATED ROWS into TempTable. This is what I have so far:

我想在Table1上创建一个触发器,将所有更新的行插入TempTable。这是我到目前为止:

CREATE TRIGGER [dbo].[TABLE1_TRIGGER] ON [dbo].[Table1]
AFTER UPDATE
AS 

IF OBJECT_ID('tempdb..##TempTable') IS NOT NULL DROP TABLE 
##TempTable
CREATE TABLE ##TempTable(
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[KAT_ID] [varchar](50) NULL,
[Price] [money] NULL)

I have no idea how to go about inserting the updated rows into TempTable.

我不知道如何将更新的行插入TempTable。

2 个解决方案

#1

You would be far better putting your data in a staging table of some kind, rather than a global temporary table. As I said in the comment, they perform terribly.

将数据放在某种临时表中,而不是全局临时表,会更好。正如我在评论中所说,他们的表现非常糟糕。

Firstly, we'll start with some kind of staging table. I don't know what your set up is, so we'll put in a schema for it as well:

首先,我们将从某种临时表开始。我不知道你的设置是什么,所以我们也会为它设置一个模式:

CREATE SCHEMA staging;
CREATE TABLE staging.[RelevantNameHere] (ID bigint IDENTITY(1,1),
                                         KAT_ID varchar(50),
                                         Price money);
GO

Then we can move onto the trigger:

然后我们可以进入触发器:

CREATE TRIGGER [dbo].[TABLE1_TRIGGER] ON [dbo].[Table1]
AFTER UPDATE
AS 
    TRUNCATE TABLE staging.[RelevantNameHere];

    INSERT INTO [RelevantNameHere] (KAT_ID, Price)
    SELECT i.KAT_ID, i.Price
    --In a trigger such as this, there are 2 additional objects you can refer to
    --[inserted] and [deleted]. The names are a give away as to what they contain.
    FROM inserted i; 
GO

Of course, this is working on the very limited information we have here. I very much doubt that the problem you have described is actually the problem you have. You would probably be better explaining your full goal and aims are in your post; there could well be a far better answer out there than the above.

当然,这是我们在这里提供的非常有限的信息。我非常怀疑你所描述的问题实际上是你遇到的问题。您可能会更好地解释您的完整目标和目标在您的帖子中;那里的答案可能远远超过上述答案。

#2

I guess below is the query you are looking for .

我想以下是您正在寻找的查询。

CREATE TRIGGER [dbo].[TABLE1_TRIGGER] ON [dbo].[Table1]
AFTER UPDATE
AS 

IF OBJECT_ID('tempdb..##TempTable') IS NOT NULL 
DROP TABLE ##TempTable

CREATE TABLE ##TempTable(
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[KAT_ID] [varchar](50) NULL,
[Price] [money] NULL)

INSERT INTO ##TempTable (KAT_ID,Price)

SELECT KAT_ID,Price FROM [INSERTED]

#1