<think>我们有两个表：InspectInfo（調査情報）和PurchaseMaterial（アイテム・購入品）触发器定义在InspectInfo表上，当该表有记录创建（INSERT）时触发。需求：根据InspectInfo表中插入的记录的某些属性值（如REACHSVHC,POPs等）来更新PurchaseMaterial表中对应记录的规制判定结果属性。注意：触发器需要知道InspectInfo记录与PurchaseMaterial记录的关联关系。需求中没有明确给出，所以我们需要假设存在一个外键关联。假设：InspectInfo表中有一个外键字段（比如purchase_material_id）指向PurchaseMaterial表的主键。如果实际表结构中没有这样的关联字段，则此触发器无法实现。这里我们假设存在这样的关联字段，命名为purchase_material_id。触发器逻辑：当向InspectInfo表插入一条记录时，根据该记录中各个属性（REACHSVHC,POPs等）的值，去更新关联的PurchaseMaterial记录的相应字段。属性映射：InspectInfo表的属性（需求中提到的）：[REACHSVHC]属性->假设列名为：reach_svhc[POPs]属性->pops[RoHS]属性->rohs[CP65]属性->cp65[TSCA]属性->tsca[CHCC]属性->chcc[CPSIA]属性->cpsia[OEKO-TEXstandard100]属性->oeko_tex[AFIRM]属性->afirm[bluesign]属性->bluesignPurchaseMaterial表的属性（需求中给出的）：[規制判定結果[REACHSVHC]]属性->y_reach_result[規制判定結果[POPs]]属性->y_pops_result...以此类推规则：对于每个属性，如果InspectInfo中对应的属性值为'qualified'或'qualified_conditional'，则将PurchaseMaterial中对应的属性更新为'PASS'；如果为'failed'，则更新为'FAIL'。注意：触发器在记录创建（INSERT）时触发，所以是AFTERINSERT（或BEFOREINSERT，但更新另一个表通常在AFTER）。由于可能同时更新多个属性，我们需要对每个属性进行判断并更新PurchaseMaterial对应的字段。但是，注意：一条InspectInfo记录可能同时更新多个属性，因此我们需要在触发器中一次性更新PurchaseMaterial记录的所有相关字段。实现步骤：1.在InspectInfo表上创建AFTERINSERT触发器。2.对于每个插入的记录，根据其各个属性的值，设置对应的PurchaseMaterial字段的更新值。3.更新PurchaseMaterial表中与该InspectInfo记录关联的记录（通过purchase_material_id）。代码结构：假设表结构：InspectInfo(idNUMBERPRIMARYKEY,purchase_material_idNUMBER,--外键，关联到PurchaseMaterial的idreach_svhcVARCHAR2(50),popsVARCHAR2(50),rohsVARCHAR2(50),cp65VARCHAR2(50),tscaVARCHAR2(50),chccVARCHAR2(50),cpsiaVARCHAR2(50),oeko_texVARCHAR2(50),afirmVARCHAR2(50),bluesignVARCHAR2(50));PurchaseMaterial(idNUMBERPRIMARYKEY,y_reach_resultVARCHAR2(50),y_pops_resultVARCHAR2(50),y_rohs_resultVARCHAR2(50),y_cp65_resultVARCHAR2(50),y_tsca_resultVARCHAR2(50),y_chcc_resultVARCHAR2(50),y_cpsia_resultVARCHAR2(50),y_oeko_tex_resultVARCHAR2(50),y_afirm_resultVARCHAR2(50),y_bluesign_resultVARCHAR2(50));触发器代码：</think>### Oracle触发器实现方案
根据您的需求，我将创建一个在`InspectInfo`表插入记录时，根据特定字段值更新`PurchaseMaterial`表中对应规管判定结果的触发器。以下是完整实现代码：
```sql
CREATE OR REPLACE TRIGGER TRG_IA_INSPECTINFO_01
AFTER INSERT ON InspectInfo
FOR EACH ROW
BEGIN
-- 处理REACH SVHC判定结果
IF :NEW.reach_svhc IN ('qualified', 'qualified_conditional') THEN
UPDATE PurchaseMaterial
SET y_reach_result = 'PASS'
WHERE id = :NEW.purchase_material_id; -- 假设关联字段
ELSIF :NEW.reach_svhc = 'failed' THEN
UPDATE PurchaseMaterial
SET y_reach_result = 'FAIL'
WHERE id = :NEW.purchase_material_id;
END IF;
-- 处理POPs判定结果
IF :NEW.pops IN ('qualified', 'qualified_conditional') THEN
UPDATE PurchaseMaterial
SET y_pops_result = 'PASS'
WHERE id = :NEW.purchase_material_id;
ELSIF :NEW.pops = 'failed' THEN
UPDATE PurchaseMaterial
SET y_pops_result = 'FAIL'
WHERE id = :NEW.purchase_material_id;
END IF;
-- 处理RoHS判定结果
IF :NEW.rohs IN ('qualified', 'qualified_conditional') THEN
UPDATE PurchaseMaterial
SET y_rohs_result = 'PASS'
WHERE id = :NEW.purchase_material_id;
ELSIF :NEW.rohs = 'failed' THEN
UPDATE PurchaseMaterial
SET y_rohs_result = 'FAIL'
WHERE id = :NEW.purchase_material_id;
END IF;
-- 处理CP65判定结果
IF :NEW.cp65 IN ('qualified', 'qualified_conditional') THEN
UPDATE PurchaseMaterial
SET y_cp65_result = 'PASS'
WHERE id = :NEW.purchase_material_id;
ELSIF :NEW.cp65 = 'failed' THEN
UPDATE PurchaseMaterial
SET y_cp65_result = 'FAIL'
WHERE id = :NEW.purchase_material_id;
END IF;
-- 处理TSCA判定结果
IF :NEW.tsca IN ('qualified', 'qualified_conditional') THEN
UPDATE PurchaseMaterial
SET y_tsca_result = 'PASS'
WHERE id = :NEW.purchase_material_id;
ELSIF :NEW.tsca = 'failed' THEN
UPDATE PurchaseMaterial
SET y_tsca_result = 'FAIL'
WHERE id = :NEW.purchase_material_id;
END IF;
-- 处理CHCC判定结果
IF :NEW.chcc IN ('qualified', 'qualified_conditional') THEN
UPDATE PurchaseMaterial
SET y_chcc_result = 'PASS'
WHERE id = :NEW.purchase_material_id;
ELSIF :NEW.chcc = 'failed' THEN
UPDATE PurchaseMaterial
SET y_chcc_result = 'FAIL'
WHERE id = :NEW.purchase_material_id;
END IF;
-- 处理CPSIA判定结果
IF :NEW.cpsia IN ('qualified', 'qualified_conditional') THEN
UPDATE PurchaseMaterial
SET y_cpsia_result = 'PASS'
WHERE id = :NEW.purchase_material_id;
ELSIF :NEW.cpsia = 'failed' THEN
UPDATE PurchaseMaterial
SET y_cpsia_result = 'FAIL'
WHERE id = :NEW.purchase_material_id;
END IF;
-- 处理OEKO-TEX判定结果
IF :NEW.oeko_tex IN ('qualified', 'qualified_conditional') THEN
UPDATE PurchaseMaterial
SET y_oeko_tex_result = 'PASS'
WHERE id = :NEW.purchase_material_id;
ELSIF :NEW.oeko_tex = 'failed' THEN
UPDATE PurchaseMaterial
SET y_oeko_tex_result = 'FAIL'
WHERE id = :NEW.purchase_material_id;
END IF;
-- 处理AFIRM判定结果
IF :NEW.afirm IN ('qualified', 'qualified_conditional') THEN
UPDATE PurchaseMaterial
SET y_afirm_result = 'PASS'
WHERE id = :NEW.purchase_material_id;
ELSIF :NEW.afirm = 'failed' THEN
UPDATE PurchaseMaterial
SET y_afirm_result = 'FAIL'
WHERE id = :NEW.purchase_material_id;
END IF;
-- 处理bluesign判定结果
IF :NEW.bluesign IN ('qualified', 'qualified_conditional') THEN
UPDATE PurchaseMaterial
SET y_bluesign_result = 'PASS'
WHERE id = :NEW.purchase_material_id;
ELSIF :NEW.bluesign = 'failed' THEN
UPDATE PurchaseMaterial
SET y_bluesign_result = 'FAIL'
WHERE id = :NEW.purchase_material_id;
END IF;
END;
/
```
### 关键设计说明：
1. **触发时机**：
```sql
AFTER INSERT ON InspectInfo
FOR EACH ROW
```
- 在`InspectInfo`表插入记录后触发
- `FOR EACH ROW`确保每条新记录都会处理
2. **表关联设计**：
```sql
WHERE id = :NEW.purchase_material_id
```
- 假设`InspectInfo`表中有`purchase_material_id`字段关联到`PurchaseMaterial`表
- 如实际关联字段不同，请替换为正确的关联字段名
3. **属性映射**：
| 需求属性名 | 数据库字段名 |
|------------|--------------|
| REACH SVHC | `reach_svhc` |
| POPs | `pops` |
| RoHS | `rohs` |
| CP65 | `cp65` |
| TSCA | `tsca` |
| CHCC | `chcc` |
| CPSIA | `cpsia` |
| OEKO-TEX standard 100 | `oeko_tex` |
| AFIRM | `afirm` |
| bluesign | `bluesign` |
4. **判定逻辑**：
```sql
IF :NEW.attribute IN ('qualified', 'qualified_conditional') THEN
UPDATE ... SET result = 'PASS'
ELSIF :NEW.attribute = 'failed' THEN
UPDATE ... SET result = 'FAIL'
END IF;
```
- 精确匹配需求中的三种判定状态
- 使用`IN`操作符简化条件判断
### 重要注意事项：
1. **关联字段确认**：
- 代码中使用了`:NEW.purchase_material_id`作为关联字段
- 请根据实际表结构调整该字段名
```sql
-- 查看InspectInfo表结构
DESC InspectInfo;
```
2. **空值处理**：
- 当前逻辑不处理NULL值（需求未要求）
- 如需处理空值，可添加额外条件：
```sql
IF :NEW.reach_svhc IS NULL THEN
UPDATE PurchaseMaterial
SET y_reach_result = NULL
WHERE id = :NEW.purchase_material_id;
END IF;
```
3. **性能优化**：
- 在关联字段上创建索引：
```sql
CREATE INDEX IDX_INSPECTINFO_PMID ON InspectInfo(purchase_material_id);
CREATE INDEX IDX_PURCHASEMATERIAL_ID ON PurchaseMaterial(id);
```
4. **批量插入处理**：
- 当批量插入多条`InspectInfo`记录时，考虑使用BULK COLLECT优化
- 但触发器本身是行级触发，需评估实际数据量
### 测试用例：
```sql
-- 测试用例1：合格判定
INSERT INTO InspectInfo (purchase_material_id, reach_svhc, pops)
VALUES (1001, 'qualified', 'failed');
-- 验证结果
SELECT y_reach_result, y_pops_result
FROM PurchaseMaterial
WHERE id = 1001;
-- 预期: y_reach_result='PASS', y_pops_result='FAIL'
-- 测试用例2：条件合格
INSERT INTO InspectInfo (purchase_material_id, rohs, bluesign)
VALUES (1002, 'qualified_conditional', 'qualified');
-- 验证结果
SELECT y_rohs_result, y_bluesign_result
FROM PurchaseMaterial
WHERE id = 1002;
-- 预期: y_rohs_result='PASS', y_bluesign_result='PASS'
```
### 备选方案（使用CASE语句）：
```sql
-- 更简洁的写法（但需确保所有字段都存在）
UPDATE PurchaseMaterial
SET
y_reach_result = CASE
WHEN :NEW.reach_svhc IN ('qualified','qualified_conditional') THEN 'PASS'
WHEN :NEW.reach_svhc = 'failed' THEN 'FAIL'
ELSE y_reach_result END,
y_pops_result = CASE
WHEN :NEW.pops IN ('qualified','qualified_conditional') THEN 'PASS'
WHEN :NEW.pops = 'failed' THEN 'FAIL'
ELSE y_pops_result END,
-- ...其他字段类似...
WHERE id = :NEW.purchase_material_id;
```
STeP认证是OEKO-TEX为纺织品可持续生产设立的评估体系，涉及化学品管理、环境绩效、环境管理、社会责任、质量管理及职业健康安全六个模块。通过认证的企业表明其产品符合行业生态要求，对提升品牌国际影响力和消费者信任度有积极影响。认证流程包括在线申请、评估、现场审核等步骤，证书有效期为3年。
扫一扫
390
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
