SQL Server：触发如何读取插入、更新、删除的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8505924/
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 how to read value for Insert, Update, Delete
提问by Tomas
I have the trigger in one table and would like to read UserIdvalue when a row is inserted, updated or deleted. How to do that? The code below does not work, I get error on UPDATED
我在一个表中有触发器，并希望UserId在插入、更新或删除行时读取值。怎么做？下面的代码不起作用，我收到错误UPDATED
ALTER TRIGGER [dbo].[UpdateUserCreditsLeft]
ON [dbo].[Order]
AFTER INSERT,UPDATE,DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@UserId INT,
SELECT @UserId = INSERTED.UserId FROM INSERTED, DELETED
UPDATE dbo.[User] SET CreditsLeft = CreditsLeft - 1 WHERE Id = @UserId
END
回答by MatBailie
Please note that inserted, deletedmeans the same thing as inserted CROSS JOIN deletedand gives every combination of every row. I doubt this is what you want.
请注意，inserted, deleted这inserted CROSS JOIN deleted与给出每一行的每个组合的含义相同。我怀疑这就是你想要的。
Something like this may help get you started...
像这样的事情可能会帮助你开始......
SELECT
CASE WHEN inserted.primaryKey IS NULL THEN 'This is a delete'
WHEN deleted.primaryKey IS NULL THEN 'This is an insert'
ELSE 'This is an update'
END as Action,
*
FROM
inserted
FULL OUTER JOIN
deleted
ON inserted.primaryKey = deleted.primaryKey
Depending on what you want to do, you then reference the table you are interested in with inserted.userIDor deleted.userID, etc.
根据您要执行的操作，然后使用inserted.userID或deleted.userID等引用您感兴趣的表。
Finally, be aware that insertedand deletedare tablesand can (and do) contain more than one record.
最后，请注意inserted和deleted是表，并且可以（并且确实）包含多个记录。
If you insert 10 records at once, the insertedtable will contain ALL 10 records. The same applies to deletes and the deletedtable. And both tables in the case of an update.
如果一次插入 10 条记录，该inserted表将包含所有 10 条记录。这同样适用于删除和deleted表。并且在更新的情况下两个表。
EDITExamplee Trigger after OPs edit.
编辑示例 OP 编辑后触发。
ALTER TRIGGER [dbo].[UpdateUserCreditsLeft]
ON [dbo].[Order]
AFTER INSERT,UPDATE,DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE
User
SET
CreditsLeft = CASE WHEN inserted.UserID IS NULL THEN <new value for a DELETE>
WHEN deleted.UserID IS NULL THEN <new value for an INSERT>
ELSE <new value for an UPDATE>
END
FROM
User
INNER JOIN
(
inserted
FULL OUTER JOIN
deleted
ON inserted.UserID = deleted.UserID -- This assumes UserID is the PK on UpdateUserCreditsLeft
)
ON User.UserID = COALESCE(inserted.UserID, deleted.UserID)
END
If the PrimaryKey of UpdateUserCreditsLeftis something other than UserID, use that in the FULL OUTER JOIN instead.
如果的 PrimaryKeyUpdateUserCreditsLeft不是 UserID，则在 FULL OUTER JOIN 中使用它。
回答by MatBailie
There is no updateddynamic table.There is just insertedand deleted. On an UPDATEcommand, the old data is stored in the deleteddynamic table, and the new values are stored in the inserteddynamic table.
没有updated动态表。只有inserted和deleted。在UPDATE命令上，旧数据存储在deleted动态表中，新值存储在inserted动态表中。
Think of an UPDATEas a DELETE/INSERTcombination.
将 aUPDATE视为一个DELETE/INSERT组合。
回答by aF.
Here is the syntax to create a trigger:
以下是创建触发器的语法：
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
If you want to use On Update you only can do it with the IF UPDATE ( column )section. That's not possible to do what you are asking.
如果您想使用 On Update，则只能使用该IF UPDATE ( column )部分。这不可能做到你所要求的。
