Create a Store global attribute with a SQL migration in Znode 9
Create a fictional support-label attribute, connect it to an existing Store group and family, and make reruns safe without overwriting an administrator's label or Store values.
In this guide
One field, several relationships
A support team wants a short label beside its Store contact information. The value belongs to a Store, so this recipe uses global-attribute metadata. A product specification belongs to PIM and follows a different set of tables. Decide that ownership before writing a migration.
The fictional attribute is GcgSupportLabel. Its definition says what the field is. A localized label names the field in Admin. A group places it on a form; a family and entity mapping connect the group to the intended Store configuration. None of those metadata rows is the value entered for a particular Store. This example deliberately leaves Store values under administrative control.
Prepare the target and the runner
Use Admin > Global Attributes > Attribute Groups to create or select the Store group, then associate it with the existing Store family under Global Attribute Families. Znode 9 maintains one family for each Store, User, or Account entity type. This example expects group code GcgContent and family code Store; substitute the existing family code in your installation. Confirm their locale labels before deployment.
Set @AuditUserId to an existing active deployment actor and review the locale code. IDs are resolved from bounded codes for the other prerequisites. The script rejects missing or ambiguous lookups. It also selects the non-PIM Text type explicitly, because a type name alone is not enough context.
Use one dedicated deployment connection. This example owns its transaction and refuses an ambient transaction. If your DbUp runner owns transactions, adapt the transaction boundary as a reviewed change before using it. A transaction-scoped application lock serializes other deployments that use the same lock name. It does not stop Admin or unrelated scripts from writing these tables, so use a controlled deployment window.
Add the metadata migration
Save this original example as a new migration using your repository's numbering convention. It creates the definition only when absent, checks the existing type and behavior flags, and adds each missing relationship separately. Existing localized labels are preserved, including an administrator's changes. Duplicate rows cause an explicit failure.
Set the required actor input before execution. The result returns the resolved attribute ID and configuration codes. Complete the Admin and application checks below so the database change reaches the intended user workflow.
001-add-support-label.sql sql
-- Original GCG example. Znode 9 global-attribute schema pattern.
-- Configure these inputs after checking the target database and patch.
-- Run on a dedicated deployment connection without an outer transaction.
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @AuditUserId int = NULL; -- Required: existing deployment actor.
DECLARE @LocaleCode nvarchar(50) = N'en-US';
DECLARE @GroupCode varchar(200) = 'GcgContent';
DECLARE @FamilyCode varchar(200) = 'Store';
DECLARE @Code nvarchar(300) = N'GcgSupportLabel';
DECLARE @Label nvarchar(300) = N'Support label';
DECLARE @EntityId int, @TypeId int, @LocaleId int,
@GroupId int, @FamilyId int, @AttributeId int,
@LockResult int, @Now datetime = GETDATE();
IF @@TRANCOUNT <> 0
THROW 51000, 'This example owns its transaction. Review runner configuration.', 1;
IF @AuditUserId IS NULL OR NOT EXISTS
(SELECT 1 FROM dbo.ZnodeUser WHERE UserId = @AuditUserId AND IsActive = 1)
THROW 51001, 'Set an existing active deployment actor.', 1;
BEGIN TRY
BEGIN TRANSACTION;
EXEC @LockResult = sys.sp_getapplock
@Resource = N'GCG.StoreAttributeMetadata',
@LockMode = 'Exclusive', @LockOwner = 'Transaction',
@LockTimeout = 10000;
IF @LockResult < 0
THROW 51002, 'Another cooperating attribute deployment is running.', 1;
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalEntity
WHERE EntityName = N'Store' AND IsActive = 1) <> 1
THROW 51003, 'Expected exactly one active Store entity.', 1;
SELECT @EntityId = GlobalEntityId FROM dbo.ZnodeGlobalEntity
WHERE EntityName = N'Store' AND IsActive = 1;
IF (SELECT COUNT(*) FROM dbo.ZnodeAttributeType
WHERE AttributeTypeName = 'Text' AND IsPimAttributeType = 0) <> 1
THROW 51004, 'Expected exactly one non-PIM Text type.', 1;
SELECT @TypeId = AttributeTypeId FROM dbo.ZnodeAttributeType
WHERE AttributeTypeName = 'Text' AND IsPimAttributeType = 0;
IF (SELECT COUNT(*) FROM dbo.ZnodeLocale
WHERE Code = @LocaleCode AND IsActive = 1) <> 1
THROW 51005, 'Expected exactly one active locale.', 1;
SELECT @LocaleId = LocaleId FROM dbo.ZnodeLocale
WHERE Code = @LocaleCode AND IsActive = 1;
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalAttributeGroup
WHERE GroupCode = @GroupCode AND GlobalEntityId = @EntityId) <> 1
THROW 51006, 'Create or select one Store group before deployment.', 1;
SELECT @GroupId = GlobalAttributeGroupId FROM dbo.ZnodeGlobalAttributeGroup
WHERE GroupCode = @GroupCode AND GlobalEntityId = @EntityId;
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalAttributeFamily
WHERE FamilyCode = @FamilyCode AND GlobalEntityId = @EntityId) <> 1
THROW 51007, 'Expected exactly one intended Store family.', 1;
SELECT @FamilyId = GlobalAttributeFamilyId FROM dbo.ZnodeGlobalAttributeFamily
WHERE FamilyCode = @FamilyCode AND GlobalEntityId = @EntityId;
IF EXISTS (SELECT 1 FROM dbo.ZnodeGlobalAttribute
WHERE AttributeCode = @Code
AND (GlobalEntityId <> @EntityId OR GlobalEntityId IS NULL))
THROW 51014, 'Attribute code is already used by another entity.', 1;
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalAttribute
WHERE AttributeCode = @Code AND GlobalEntityId = @EntityId) > 1
THROW 51008, 'Duplicate attribute definitions require investigation.', 1;
SELECT @AttributeId = GlobalAttributeId FROM dbo.ZnodeGlobalAttribute
WHERE AttributeCode = @Code AND GlobalEntityId = @EntityId;
IF @AttributeId IS NOT NULL AND EXISTS
(SELECT 1 FROM dbo.ZnodeGlobalAttribute WHERE GlobalAttributeId = @AttributeId
AND (AttributeTypeId IS NULL OR AttributeTypeId <> @TypeId
OR ISNULL(IsSystemDefined, 1) <> 0 OR ISNULL(IsRequired, 1) <> 0
OR ISNULL(IsLocalizable, 0) <> 1 OR ISNULL(IsActive, 0) <> 1))
THROW 51009, 'Existing definition conflicts with this migration.', 1;
IF @AttributeId IS NULL
BEGIN
INSERT dbo.ZnodeGlobalAttribute
(AttributeTypeId, AttributeCode, IsRequired, IsLocalizable, IsActive,
DisplayOrder, HelpDescription, CreatedBy, CreatedDate,
ModifiedBy, ModifiedDate, IsSystemDefined, GlobalEntityId)
VALUES (@TypeId, @Code, 0, 1, 1, 100, N'Customer-facing support label',
@AuditUserId, @Now, @AuditUserId, @Now, 0, @EntityId);
SET @AttributeId = CONVERT(int, SCOPE_IDENTITY());
END;
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalAttributeLocale
WHERE GlobalAttributeId = @AttributeId AND LocaleId = @LocaleId) > 1
THROW 51010, 'Duplicate localized labels require investigation.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.ZnodeGlobalAttributeLocale
WHERE GlobalAttributeId = @AttributeId AND LocaleId = @LocaleId)
INSERT dbo.ZnodeGlobalAttributeLocale
(LocaleId, GlobalAttributeId, AttributeName, Description,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
VALUES (@LocaleId, @AttributeId, @Label, NULL,
@AuditUserId, @Now, @AuditUserId, @Now);
-- An existing translated label belongs to the administrator. Preserve it.
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalAttributeGroupMapper
WHERE GlobalAttributeGroupId = @GroupId AND GlobalAttributeId = @AttributeId) > 1
THROW 51011, 'Duplicate attribute/group mapping.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.ZnodeGlobalAttributeGroupMapper
WHERE GlobalAttributeGroupId = @GroupId AND GlobalAttributeId = @AttributeId)
INSERT dbo.ZnodeGlobalAttributeGroupMapper
(GlobalAttributeGroupId, GlobalAttributeId, AttributeDisplayOrder,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
VALUES (@GroupId, @AttributeId, 100, @AuditUserId, @Now, @AuditUserId, @Now);
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalFamilyGroupMapper
WHERE GlobalAttributeFamilyId = @FamilyId AND GlobalAttributeGroupId = @GroupId) > 1
THROW 51012, 'Duplicate family/group mapping.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.ZnodeGlobalFamilyGroupMapper
WHERE GlobalAttributeFamilyId = @FamilyId AND GlobalAttributeGroupId = @GroupId)
INSERT dbo.ZnodeGlobalFamilyGroupMapper
(GlobalAttributeFamilyId, GlobalAttributeGroupId, AttributeGroupDisplayOrder,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
VALUES (@FamilyId, @GroupId, 100, @AuditUserId, @Now, @AuditUserId, @Now);
IF (SELECT COUNT(*) FROM dbo.ZnodeGlobalGroupEntityMapper
WHERE GlobalAttributeGroupId = @GroupId AND GlobalEntityId = @EntityId) > 1
THROW 51013, 'Duplicate group/entity mapping.', 1;
IF NOT EXISTS (SELECT 1 FROM dbo.ZnodeGlobalGroupEntityMapper
WHERE GlobalAttributeGroupId = @GroupId AND GlobalEntityId = @EntityId)
INSERT dbo.ZnodeGlobalGroupEntityMapper
(GlobalAttributeGroupId, GlobalEntityId, AttributeGroupDisplayOrder,
CreatedBy, CreatedDate, ModifiedBy, ModifiedDate)
VALUES (@GroupId, @EntityId, 100, @AuditUserId, @Now, @AuditUserId, @Now);
COMMIT TRANSACTION;
SELECT @Code AS AttributeCode, @AttributeId AS AttributeId,
@GroupCode AS GroupCode, @FamilyCode AS FamilyCode, @LocaleCode AS LocaleCode;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
THROW;
END CATCH;
Read back the graph
Use the query below to inspect the exact Store definition and locale. Confirm the group and family are the intended ones rather than simply counting all mappings in the database. One row is expected for this recipe's specific mapping path. Zero indicates a missing prerequisite or relationship; multiple rows require investigation.
Also compare unrelated rows and any existing Store values before and after deployment. A broad join can multiply rows even when the definition is unique, which is why the migration checks the individual edges before inserting.
verify-support-label.sql sql
SELECT a.AttributeCode, l.AttributeName, loc.Code AS LocaleCode,
g.GroupCode, f.FamilyCode, e.EntityName
FROM dbo.ZnodeGlobalAttribute a
JOIN dbo.ZnodeGlobalEntity e ON e.GlobalEntityId = a.GlobalEntityId
JOIN dbo.ZnodeGlobalAttributeLocale l ON l.GlobalAttributeId = a.GlobalAttributeId
JOIN dbo.ZnodeLocale loc ON loc.LocaleId = l.LocaleId
JOIN dbo.ZnodeGlobalAttributeGroupMapper gm ON gm.GlobalAttributeId = a.GlobalAttributeId
JOIN dbo.ZnodeGlobalAttributeGroup g ON g.GlobalAttributeGroupId = gm.GlobalAttributeGroupId
JOIN dbo.ZnodeGlobalFamilyGroupMapper fm ON fm.GlobalAttributeGroupId = g.GlobalAttributeGroupId
JOIN dbo.ZnodeGlobalAttributeFamily f ON f.GlobalAttributeFamilyId = fm.GlobalAttributeFamilyId
JOIN dbo.ZnodeGlobalGroupEntityMapper em ON em.GlobalAttributeGroupId = g.GlobalAttributeGroupId
AND em.GlobalEntityId = e.GlobalEntityId
WHERE a.AttributeCode = N'GcgSupportLabel' AND e.EntityName = N'Store'
AND g.GroupCode = 'GcgContent' AND f.FamilyCode = 'Store'
AND g.GlobalEntityId = e.GlobalEntityId AND f.GlobalEntityId = e.GlobalEntityId
AND loc.Code = N'en-US';Verify the Store workflow
Open Stores and Reps > Stores > Additional Store Attributes and locate the field for the intended Store. Enter a synthetic support label, save, reload, and verify the same locale. Read the value through the application path used by your customization. Publish Store settings and refresh caches when required by that consuming path and Znode patch.
Exercise fresh creation, an unchanged rerun, repair of a missing mapping, an edited Admin label, a conflicting definition, and duplicate locale rows in a disposable copy of the target schema. Verify the same field through Admin, the application read path, and the deployment runner. These checks connect migration behavior to the actual Store configuration.
- Run against a disposable copy of the target schema before release.
- Record the exact Znode patch, SQL Server version, migration runner, and observed Admin result.
- Test missing group/family prerequisites and a same-code conflict in your target schema.
- Verify no Store values or unrelated metadata changed.
Make the next change a forward migration
Once the file has been recorded in the migration journal, editing that file is not a reliable way to update deployed environments. Add a new migration for a new requirement. Keep deliberate value changes separate from metadata creation so reviewers can see exactly which administrator-owned configuration is being changed.
Avoid deleting the attribute as a routine rollback once users have entered data or customizations depend on it. A forward correction is often safer. If removal is required, first inventory its values and relationships and define a reviewed recovery path. The next recipe explains the difference between journaled migrations and programmability files that run on every deployment.
References and further reading
Bring your next engineering question.
GCG helps teams review attribute migrations, deployment behavior, and the application paths that depend on them.
Independent guidance from GCG. Znode is a trademark of its owner. Examples use fictional data and are not official platform documentation. Suggest a correction.