MS SQL trigger problem.

DougKampe — Tue, 11 Jan 2011 17:28:55 GMT

To start, I am a novice SQL user and I've searched far and wide for a solution for this, so calling all T-SQL gurus.

I am working on a project that requires the development of a complex set of related tables and layers. We're transitioning from flat files to a relational databases. For testing purposes, I am using MS SQL Server 2008 R2 Express edition and have a personal SDE geodatabase. So far things have been going well, except I've had a difficult time with one aspect.

I've designed a trigger on a table that when Column A/Row 1 is edited, a change occurs in column B/Row 1 when the editor saves his/her edits. This works flawlessly. However, when the editor changes data in Column A/Rows 1&2 at the same time and tries to save, an error message pops up and edits need to be stopped. I want to design a trigger that holds onto all edits performed on Column A during an editing session, and changes the appropriate rows in Column B when saved. By the way, all edits are being done in ArcMap, NOT in the RDBMS.

Any help would be greatly appreciated. Keep in mind, I am NOT a programmer or DBA, but I do have a fairly good grasp of SQL Server.

Thanks,
Doug

Re: MS SQL trigger problem, SOLVED.

DougKampe — Thu, 13 Jan 2011 20:31:40 GMT

I didn't get any replies, but I figured it out. Since I can't be the only one with difficulties designing more complicated triggers especially for T-SQL, here you are ESRI community. Any comments on syntax or alternative strategies are welcome.
__________
CREATE TRIGGER Update_Long_ListCat
ON dbo.IR_ASSESSMENTS_2010
AFTER UPDATE
AS
BEGIN

UPDATE IR_ASSESSMENTS_2010
SET CAT_LONG = CASE
   WHEN inserted.Listing_Category = '1' THEN 10
   WHEN inserted.Listing_Category = '2' THEN 20
   WHEN inserted.Listing_Category = '3' THEN 30
   WHEN inserted.Listing_Category = '4a' THEN 41
   WHEN inserted.Listing_Category = '4b' THEN 42
   WHEN inserted.Listing_Category = '4c' THEN 43
   WHEN inserted.Listing_Category = '5' THEN 50
   ELSE IR_ASSESSMENTS_2010.CAT_LONG
END
FROM inserted
WHERE IR_ASSESSMENTS_2010.ID = inserted.ID
END

Doug

topic Re: MS SQL trigger problem, SOLVED. in Data Management Questions

MS SQL trigger problem.

Re: MS SQL trigger problem, SOLVED.