search

rssidlowski / Pollution_Source_Tracking

City of Baltimore Pollution Source Tracking
0 stars 0 forks source link

Rewrite new sample feature trigger to avoid relying on ObjectID (ensure deployed and enabled in dev and prod) #153

Closed gerrykelly closed 9 years ago

gerrykelly commented 9 years ago

revise following trigger to use max existing sample ID + 1, rather than risking object Ids that are existing sample IDs after future feature class re-creation ...

USE [Pollution_Source_Tracking] GO

/\ Object: Trigger [GIS_ADMIN].[UPDATESAMPLEID] Script Date: 4/16/2015 2:44:23 PM **/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

-- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE TRIGGER [GIS_ADMIN].[UPDATESAMPLEID] ON [GIS_ADMIN].[PST_SAMPLES] AFTER INSERT AS BEGIN

if (select OBJECTID from inserted ) is not null begin update [gis_admin].[PST_SAMPLES] set [gis_admin].[PST_SAMPLES].SAMPLE_ID = (select OBJECTID from inserted ) where [gis_admin].[PST_SAMPLES].OBJECTID = (select OBJECTID from inserted );
end

begin
INSERT INTO [gis_admin].[PST_Xref_SamplesInvestigations](SAMPLE_ID, INVESTIGATION_ID) (select OBJECTID, INVESTIGATION_1_ID from inserted ); end

END

GO

gerrykelly commented 9 years ago

updated as:

USE [Pollution_Source_Tracking] GO /\ Object: Trigger [gis_admin].[UPDATESAMPLEID] Script Date: 4/16/2015 5:57:44 PM **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER TRIGGER [gis_admin].[UPDATESAMPLEID] ON [gis_admin].[PST_SAMPLES] AFTER INSERT AS BEGIN

if (select OBJECTID from inserted ) is not null

declare @newID as int

begin set @newID =(select (max(sample_id) + 1) from [gis_admin].[PST_SAMPLES] ) update [gis_admin].[PST_SAMPLES] set [gis_admin].[PST_SAMPLES].SAMPLE_ID = @newID where [gis_admin].[PST_SAMPLES].OBJECTID = (select OBJECTID from inserted );
end

begin
INSERT INTO [gis_admin].[PST_Xref_SamplesInvestigations](SAMPLE_ID, INVESTIGATION_ID) (select @newID, INVESTIGATION_1_ID from inserted ); INSERT INTO [gis_admin].A_SAMPID_LOG ([sampid], oid) (select @newID, OBJECTID from inserted ); end

END