Teamcenter 替代组变更审计触发器

# 替代组变更审计触发器

## 1. 创建审计表

```sql
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'ALT_ALTERNATE_AUDIT')
CREATE TABLE [dbo].[ALT_ALTERNATE_AUDIT] (
    audit_id                   BIGINT IDENTITY(1,1) PRIMARY KEY,
    operation_type             NVARCHAR(10)  NOT NULL,
    operation_time             DATETIME2     NOT NULL DEFAULT GETDATE(),
    operation_user             NVARCHAR(128) NOT NULL DEFAULT SYSTEM_USER,
    alternate_group_puid       NVARCHAR(30)  NOT NULL,
    preferred_item_puid        NVARCHAR(30)  NULL,
    preferred_item_id          NVARCHAR(30)  NULL,
    occurrence_puid            NVARCHAR(30)  NULL,
    parent_bom_view_rev_puid   NVARCHAR(30)  NULL,
    parent_bom_view_puid       NVARCHAR(30)  NULL,
    parent_item_puid           NVARCHAR(30)  NULL,
    parent_item_id             NVARCHAR(30)  NULL,
    parent_item_revision_id    NVARCHAR(30)  NULL,
    pmodifier_user_id          NVARCHAR(30)  NULL,
    pmodifier_user_name        NVARCHAR(100) NULL,
    plast_mod_date             DATETIME2     NULL,
    alternate_type             NVARCHAR(10)  NOT NULL,
    old_pvalu_0                NVARCHAR(30)  NULL,
    old_item_id                NVARCHAR(30)  NULL,
    old_revision_id            NVARCHAR(30)  NULL,
    old_pseq                   INT           NULL,
    old_pobject_name            NVARCHAR(200) NULL,
    new_pvalu_0                NVARCHAR(30)  NULL,
    new_item_id                NVARCHAR(30)  NULL,
    new_revision_id            NVARCHAR(30)  NULL,
    new_pseq                   INT           NULL,
    new_pobject_name            NVARCHAR(200) NULL
);
GO
```

## 2. PALT_ITEMS 触发器（零件替代）

