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?
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.