当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Conditional after trigger

问题描述:

I am required to create an after trigger. (STRICTLY AN AFTER TRIGGER ONLY). I think I am on the right track, but can't seem to get it to execute properly. It seems to always execute the trigger and show the error message on all insert statements instead of just when there is a duplicate. This is what I have;

 CREATE TRIGGER Copies

ON table

AFTER INSERT

AS

Declare @number int

Set @number = (Select Count(*) from inserted)

If @number >0

(

SELECT *

FROM table O

JOIN inserted AS i

ON t.q_id = i.q_id

WHERE O.username = i.username

AND t.q_id = i.q_id

)

BEGIN

RAISERROR ('Already answered', 16, 1);

ROLLBACK TRANSACTION;

RETURN

END;

GO

Ps.

Please don't reply with for triggers. Thanks :)

网友答案:
If (NOT EXISTS(..code to test for existance ))
 BEGIN    
            RAISERROR ('Already answered', 16, 1);
            ROLLBACK TRANSACTION;
            RETURN 
 END
网友答案:

try this new trigger using count

CREATE TRIGGER Copies
ON table
AFTER INSERT

AS
DECLARE @Counter int
SELECT @Counter = COUNT(*)
FROM table T
JOIN inserted AS i 
    ON T.username = i.username 
    AND t.q_id = i.q_id
GROUP BY T.username, t.q_id 

If (@Counter > 1) 
BEGIN
        RAISERROR ('Already answered', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN 
END;
GO
分享给朋友:
您可能感兴趣的文章:
随机阅读: