topic Re: Trigger on A-table (versioned gdb) wont update (SQL server) in Geodatabase Questions

Trigger on A-table (versioned gdb) wont update (SQL server)

WendyBerelson — Wed, 29 May 2019 18:36:19 GMT

I have a versioned GDB (SQL server) and set a trigger on A-table that is supposed to update a second A-table when a change (insert, update) is made. I can see that the change is in the A-table (a225) where the trigger is set, but it doesnt seem to fire and make the change to a229. I am editing on DEFAULT. Note I just added in "INSERT" to the AFTER UPDATE line. I am actually altering code that I didnt write that is supposed to work on a non-versioned gdb but I need to replicate the database so had to version it and w/replication cant use move edits to base. So now have to rewrite the code to hit the a-tables. I dont think I'm supposed to set triggers on the _evw? This is all new to me so I appreciate any help/tips.

USE [UWADMINGIS_INVISION]
GO
/****** Object: Trigger [dbo].[Building_Update_SHORTNAME_trg] Script Date: 5/29/2019 11:09:41 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[Building_Update_SHORTNAME_trg]
ON [dbo].[a225]
AFTER INSERT, UPDATE
AS
IF UPDATE (SHORTNAME)
BEGIN

UPDATE [dbo].[a229]
SET UW_Building_Name = BUILDING.SHORTNAME
FROM [dbo].[a229] f
LEFT OUTER JOIN (SELECT DISTINCT LOCATION_CODE FROM inserted) i ON f.Location_Code = i.LOCATION_CODE
LEFT OUTER JOIN (
SELECT LOCATION_CODE,
SHORTNAME
FROM a225
) BUILDING on BUILDING.LOCATION_CODE = f.Location_Code
WHERE i.Location_code IS NOT NULL

END

Re: Trigger on A-table (versioned gdb) wont update (SQL server)

BirajaNayak — Wed, 29 May 2019 23:50:25 GMT

Hi Wendy,

Modifying A table using SQL will corrupt the geodatabase and it is not recommended workflow.

If you want to modify the attributes using SQL then please use versioned view for that feature class in stead of A table on Default version or any other version you wanted to.

Thanks,

Biraja

Re: Trigger on A-table (versioned gdb) wont update (SQL server)

WendyBerelson — Thu, 30 May 2019 15:54:29 GMT

Hi Biraja, I know this is not a recommended work flow I reviewed https://community.esri.com/thread/174497#comment-597453. Are you saying I should set the triggers on the _evw? I thought you couldnt set triggers there other than the native ones (e.g. v229_delete, v229_insert and v229_update) created when you version? So would I write the code to set the trigger as follows: ON [dbo].[building_evw] which is the _evw table (a225 is the adds table) for the Building feature class?

Thanks, Wendy

Re: Trigger on A-table (versioned gdb) wont update (SQL server)

BirajaNayak — Fri, 31 May 2019 23:38:34 GMT

Hi Wendy,

When you create enterprise geodatabase, default triggers are created with it and those are maintained by Esri application.

You can create your own triggers and validate or test in development environment first for any issues with your workflow caused by this triggers and you maintain it. As long as you are maintaining it on your own and not breaking your geodatabase workflow, you should be good with the additional triggers.

Thanks,

Biraja