sql触发器停止行中的重复项 [英] sql trigger to stop duplicates across row

问题描述

我有一个包含多个记录的表：

I have a table with multiple records:

• 用户名（例如'TOM'）


• Question_ID（例如'q002'）


• 答案（例如'D'）

i想要创建一个触发器，以便没人可以两次提交相同问题的答案。
仅必须是触发器。

i want to create a trigger so that no one can submit an answer to the same question twice. It has to be a trigger only.

CREATE TRIGGER trigger_Check_Duplicates

ON submit_Answer

FOR INSERT
AS

IF SELECT???

PRINT 'duplicate'

raiserror('cant submit answer to same question twice')

ROLLBACK

End

推荐答案

创建触发器

CREATE TRIGGER dbo.uniqueUserQuestion 
ON dbo.submit_Answer
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON
    IF EXISTS 
    (
        SELECT 1 
        FROM dbo.submit_Answer T 
        INNER JOIN INSERTED I 
        ON T.user_name = I.user_name 
            AND T.question_id = I.question_id
    )
    BEGIN
        -- Do dupe handling here
        PRINT 'duplicate'
        raiserror('cant submit answer to same question twice')
        return
    END

    -- actually add it in
    INSERT INTO
        dbo.submit_Answer
    SELECT
        *
    FROM
        INSERTED I
END
GO

这篇关于sql触发器停止行中的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！