Scribd
Upload
3 views2 pages

GIS Trigger for Parcel Basemap Updates

This document outlines the SQL trigger 'trg_ParcelBasemap_AfterInsertMain' which is executed after an insert operation on the 'LANDTENURE_PARCELS_BASEMAP' table. It generates a CustomID, constructs a BasemapID, creates a Certificate Number, and logs the changes into a history table. The trigger ensures that relevant parcel data is updated and tracked effectively upon insertion.

Uploaded by

Jahred Abdul
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
3 views2 pages

GIS Trigger for Parcel Basemap Updates

This document outlines the SQL trigger 'trg_ParcelBasemap_AfterInsertMain' which is executed after an insert operation on the 'LANDTENURE_PARCELS_BASEMAP' table. It generates a CustomID, constructs a BasemapID, creates a Certificate Number, and logs the changes into a history table. The trigger ensures that relevant parcel data is updated and tracked effectively upon insertion.

Uploaded by

Jahred Abdul
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

USE [HQ_GIS_DB]

GO
/****** Object: Trigger [gis_admin_hq].[trg_ParcelBasemap_AfterInsertMain]
Script Date: 9/8/2025 3:56:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [gis_admin_hq].[trg_ParcelBasemap_AfterInsertMain]
ON [gis_admin_hq].[LANDTENURE_PARCELS_BASEMAP]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

-- Step 1: Generate CustomID


UPDATE p
SET CustomID = RIGHT('000000' + CAST(NEXT VALUE FOR
gis_admin_hq.CustomParcelID_SeqMain AS VARCHAR), 6)
FROM gis_admin_hq.LANDTENURE_PARCELS_BASEMAP p
JOIN inserted i ON [Link] = [Link]
WHERE [Link] IS NULL;

-- Step 2: Generate BasemapID using CustomID with subcity code and format:
[SubcityCode]-[Wereda]-[Year]-[CustomID]
UPDATE p
SET BasemapID =
UPPER(
CASE
WHEN [Link] = 'Bole' THEN 'BO'
WHEN [Link] = 'Kirkos' THEN 'KI'
WHEN [Link] = 'Lemi Kura' THEN 'LK'
WHEN [Link] = 'Addis Ketema' THEN 'AD'
WHEN [Link] = 'Arada' THEN 'AR'
WHEN [Link] = 'Nifas Silk' THEN 'NS'
WHEN [Link] = 'Lideta' THEN 'LD'
WHEN [Link] = 'Kolfe Keranio' THEN 'KK'
WHEN [Link] = 'Gulelle' THEN 'GU'
WHEN [Link] = 'Akaki Kality' THEN 'AK'
WHEN [Link] = 'Yeka' THEN 'YE'
ELSE LEFT([Link], 2)
END
+ p.New_Wereda + CAST(YEAR(GETDATE()) AS VARCHAR) + [Link]
)
FROM gis_admin_hq.LANDTENURE_PARCELS_BASEMAP p
JOIN inserted i ON [Link] = [Link];

-- ? Step 3: Generate Certificate Number (merged from Trigger_CERTIFICATE1)


UPDATE p
SET Certificate_Number =
UPPER(LEFT([Link], 2)) + LEFT(p.New_Wereda, 2) + '-' + CAST([Link]
AS varchar)
FROM gis_admin_hq.LANDTENURE_PARCELS_BASEMAP p
JOIN inserted i ON [Link] = [Link];

-- Step 4: Insert into history table


INSERT INTO GIS_ADMIN_HQ.LANDTENURE_PARCELS_BASEMAP_HISTORY (
ParcelID, SHAPE, UniqueID, BasemapID, Landholder_Full_Name, Subcity,
New_Wereda, Block_Number, Parcel_Number, Certificate_Number, Holding_Type,
Land_Use, Land_Function, Land_Grade, Tenure_Type, Built_up_Area,
Proportional_Area, Floor_Number, ExistingGlobalID, created_user,
created_date,
last_edited_user, last_edited_date, ChangeType, ChangedBy, ChangeDate
)
SELECT
[Link], [Link], [Link], [Link], p.Landholder_Full_Name,
[Link],
p.New_Wereda, p.Block_Number, p.Parcel_Number, p.Certificate_Number,
p.Holding_Type,
p.Land_Use, p.Land_Function, p.Land_Grade, p.Tenure_Type, p.Built_up_Area,
p.Proportional_Area, p.Floor_Number, [Link], p.created_user,
p.created_date,
p.last_edited_user, p.last_edited_date,
'INSERT' AS ChangeType,
SYSTEM_USER AS ChangedBy,
GETDATE() AS ChangeDate
FROM gis_admin_hq.LANDTENURE_PARCELS_BASEMAP p
JOIN inserted i ON [Link] = [Link];
END;

You might also like