```sql
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'TR_PALT_ITEMS_Audit')
    DROP TRIGGER [dbo].[TR_PALT_ITEMS_Audit];
GO

CREATE TRIGGER [dbo].[TR_PALT_ITEMS_Audit]
ON [dbo].[PALT_ITEMS]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @op_type NVARCHAR(10);
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
        SET @op_type = 'UPDATE';
    ELSE IF EXISTS (SELECT 1 FROM inserted)
        SET @op_type = 'INSERT';
    ELSE
        SET @op_type = 'DELETE';

    WITH change_data AS (
        SELECT
            ISNULL(i.puid, d.puid) AS alternate_group_puid,
            @op_type               AS operation_type,
            'ITEM'                 AS alternate_type,
            d.pvalu_0              AS old_pvalu_0,
            d.pseq                 AS old_pseq,
            i.pvalu_0              AS new_pvalu_0,
            i.pseq                 AS new_pseq
        FROM inserted i
        FULL OUTER JOIN deleted d
            ON i.puid = d.puid AND i.pvalu_0 = d.pvalu_0
    ),
    parent_ctx AS (
        SELECT DISTINCT
            alt.puid                               AS alternate_group_puid,
            alt.rpreferred_itemu                   AS preferred_item_puid,
            pref_item.pitem_id                     AS preferred_item_id,
            occ.puid                               AS occurrence_puid,
            occ.rparent_bvru                       AS parent_bom_view_rev_puid,
            bvr.rbom_viewu                         AS parent_bom_view_puid,
            bv.rparent_itemu                       AS parent_item_puid,
            par_item.pitem_id                      AS parent_item_id,
            par_rev.pitem_revision_id              AS parent_item_revision_id,
            mod_user.puser_id                      AS pmodifier_user_id,
            mod_user.puser_name                    AS pmodifier_user_name,
            pao.plast_mod_date                     AS plast_mod_date
        FROM [dbo].[PPSALTERNATELIST] alt
        INNER JOIN change_data cd
            ON cd.alternate_group_puid = alt.puid
        LEFT JOIN [dbo].[PITEM] pref_item
            ON pref_item.puid = alt.rpreferred_itemu
        LEFT JOIN [dbo].[PPSOCCURRENCE] occ
            ON occ.ralternate_etc_refu = alt.puid
        LEFT JOIN [dbo].[PPSBOMVIEWREVISION] bvr
            ON bvr.puid = occ.rparent_bvru
        LEFT JOIN [dbo].[PPSBOMVIEW] bv
            ON bv.puid = bvr.rbom_viewu
        LEFT JOIN [dbo].[PITEM] par_item
            ON par_item.puid = bv.rparent_itemu
        LEFT JOIN [dbo].[PSTRUCTURE_REVISIONS] sr
            ON sr.pvalu_0 = occ.rparent_bvru
        LEFT JOIN [dbo].[PITEMREVISION] par_rev
            ON par_rev.puid = sr.puid
        LEFT JOIN [dbo].[PPOM_APPLICATION_OBJECT] pao
            ON pao.puid = alt.puid
        LEFT JOIN [dbo].[PPOM_USER] mod_user
            ON mod_user.puid = pao.rlast_mod_useru
    )
    INSERT INTO ALT_ALTERNATE_AUDIT (
        operation_type, alternate_group_puid, alternate_type,
        occurrence_puid, parent_bom_view_rev_puid, parent_bom_view_puid,
        parent_item_puid, parent_item_id, parent_item_revision_id,
        preferred_item_puid, preferred_item_id,
        pmodifier_user_id, pmodifier_user_name, plast_mod_date,
        old_pvalu_0, old_item_id, old_revision_id, old_pseq, old_pobject_name,
        new_pvalu_0, new_item_id, new_revision_id, new_pseq, new_pobject_name
    )
    SELECT
        cd.operation_type,
        cd.alternate_group_puid,
        cd.alternate_type,
        ctx.occurrence_puid,
        ctx.parent_bom_view_rev_puid,
        ctx.parent_bom_view_puid,
        ctx.parent_item_puid,
        ctx.parent_item_id,
        ctx.parent_item_revision_id,
        ctx.preferred_item_puid,
        ctx.preferred_item_id,
        ctx.pmodifier_user_id,
        ctx.pmodifier_user_name,
        ctx.plast_mod_date,
        cd.old_pvalu_0,
        old_item.pitem_id,
        old_rev.pitem_revision_id,
        cd.old_pseq,
        old_pwo.pobject_name,
        cd.new_pvalu_0,
        new_item.pitem_id,
        new_rev.pitem_revision_id,
        cd.new_pseq,
        new_pwo.pobject_name
    FROM change_data cd
    INNER JOIN parent_ctx ctx
        ON ctx.alternate_group_puid = cd.alternate_group_puid
    LEFT JOIN [dbo].[PITEM] old_item
        ON old_item.puid = cd.old_pvalu_0
    LEFT JOIN [dbo].[PITEMREVISION] old_rev
        ON old_rev.puid = cd.old_pvalu_0
    LEFT JOIN [dbo].[PWORKSPACEOBJECT] old_pwo
        ON old_pwo.puid = cd.old_pvalu_0
    LEFT JOIN [dbo].[PITEM] new_item
        ON new_item.puid = cd.new_pvalu_0
    LEFT JOIN [dbo].[PITEMREVISION] new_rev
        ON new_rev.puid = cd.new_pvalu_0
    LEFT JOIN [dbo].[PWORKSPACEOBJECT] new_pwo
        ON new_pwo.puid = cd.new_pvalu_0
    WHERE cd.operation_type IN ('INSERT', 'DELETE')
       OR (cd.operation_type = 'UPDATE'
           AND (ISNULL(cd.old_pvalu_0, '') <> ISNULL(cd.new_pvalu_0, '')
                OR ISNULL(cd.old_pseq, -1) <> ISNULL(cd.new_pseq, -1)));
END;
GO
```

## 3. PALT_VIEWS 触发器（组件替代）

