CODEWITHSUNDEEP
sql-servertriggersinsert-into
Szilárd Jakab
Szilárd Jakab

Reputation: 49

How can I stop the execution of a trigger from inside itself?

Here is the code. It is a trigger that is executed after an insert

 ALTER TRIGGER [dbo].[GameStacker]
 ON [dbo].[CDKeyUser]
 AFTER INSERT AS 
 BEGIN
    INSERT INTO User_Achivement
            ( User_ID ,
            Achivement_ID,
            Date 
            )
    VALUES  ((SELECT User_ID from inserted),
            (SELECT 
                CASE
                    WHEN COUNT(cd.User_ID)=1 THEN   1
                    WHEN COUNT(cd.User_ID)=5 THEN   2
                    WHEN COUNT(cd.User_ID)=10 THEN  3
                    WHEN COUNT(cd.User_ID)=15 THEN  4
                END
            FROM CDKeyUser cd
            WHERE cd.User_ID=(SELECT User_ID from inserted)
            ),                  
            GETDATE()
            )
 END

So my problem is that this trigger executes after every insert.( I know that is how it is supposed to work. ) But, if WHEN COUNT(cd.User_ID) has a value other than the values specified here, it should not execute,or just break the execution. If I add an else statement after WHEN COUNT(cd.User_ID)=15 THEN 4 I can't include any code there like raiseerror because it is translated into the value of Achivement_ID and it gives me an error. So basically i have to break the execution of the insert statement.

I would really appreciate if someone could help me out with this

Upvotes: 2

Views: 2919

Answers (3)

Dudi Konfino
Dudi Konfino

Reputation: 1136

ALTER TRIGGER [dbo].[GameStacker]
 ON [dbo].[CDKeyUser]
 AFTER INSERT AS 
 BEGIN
 if (selet COUNT(User_ID) from CDKeyUser)<=15
 begin
 'your code'
 END
 end

Upvotes: 0

artm
artm

Reputation: 8584

Try:

if (select count(cd1.User_ID) 
    FROM CDKeyUser cd1
    WHERE cd1.User_ID=(SELECT User_ID from inserted)) IN (1, 5, 10, 15)
begin
    --your insert statement
end

Complete code:

ALTER TRIGGER [dbo].[GameStacker]
 ON [dbo].[CDKeyUser]
 AFTER INSERT AS 
 BEGIN
 if (select count(cd1.User_ID) 
    FROM CDKeyUser cd1
    WHERE cd1.User_ID=(SELECT User_ID from inserted)) IN (1, 5, 10, 15)
 begin
    INSERT INTO User_Achivement
            ( User_ID ,
            Achivement_ID,
            Date 
            )
    VALUES  ((SELECT User_ID from inserted),
            (SELECT 
                CASE
                    WHEN COUNT(cd.User_ID)=1 THEN   1
                    WHEN COUNT(cd.User_ID)=5 THEN   2
                    WHEN COUNT(cd.User_ID)=10 THEN  3
                    WHEN COUNT(cd.User_ID)=15 THEN  4
                END
            FROM CDKeyUser cd
            WHERE cd.User_ID=(SELECT User_ID from inserted)
            ),                  
            GETDATE()
            )
  end
  END

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

Try something like this.

IF EXISTS (SELECT Count(A.User_ID) cnt
           FROM   CDKeyUser A
                  JOIN inserted B
                    ON a.User_ID = b.User_ID
           HAVING Count(A.User_ID) IN ( 1, 5, 10, 15 ))
  BEGIN
      INSERT INTO User_Achivement
                  (User_ID,Achivement_ID,Date)
      VALUES      ((SELECT User_ID
                    FROM   inserted),(SELECT CASE
                                               WHEN Count(cd.User_ID) = 1 THEN 1
                                               WHEN Count(cd.User_ID) = 5 THEN 2
                                               WHEN Count(cd.User_ID) = 10 THEN 3
                                               WHEN Count(cd.User_ID) = 15 THEN 4
                                             END
                                      FROM   CDKeyUser cd
                                      WHERE  cd.User_ID = (SELECT User_ID
                                                           FROM   inserted)),Getdate() )
  END

Upvotes: 1

Related Questions