• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

MSSQL Trigger On Same Table

Hello...

We have a table called "SY_WRKGRP", with a Primary Key on Field "WRKGRP_ID".  Within that table, there's a field called "NXT_ITEM_NO".

When a value is UPDATED on the "NXT_ITEM_NO" field, for *any* of the WRKGRP_ID's, we want/need the value of "NXT_ITEM_NO" to be updated with that same value...for all other WRKGRP_ID's.

We were having a issue with the Syntax, as it seems we're (somehow?) looping the originating WRKGRP_ID onto itself.  Please help...something's obviously amuck :-)

Thanks!...Mark
0
datatechcorp
Asked:
datatechcorp
  • 3
  • 3
1 Solution
 
Scott PletcherSenior DBACommented:
That doesn't really make sense to me.  Instead of updating every row, why not just put the NXT_ITEM_NO into a separate table?
0
 
datatechcorpAuthor Commented:
...because we cannot alter the Table Structure...we simply need "consistency" amongst the data field...once they are populated/updated (there's business logic behind all this).

I hope this helps...Thanks!...Mark
0
 
Scott PletcherSenior DBACommented:
UPDATE SY_WRKGRP
SET NXT_ITEM_NO = (
    SELECT TOP (1) NXT_ITEM_NO
    FROM inserted
    ORDER BY WRKGRP_ID DESC
    )


Not sure about the "onto itself" part ... updating the originally changed WRKGRP_ID row with the value from that row shouldn't really change the data.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
datatechcorpAuthor Commented:
Hi Scott...

It was suggested that we needed a 'NOCOUNT' instruction added in there somewhere...does that make sense?  Please let me know...Thanks!...Mark
0
 
Scott PletcherSenior DBACommented:
Yes, that does indeed make sense, and it's quite true:

CREATE TRIGGER SY_WRKGRP__TRG
ON SY_WRKGRP
AFTER UPDATE --, INSERT
AS
SET NOCOUNT ON
UPDATE SY_WRKGRP
SET NXT_ITEM_NO = (
    SELECT TOP (1) NXT_ITEM_NO
    FROM inserted
    ORDER BY WRKGRP_ID DESC
    )
GO
0
 
datatechcorpAuthor Commented:
Scott...thank you soooo much!  It's very appreciated!...Thanks!...Mark
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now