SQL trigger with parameter

Question

I have a nodejs app with SQL Server. I want to be able to update a table for a "specific org" based on an insert and delete action. Let's say I have 2 tables as follows:

Project: projId, orgId, projName
Tasks: taskId, projId, taskName
Users: userId, orgId, userName
OrganizationStats: numberOfProjects, numberOfUsers, numberOfTasks orgId

So let's say I add a new project for an organization where orgId = 1. My insert statement from Nodejs would be:

insert into project (projId, orgId, projName) 
values (${'projId'}, ${'orgId'}, 'New Project');

I want to write a trigger in SQL Server that adds 1 to the numberOfProjects column with orgId that's passed in.

create trigger updateProjectAfterInsert 
on project 
after insert 
as 
begin 
    update OrganizationStats 
    set numprojects = numberOfProjects + 1 
    where orgId = 'THE_INSERTED_ORGID_VALUE';
end;

My problem is I don't know how to pass the ${'orgId'} to the trigger.

You also assume that the number of inserted rows is always 1 - a common mistake. But why bother doing this at all? The value can be easily determined by counting the related rows when needed and it will ALWAYS be correct. Did you forget about deletions? Can a project be moved between organizations? — SMor
– SMor, Commented Nov 17, 2020 at 20:16
Personally, I recommend against storing values which can be calculated by an aggregate. If you need such information easily accessible, you're better off making a VIEW with the value in there, in my opinion. — Thom A
– Thom A ♦, Commented Nov 17, 2020 at 20:19

Thom A · Accepted Answer · 2020-11-17 20:30:23Z

1

I'm going to expand on my comment here:

Personally, I recommend against storing values which can be calculated by an aggregate. If you need such information easily accessible, you're better off making a VIEW with the value in there, in my opinion.

What I mean by this is that NumProjects has "no right" being in the table OrganizationStats, instead it should be calculated at the time the information is needed. You can't use an aggregate function in a computed column's definition without a scalar function, and those can be quite slow. Instead I recommend creating a VIEW (or if you prefer table value function) to give you the information from the table:

CREATE VIEW dbo.vw_OrganisationStats AS

    SELECT {Columns from OrganizationStats},
           P.Projects AS NumProjects
    FROM dbo.OrganizationStats OS
         CROSS APPLY (SELECT COUNT(*) AS Projects
                      FROM dbo.Projects P
                      WHERE P.OrgID = OS.OrgID) P;

I use a CROSS APPLY with a subquery, as then you don't need a huge GROUP BY at the end.

answered Nov 17, 2020 at 20:30

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sean D Over a year ago

So given the above do I need to create 2 more views to calculate the COUNT for numberOfUsers and numberOfTasks as well or can the other two columns get calculated within the same view?

Thom A Over a year ago

You would add the to the same view.

Sean D Over a year ago

so given the table structure in the original question, I am not sure they'd all get into one view query.. @Lamu Can you please help with a sample code?

Thom A Over a year ago

"Can you please help with a sample code?" There's one in my question, @SeanD . I don't know your design, or details of requirements that aren't covered in the question, but you can certainly have multiple aggregate functions in a single VIEW. If you don't know how to implement the above, you're better off asking a new question, as you end up invalidating both answers here if you change this question.

pmbAustin · Accepted Answer · 2020-11-17 20:19:41Z

0

I think what you want this something like this:

  CREATE TRIGGER updateProjectAfterInsert 
  ON Project 
  AFTER INSERT 
  AS 
  BEGIN 
      UPDATE OrganizationStats 
         SET NumProjects = NumProjects + 1 
       WHERE OrgId IN (SELECT OrgId FROM inserted);
  END;

Also note, Triggers must always assume multiple rows. It's possible to insert multiple rows, update multiple rows, and delete multiple rows. The "inserted" and "deleted" collections contain the data needed ("inserted" contains the rows being inserted, "deleted" contains the rows being deleted, and on an update "inserted" contains the values after the update, and "deleted" contains the values before the update).

answered Nov 17, 2020 at 20:19

pmbAustin

4,0101 gold badge27 silver badges35 bronze badges

5 Comments

Thom A Over a year ago

Note that if 2 projects for the same organisation are inserted at the same time, this will work, but not as the OP expects.

pmbAustin Over a year ago

Hrm, yes, if that's a possible case, then the "+1" should be computed via a correlated subquery most likely.

Sean D Over a year ago

orgId is passed in. basically the same logic needs to work for deleted meaning i need to take subtract one from the column.

Thom A Over a year ago

AFTER INSERT triggers don't occur after a DELETE @SeanD .

pmbAustin Over a year ago

Add another AFTER DELETE trigger to handle deletes, @SeanD

Collectives™ on Stack Overflow

SQL trigger with parameter

2 Answers 2

4 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related