如何从具有复合主键的表中删除

Question

我有一个带有复合主键的表Role。我必须从中删除值，并检查它们是否等于某个值(在触发器中(。我该怎么做？

表格代码：

CREATE TABLE Role
(
roleName VARCHAR(50),
groupId  INT,
CONSTRAINT FK_Group FOREIGN KEY (groupId) REFERENCES [Group] (groupId),
PRIMARY KEY (roleName, groupId),
canBan   TINYINT DEFAULT (0) /* 0 or 1 */
)

未经批准的触发器代码

CREATE TRIGGER DeleteRoleTrigger
ON [Role]
INSTEAD OF DELETE
AS
BEGIN
IF 'everyone' IN (SELECT roleName FROM deleted)
BEGIN
THROW 50002, 'Cannot delete role "everyone"! Nothing is deleted', 1
--   id severity state
END
ELSE
BEGIN
-- todo
DELETE
FROM Role
WHERE (EXISTS(SELECT groupId, roleName FROM deleted))
PRINT N'Роль удалена'
END
END
GO

Answer 1

问题可能出现在WHERE (EXISTS(SELECT groupId, roleName FROM deleted))行。我建议在delete语句中使用内部联接，如下所示。

DELETE r
FROM Role r
INNER JOIN deleted d
ON r.groupId = d.groupId AND r.roleName = d.roleName

Answer 2

您可能需要更改此触发器的ELSE部分

DELETE r
FROM [Role] r join deleted d
on (r.groupId = d.groupId and r.roleName =d.roleName)

或

DELETE r
FROM [Role] r WHERE EXISTS(SELECT 1 FROM deleted d
WHERE d.groupId=r.groupId and d.roleName=r.roleName)