```sql
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'TR_PALT_VIEWS_Audit')
    DROP TRIGGER [dbo].[TR_PALT_VIEWS_Audit];
GO

CREATE TRIGGER [dbo].[TR_PALT_VIEWS_Audit]
ON [dbo].[PALT_VIEWS]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @op_type NVARCHAR(10);
    IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
        SET @op_type = 'UPDATE';
    ELSE IF EXISTS (SELECT 1 FROM inserted)
        SET @op_type = 'INSERT';
    ELSE
        SET @op_type = 'DELETE';

    WITH change_data AS (
        SELECT
            ISNULL(i.puid, d.puid) AS alternate_group_puid,
            @op_type               AS operation_type,
            'VIEW'                 AS alternate_type,
            d.pvalu_0              AS old_pvalu_0,
            d.pseq                 AS old_pseq,
            i.pvalu_0              AS new_pvalu_0,
            i.pseq                 AS new_pseq
        FROM inserted i
        FULL OUTER JOIN deleted d
            ON i.puid = d.puid AND i.pvalu_0 = d.pvalu_0
    ),
    parent_ctx AS (
        SELECT DISTINCT
            alt.puid                               AS alternate_group_puid,
            alt.rpreferred_itemu                   AS preferred_item_puid,
            pref_item.pitem_id                     AS preferred_item_id,
            occ.puid                               AS occurrence_puid,
            occ.rparent_bvru                       AS parent_bom_view_rev_puid,
            bvr.rbom_viewu                         AS parent_bom_view_puid,
            bv.rparent_itemu                       AS parent_item_puid,
            par_item.pitem_id                      AS parent_item_id,
            par_rev.pitem_revision_id              AS parent_item_revision_id,
            mod_user.puser_id                      AS pmodifier_user_id,
            mod_user.puser_name                    AS pmodifier_user_name,
            pao.plast_mod_date                     AS plast_mod_date
        FROM [dbo].[PPSALTERNATELIST] alt
        INNER JOIN change_data cd
            ON cd.alternate_group_puid = alt.puid
        LEFT JOIN [dbo].[PITEM] pref_item
            ON pref_item.puid = alt.rpreferred_itemu
        LEFT JOIN [dbo].[PPSOCCURRENCE] occ
            ON occ.ralternate_etc_refu = alt.puid
        LEFT JOIN [dbo].[PPSBOMVIEWREVISION] bvr
            ON bvr.puid = occ.rparent_bvru
        LEFT JOIN [dbo].[PPSBOMVIEW] bv
            ON bv.puid = bvr.rbom_viewu
        LEFT JOIN [dbo].[PITEM] par_item
            ON par_item.puid = bv.rparent_itemu
        LEFT JOIN [dbo].[PSTRUCTURE_REVISIONS] sr
            ON sr.pvalu_0 = occ.rparent_bvru
        LEFT JOIN [dbo].[PITEMREVISION] par_rev
            ON par_rev.puid = sr.puid
        LEFT JOIN [dbo].[PPOM_APPLICATION_OBJECT] pao
            ON pao.puid = alt.puid
        LEFT JOIN [dbo].[PPOM_USER] mod_user
            ON mod_user.puid = pao.rlast_mod_useru
    )
    INSERT INTO ALT_ALTERNATE_AUDIT (
        operation_type, alternate_group_puid, alternate_type,
        occurrence_puid, parent_bom_view_rev_puid, parent_bom_view_puid,
        parent_item_puid, parent_item_id, parent_item_revision_id,
        preferred_item_puid, preferred_item_id,
        pmodifier_user_id, pmodifier_user_name, plast_mod_date,
        old_pvalu_0, old_item_id, old_revision_id, old_pseq, old_pobject_name,
        new_pvalu_0, new_item_id, new_revision_id, new_pseq, new_pobject_name
    )
    SELECT
        cd.operation_type,
        cd.alternate_group_puid,
        cd.alternate_type,
        ctx.occurrence_puid,
        ctx.parent_bom_view_rev_puid,
        ctx.parent_bom_view_puid,
        ctx.parent_item_puid,
        ctx.parent_item_id,
        ctx.parent_item_revision_id,
        ctx.preferred_item_puid,
        ctx.preferred_item_id,
        ctx.pmodifier_user_id,
        ctx.pmodifier_user_name,
        ctx.plast_mod_date,
        cd.old_pvalu_0,
        old_bv_item.pitem_id,
        old_rev.pitem_revision_id,
        cd.old_pseq,
        old_pwo.pobject_name,
        cd.new_pvalu_0,
        new_bv_item.pitem_id,
        new_rev.pitem_revision_id,
        cd.new_pseq,
        new_pwo.pobject_name
    FROM change_data cd
    INNER JOIN parent_ctx ctx
        ON ctx.alternate_group_puid = cd.alternate_group_puid
    LEFT JOIN [dbo].[PPSBOMVIEW] old_bv
        ON old_bv.puid = cd.old_pvalu_0
    LEFT JOIN [dbo].[PITEM] old_bv_item
        ON old_bv_item.puid = old_bv.rparent_itemu
    LEFT JOIN [dbo].[PITEMREVISION] old_rev
        ON old_rev.puid = cd.old_pvalu_0
    LEFT JOIN [dbo].[PWORKSPACEOBJECT] old_pwo
        ON old_pwo.puid = cd.old_pvalu_0
    LEFT JOIN [dbo].[PPSBOMVIEW] new_bv
        ON new_bv.puid = cd.new_pvalu_0
    LEFT JOIN [dbo].[PITEM] new_bv_item
        ON new_bv_item.puid = new_bv.rparent_itemu
    LEFT JOIN [dbo].[PITEMREVISION] new_rev
        ON new_rev.puid = cd.new_pvalu_0
    LEFT JOIN [dbo].[PWORKSPACEOBJECT] new_pwo
        ON new_pwo.puid = cd.new_pvalu_0
    WHERE cd.operation_type IN ('INSERT', 'DELETE')
       OR (cd.operation_type = 'UPDATE'
           AND (ISNULL(cd.old_pvalu_0, '') <> ISNULL(cd.new_pvalu_0, '')
                OR ISNULL(cd.old_pseq, -1) <> ISNULL(cd.new_pseq, -1)));
END;
GO
```

