Styjun

What is göddertrobbe?

Why was the battle set up *outside* Winterfell?

Airbnb - host wants to reduce rooms, can we get refund?

What is the temperature of the black night sky?

Why is B♯ higher than C♭ in 31-ET?

How can I close a gap between my fence and my neighbor's that's on his side of the property line?

What actually is the vector of angular momentum?

Upside-Down Pyramid Addition...REVERSED!

What does a yield inside a yield do?

SFINAE works with deduction but fails with substitution

What is the most remote airport from the center of the city it supposedly serves?

Why is `abs()` implemented differently?

Number of seconds in 6 weeks

How could a planet have most of its water in the atmosphere?

Pressure to defend the relevance of one's area of mathematics

Is there a legal ground for stripping the UK of its UN Veto if Scotland and/or N.Ireland split from the UK?

Manager is threatning to grade me poorly if I don't complete the project

What is the minimal installation possible in order to run a .jar Java file?

Has any spacecraft ever had the ability to directly communicate with civilian air traffic control?

Would glacier 'trees' be plausible?

What algorithms are considered reinforcement learning algorithms?

In Avengers 1, why does Thanos need Loki?

Why is Arya visibly scared in the library in S8E3?

In Endgame, why were these characters still around?

SQL Server : AFTER INSERT trigger not working properly, even after making sure FIRE_TRIGGERS was set in SSIS package

Solutions for INSERT OR UPDATE on SQL ServerInsert Update trigger how to determine if insert or updateSelect n random rows from SQL Server tableSET NOCOUNT ON usageSELECT INTO a table variable in T-SQLSQL Server trigger - SET NORESULTS ON?SQL Server Trigger not triggered after insert and updateHow to fire trigger after every insert in sql server table automatically?Insert trigger in SQL ServerSQL Server trigger failing for row inserts in quick succession

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

Unsure why this is not working properly. Let me give some context of what I want to happen.

I am inserting a large number of rows into the OpenCase table (about 35 K). This data is from the last 3 months. The Resolved Case table is already current and up to date.

Some of the open cases have since been resolved, so I want it to check each row from the inserted table and check to see if that SUB_QUERY_ID is in the Resolved Case table. If it is, I want to update the RESOLVED_ON date in the Open Case table with the SUB_QUERY_CLOSED_ON date in the Resolved Case table for this case.

An example below:

From Resolved Case table:

SUB_QUERY_ID SUB_QUERY_CLOSED_ON
----------------------------------
US12332-1 1/28/2019

From Open Case table:

SUB_QUERY_ID RESOLVED_ON
-------------------------------------
US12332-1 (The trigger should get the SUB_QUERY_CLOSED_ON date from 
 the Resolved Cases Table and update it here)

From the example I have seen, this should be working, but the RESOLVED_ON dates are all NULL, even when I manually check a few of the Open Case SUB_QUERY_ID's and see they are in the Resolved Case table.

ALTER TRIGGER [FindResolvedCaseDate] 
ON [OpenCases]
AFTER INSERT, UPDATE
AS 
BEGIN
 IF @@ROWCOUNT = 0 
 RETURN

 SET NOCOUNT ON;

 DECLARE @SUB_QUERY_ID VARCHAR(150)

 SELECT @SUB_QUERY_ID = SUB_QUERY_ID 
 FROM INSERTED

 UPDATE OpenCases 
 SET RESOLVED_ON = (SELECT TOP 1 SUB_QUERY_CLOSED_ON 
 FROM ResolvedCases 
 WHERE SUB_QUERY_ID = @SUB_QUERY_ID 
 ORDER BY SUB_QUERY_CLOSED_ON DESC)
END

sql-server triggers

share|improve this question

edited Mar 22 at 21:25

marc_s

587k13011281274

asked Mar 22 at 21:03

MattEMattE

360517

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  Your trigger has MAJOR flaw in that you seem to assume it'll be called once per row - that is not the case. The trigger will fire once per statement, so if your INSERT that causes this trigger to fire inserts 25 rows, you'll get the trigger fired once and the Inserted pseudo table will contain 25 rows. Which of those 25 rows will your code select from Inserted? It's non-deterministic, you'll get one arbitrary row and you will be ignoring all other rows. You need to rewrite your trigger to take this into account!
  
  – marc_s
  Mar 22 at 21:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It is not safe to rely on @@rowcount in a trigger. Why? In a merge situation, this will be set to the total number of rows affected by the merge statement (inserted, updated, deleted). This slow loading link to web archive explains.
  
  – SMor
  Mar 22 at 22:52
  

  
  
  
  

add a comment | 

0

Unsure why this is not working properly. Let me give some context of what I want to happen.

I am inserting a large number of rows into the OpenCase table (about 35 K). This data is from the last 3 months. The Resolved Case table is already current and up to date.

Some of the open cases have since been resolved, so I want it to check each row from the inserted table and check to see if that SUB_QUERY_ID is in the Resolved Case table. If it is, I want to update the RESOLVED_ON date in the Open Case table with the SUB_QUERY_CLOSED_ON date in the Resolved Case table for this case.

An example below:

From Resolved Case table:

SUB_QUERY_ID SUB_QUERY_CLOSED_ON
----------------------------------
US12332-1 1/28/2019

From Open Case table:

SUB_QUERY_ID RESOLVED_ON
-------------------------------------
US12332-1 (The trigger should get the SUB_QUERY_CLOSED_ON date from 
 the Resolved Cases Table and update it here)

