Sql Server 触发器将新行中的值插入到另一个表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2247679/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql Server trigger insert values from new row into another table
提问by jim
I have a site using the asp.net membership schema. I'd like to set up a trigger on the aspnet_users table that inserted the user_id and the user_name of the new row into another table.
我有一个使用 asp.net 成员资格架构的站点。我想在 aspnet_users 表上设置一个触发器，将新行的 user_id 和 user_name 插入到另一个表中。
How do I go about getting the values from the last insert?
我如何从最后一次插入中获取值？
I can select by the last date_created but that seems smelly. Is there a better way?
我可以按最后一个 date_created 进行选择，但这看起来很臭。有没有更好的办法？
回答by KM.
try this for sql server
为 sql server 试试这个
CREATE TRIGGER yourNewTrigger ON yourSourcetable
FOR INSERT
AS
INSERT INTO yourDestinationTable
(col1, col2 , col3, user_id, user_name)
SELECT
'a' , default , null, user_id, user_name
FROM inserted
go
回答by Oded
You use an insert trigger - inside the trigger, inserted row items will be exposed as a logical table INSERTED, which has the same column layout as the table the trigger is defined on.
您使用插入触发器 - 在触发器内部，插入的行项目将作为逻辑表公开INSERTED，该表与定义触发器的表具有相同的列布局。
Delete triggers have access to a similar logical table called DELETED.
删除触发器可以访问一个类似的名为 的逻辑表DELETED。
Update triggers have access to both an INSERTEDtable that contains the updated values and a DELETEDtable that contains the values to be updated.
更新触发器可以访问INSERTED包含更新值的DELETED表和包含要更新值的表。
回答by HLGEM
In a SQL Server trigger you have available two psdeuotables called inserted and deleted. These contain the old and new values of the record.
在 SQL Server 触发器中，您有两个可用的 psdeuotables，称为插入和删除。这些包含记录的旧值和新值。
So within the trigger (you can look up the create trigger parts easily) you would do something like this:
因此，在触发器中（您可以轻松查找创建触发器部分），您将执行以下操作：
Insert table2 (user_id, user_name)
select user_id, user_name from inserted i
left join table2 t on i.user_id = t.userid
where t.user_id is null
When writing triggers remember they act once on the whole batch of information, they do not process row-by-row. So account for multiple row inserts in your code.
编写触发器时，请记住它们对整批信息执行一次，它们不会逐行处理。因此，请考虑在您的代码中插入多行。
回答by Teja Kantamneni
回答by cmsjr
When you are in the context of a trigger you have access to the logical table INSERTED which contains all the rows that have just been inserted to the table. You can build your insert to the other table based on a select from Inserted.
当您处于触发器的上下文中时，您可以访问逻辑表 INSERTED，其中包含刚刚插入到表中的所有行。您可以根据 Inserted 中的选择构建对另一个表的插入。
回答by Adil iqbal
Create
trigger `[dbo].[mytrigger]` on `[dbo].[Patients]` after update , insert as
begin
--Sql logic
print 'Hello world'
end