## 4. PPSALTERNATELIST 触发器（首选零组件变更）

```sql
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'TR_PPSALTERNATELIST_Audit')
    DROP TRIGGER [dbo].[TR_PPSALTERNATELIST_Audit];
GO

CREATE TRIGGER [dbo].[TR_PPSALTERNATELIST_Audit]
ON [dbo].[PPSALTERNATELIST]
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    WITH changed AS (
        SELECT
            i.puid                    AS alternate_group_puid,
            d.rpreferred_itemu        AS old_preferred_puid,
            i.rpreferred_itemu        AS new_preferred_puid,
            occ.puid                  AS occurrence_puid,
            occ.rparent_bvru          AS parent_bom_view_rev_puid,
            bvr.rbom_viewu            AS parent_bom_view_puid,
            bv.rparent_itemu          AS parent_item_puid,
            par_item.pitem_id         AS parent_item_id,
            par_rev.pitem_revision_id AS parent_item_revision_id,
            mod_user.puser_id         AS pmodifier_user_id,
            mod_user.puser_name       AS pmodifier_user_name,
            pao.plast_mod_date        AS plast_mod_date
        FROM inserted i
        INNER JOIN deleted d
            ON d.puid = i.puid
        LEFT JOIN [dbo].[PPSOCCURRENCE] occ
            ON occ.ralternate_etc_refu = i.puid
        LEFT JOIN [dbo].[PPSBOMVIEWREVISION] bvr
            ON bvr.puid = occ.rparent_bvru
        LEFT JOIN [dbo].[PPSBOMVIEW] bv
            ON bv.puid = bvr.rbom_viewu
        LEFT JOIN [dbo].[PITEM] par_item
            ON par_item.puid = bv.rparent_itemu
        LEFT JOIN [dbo].[PSTRUCTURE_REVISIONS] sr
            ON sr.pvalu_0 = occ.rparent_bvru
        LEFT JOIN [dbo].[PITEMREVISION] par_rev
            ON par_rev.puid = sr.puid
        LEFT JOIN [dbo].[PPOM_APPLICATION_OBJECT] pao
            ON pao.puid = i.puid
        LEFT JOIN [dbo].[PPOM_USER] mod_user
            ON mod_user.puid = pao.rlast_mod_useru
        WHERE ISNULL(d.rpreferred_itemu, '') <> ISNULL(i.rpreferred_itemu, '')
    )
    INSERT INTO ALT_ALTERNATE_AUDIT (
        operation_type, alternate_group_puid, alternate_type,
        occurrence_puid, parent_bom_view_rev_puid, parent_bom_view_puid,
        parent_item_puid, parent_item_id, parent_item_revision_id,
        preferred_item_puid, preferred_item_id,
        pmodifier_user_id, pmodifier_user_name, plast_mod_date,
        old_pvalu_0, old_item_id, old_pseq,
        new_pvalu_0, new_item_id, new_pseq
    )
    SELECT
        'UPDATE'             AS operation_type,
        cd.alternate_group_puid,
        'GROUP'              AS alternate_type,
        cd.occurrence_puid,
        cd.parent_bom_view_rev_puid,
        cd.parent_bom_view_puid,
        cd.parent_item_puid,
        cd.parent_item_id,
        cd.parent_item_revision_id,
        cd.new_preferred_puid,
        new_pref_item.pitem_id,
        cd.pmodifier_user_id,
        cd.pmodifier_user_name,
        cd.plast_mod_date,
        cd.old_preferred_puid,
        old_pref_item.pitem_id,
        NULL,
        cd.new_preferred_puid,
        new_pref_item.pitem_id,
        NULL
    FROM changed cd
    LEFT JOIN [dbo].[PITEM] old_pref_item
        ON old_pref_item.puid = cd.old_preferred_puid
    LEFT JOIN [dbo].[PITEM] new_pref_item
        ON new_pref_item.puid = cd.new_preferred_puid;
END;
GO
```