From the example I have seen, this should be working, but the RESOLVED_ON dates are all NULL, even when I manually check a few of the Open Case SUB_QUERY_ID's and see they are in the Resolved Case table.

ALTER TRIGGER [FindResolvedCaseDate] 
ON [OpenCases]
AFTER INSERT, UPDATE
AS 
BEGIN
 IF @@ROWCOUNT = 0 
 RETURN

 SET NOCOUNT ON;

 DECLARE @SUB_QUERY_ID VARCHAR(150)

 SELECT @SUB_QUERY_ID = SUB_QUERY_ID 
 FROM INSERTED

 UPDATE OpenCases 
 SET RESOLVED_ON = (SELECT TOP 1 SUB_QUERY_CLOSED_ON 
 FROM ResolvedCases 
 WHERE SUB_QUERY_ID = @SUB_QUERY_ID 
 ORDER BY SUB_QUERY_CLOSED_ON DESC)
END

sql-server triggers

share|improve this question

edited Mar 22 at 21:25

marc_s

587k13011281274

asked Mar 22 at 21:03

MattEMattE

360517

• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  Your trigger has MAJOR flaw in that you seem to assume it'll be called once per row - that is not the case. The trigger will fire once per statement, so if your INSERT that causes this trigger to fire inserts 25 rows, you'll get the trigger fired once and the Inserted pseudo table will contain 25 rows. Which of those 25 rows will your code select from Inserted? It's non-deterministic, you'll get one arbitrary row and you will be ignoring all other rows. You need to rewrite your trigger to take this into account!
  
  – marc_s
  Mar 22 at 21:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It is not safe to rely on @@rowcount in a trigger. Why? In a merge situation, this will be set to the total number of rows affected by the merge statement (inserted, updated, deleted). This slow loading link to web archive explains.
  
  – SMor
  Mar 22 at 22:52
  

  
  
  
  

add a comment | 

Unsure why this is not working properly. Let me give some context of what I want to happen.

I am inserting a large number of rows into the OpenCase table (about 35 K). This data is from the last 3 months. The Resolved Case table is already current and up to date.

Some of the open cases have since been resolved, so I want it to check each row from the inserted table and check to see if that SUB_QUERY_ID is in the Resolved Case table. If it is, I want to update the RESOLVED_ON date in the Open Case table with the SUB_QUERY_CLOSED_ON date in the Resolved Case table for this case.

An example below:

From Resolved Case table:

SUB_QUERY_ID SUB_QUERY_CLOSED_ON
----------------------------------
US12332-1 1/28/2019

From Open Case table:

SUB_QUERY_ID RESOLVED_ON
-------------------------------------
US12332-1 (The trigger should get the SUB_QUERY_CLOSED_ON date from 
 the Resolved Cases Table and update it here)

From the example I have seen, this should be working, but the RESOLVED_ON dates are all NULL, even when I manually check a few of the Open Case SUB_QUERY_ID's and see they are in the Resolved Case table.

ALTER TRIGGER [FindResolvedCaseDate] 
ON [OpenCases]
AFTER INSERT, UPDATE
AS 
BEGIN
 IF @@ROWCOUNT = 0 
 RETURN

 SET NOCOUNT ON;

 DECLARE @SUB_QUERY_ID VARCHAR(150)

 SELECT @SUB_QUERY_ID = SUB_QUERY_ID 
 FROM INSERTED

 UPDATE OpenCases 
 SET RESOLVED_ON = (SELECT TOP 1 SUB_QUERY_CLOSED_ON 
 FROM ResolvedCases 
 WHERE SUB_QUERY_ID = @SUB_QUERY_ID 
 ORDER BY SUB_QUERY_CLOSED_ON DESC)
END

sql-server triggers

share|improve this question

edited Mar 22 at 21:25

marc_s

587k13011281274

asked Mar 22 at 21:03

MattEMattE

360517

SUB_QUERY_ID SUB_QUERY_CLOSED_ON
----------------------------------
US12332-1 1/28/2019

SUB_QUERY_ID RESOLVED_ON
-------------------------------------
US12332-1 (The trigger should get the SUB_QUERY_CLOSED_ON date from 
 the Resolved Cases Table and update it here)

ALTER TRIGGER [FindResolvedCaseDate] 
ON [OpenCases]
AFTER INSERT, UPDATE
AS 
BEGIN
 IF @@ROWCOUNT = 0 
 RETURN

 SET NOCOUNT ON;

 DECLARE @SUB_QUERY_ID VARCHAR(150)

 SELECT @SUB_QUERY_ID = SUB_QUERY_ID 
 FROM INSERTED

 UPDATE OpenCases 
 SET RESOLVED_ON = (SELECT TOP 1 SUB_QUERY_CLOSED_ON 
 FROM ResolvedCases 
 WHERE SUB_QUERY_ID = @SUB_QUERY_ID 
 ORDER BY SUB_QUERY_CLOSED_ON DESC)
END

sql-server triggers

sql-server triggers

share|improve this question

edited Mar 22 at 21:25

marc_s

587k13011281274

asked Mar 22 at 21:03

MattEMattE

360517

share|improve this question

edited Mar 22 at 21:25

marc_s

587k13011281274

asked Mar 22 at 21:03

MattEMattE

360517

edited Mar 22 at 21:25

marc_s

587k13011281274

edited Mar 22 at 21:25

marc_s

587k13011281274

asked Mar 22 at 21:03

MattEMattE

360517

asked Mar 22 at 21:03

MattEMattE

360517