Hi I 've a application runnning with nodejs and SQL server, What I need is that every time I Insert a record into the ChargesTable I need to check, how may charges does my user have made, and every 5 charges Insert data into an other table BadgesTable, i also need to do this every 10,15,20,50,100 charges. My question is if I can do this with a Trigger and how it would be, or should i use a Storage Procedure?

  • 1
    Trigger is enough. After Insert.
    – Akina
    Jul 8, 2019 at 17:40
  • Can I use a Select and a if else sentence? , could u post a example ? Jul 8, 2019 at 19:02
  • 1
    SELECT - yes, of course. IF - not needed, WHERE (SELECT count(*) FROM ...) IN (5,10,15,20,50,100) is enough.
    – Akina
    Jul 8, 2019 at 19:16
  • could you please write down the query I'm new into triggers Jul 8, 2019 at 19:29

1 Answer 1


This would probably be best implemented through the use of a trigger, and a separate table to keep track of charges, something like this.

First, create the separate table:

CREATE TABLE User_ChargeCount (
    userID bigint NOT NULL PRIMARY KEY,
    chargeCount int DEFAULT 0 NOT NULL

Now, create a trigger on the Users table to automatically add a row each time a new user is added:

ON Users 
    INSERT INTO User_ChargeCount (userId)
    SELECT userID
    FROM inserted

If you already have users in the table, add their records also:

INSERT INTO User_ChargeCount (userId)
FROM Users u
LEFT JOIN User_ChargeCount uc
    ON u.userID = uc.userID

Finally, add the trigger on your Charges table to log the new additions:

CREATE TRIGGER UserCharges_Insert
On UserCharges
    UPDATE User_ChargeCount
    SET chargeCount += ct
    FROM User_ChargeCount u
        Select userId, 
            Count(*) as ct
        FROM inserted
        GROUP BY userId
        ) i
        ON i.userId = u.userID

At this point, if you need to retroactively populate the count, create the code to do that.

Finally, you will want to automate the reactions to the addition of a charge through a trigger on the User_ChargeCount table, like this. First, have a table containing the Charge count thresholds and the related Badge information:

CREATE TABLE UserChargeThresholds (
    ChargeCount int not null PRIMARY KEY,
    BadgeID int not null

Then add your trigger for finding the badges that are needed:

CREATE TRIGGER UserChargeThresholdCheck
ON User_ChargeCount
    WITH CCCheck as (
        SELECT i.UserId,
            (Select MAX(ChargeCount)
             FROM UserChargeThresholds t
             WHERE t.ChargeCount <= i.chargeCount
             ) as NewCC,
            (Select MAX(ChargeCount)
             FROM UserChargeThresholds t
             WHERE t.ChargeCount <= d.chargeCount
             ) as OldCC
             FROM inserted i
             INNER JOIN deleted d
                ON i.userID = d.userID
    INSERT INTO User_Badges
    SELECT userId,
        GetDate() -- assumes a Date Badge Awarded field exists
    FROM CCCheck
    INNER JOIN UserChargeThresholds uct
        ON NewCC = uct.ChargeCount
    WHERE NewCC > OldCC

Now, all of the normal updates are automated. You will have to decide how you want to handle reversed charges, deleted charges, zero-charge rows, etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.