更新时间:2023-02-05 15:30:05
请注意,插入,删除
的含义与插入的交叉连接删除
相同,并给出每种组合每行。我怀疑这就是您想要的。
Please note that inserted, deleted
means the same thing as inserted CROSS JOIN deleted
and gives every combination of every row. I doubt this is what you want.
像这样的事情可能有助于您入门...
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
然后根据您要执行的操作,使用 inserted.userID
或 deleted.userID
来引用您感兴趣的表。 >等。
Depending on what you want to do, you then reference the table you are interested in with inserted.userID
or deleted.userID
, etc.
最后,请注意已插入
和删除的
是表,并且可以(并且确实)包含多个记录。
Finally, be aware that inserted
and deleted
are tables and can (and do) contain more than one record.
如果一次插入10条记录,则插入的
表将包含所有10条记录。删除和已删除
表也是如此。以及两个表都进行更新。
If you insert 10 records at once, the inserted
table will contain ALL 10 records. The same applies to deletes and the deleted
table. And both tables in the case of an update.
编辑示例在OP编辑后触发。
EDIT Examplee Trigger after OPs edit.
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
如果主键为 UpdateUserCreditsLeft
不同于UserID,请在FULL OUTER JOIN中使用。
If the PrimaryKey of UpdateUserCreditsLeft
is something other than UserID, use that in the FULL OUTER JOIN instead.