-1

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 at 17:40
  • Can I use a Select and a if else sentence? , could u post a example ? – E.Rawrdríguez.Ophanim Jul 8 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 at 19:16
  • could you please write down the query I'm new into triggers – E.Rawrdríguez.Ophanim Jul 8 at 19:29
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:

CREATE TRIGGER NewUser
ON Users 
AFTER INSERT AS
    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)
SELECT u.userID
FROM Users u
LEFT JOIN User_ChargeCount uc
    ON u.userID = uc.userID
WHERE uc.userID IS NULL

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

CREATE TRIGGER UserCharges_Insert
On UserCharges
AFTER INSERT AS
    UPDATE User_ChargeCount
    SET chargeCount += ct
    FROM User_ChargeCount u
    INNER JOIN (
        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
AFTER UPDATE
AS
BEGIN
    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,
        uct.BadgeID,
        GetDate() -- assumes a Date Badge Awarded field exists
    FROM CCCheck
    INNER JOIN UserChargeThresholds uct
        ON NewCC = uct.ChargeCount
    WHERE NewCC > OldCC
END

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, privacy policy and cookie policy

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