## 验证查询

```sql
-- 查看所有审计记录
SELECT * FROM [dbo].[ALT_ALTERNATE_AUDIT] ORDER BY audit_id DESC;

-- 按父项零组件ID查询
SELECT * FROM [dbo].[ALT_ALTERNATE_AUDIT]
WHERE parent_item_id = 'xxx'
ORDER BY operation_time DESC;

-- 按修改人查询
SELECT * FROM [dbo].[ALT_ALTERNATE_AUDIT]
WHERE pmodifier_user_id = 'xxx'
ORDER BY operation_time DESC;

-- 统计各类操作数量
SELECT operation_type, alternate_type, COUNT(*) AS cnt
FROM [dbo].[ALT_ALTERNATE_AUDIT]
GROUP BY operation_type, alternate_type
ORDER BY operation_type, alternate_type;
```

## 数据追溯路径

```
PALT_ITEMS.puid / PALT_VIEWS.puid
  → PPSALTERNATELIST.puid
    → PPSOCCURRENCE.ralternate_etc_refu (1:1)
      → PPSOCCURRENCE.rparent_bvru → PPSBOMVIEWREVISION.puid
        → PPSBOMVIEWREVISION.rbom_viewu → PPSBOMVIEW.puid
          → PPSBOMVIEW.rparent_itemu → PITEM.puid (父项)
            → PITEM.pitem_id (父项零组件ID)
        → PSTRUCTURE_REVISIONS.pvalu_0 = PPSBOMVIEWREVISION.puid
          → PSTRUCTURE_REVISIONS.puid → PITEMREVISION.puid
            → PITEMREVISION.pitem_revision_id (父项版本)
    → PPOM_APPLICATION_OBJECT.puid = PPSALTERNATELIST.puid
      → PPOM_APPLICATION_OBJECT.rlast__mod_useru → PPOM_USER.puid
        → PPOM_USER.puser_id (修改人账户)
        → PPOM_USER.puser_name (修改人名称)
      → PPOM_APPLICATION_OBJECT.plast_mod_date (最后修改时间)
```

## 说明

- 所有 `[tc].` 前缀已去掉，在 `[tc]` 数据库下直接执行
- `PPSOCCURRENCE` 1:1 关系，每个替代组对应一个 BOM 行
- `rpreferred_itemu` 双 r 拼写
- 零件替代 → `PALT_ITEMS`（pvalu_0 → PITEM.puid）
- 组件替代 → `PALT_VIEWS`（pvalu_0 → PPSBOMVIEW.puid → PITEM.puid）
- 修改人信息 → `PPOM_APPLICATION_OBJECT.puid = PPSALTERNATELIST.puid` → `PPOM_USER`
- 审计表修改人字段统一加 `p` 前缀：`pmodifier_user_id`, `pmodifier_user_name`, `plast_mod_date`
- UPDATE 只记录值实际变化的行，避免无意义审计
posted @ 2026-07-20 16:57  张永全-PLM顾问  阅读(18)  评论(0)    收藏  举报