U8+售后服务单使用触发器实现日志记录

编程入门行业动态更新时间:2024-10-11 17:28:17

U8+售后服务单使用<a href=https://www.elefans.com/category/jswz/34/1768873.html style= 触发器实现日志记录"/>

U8+售后服务单使用触发器实现日志记录

文章目录

- 1.描述及效果
- - 1.1 需求描述
  - 1.2 效果
- 2. 思路
- 3. 步骤
- - 3.1 创建一张表用来记录用Inserted和deleted获取的值
  - 3.2 表头触发器
  - 3.3 表体触发器
  - 3.3 配件表触发器
  - 3.5 报表
- 4.总结

1.描述及效果

1.1 需求描述

根据实际需求，监测哪个用户对服务单进行了什么操作（增删改查）
监测服务单中的字段（表头、表体、配件表）
例如服务单编码、状态、存货编码等。

1.2 效果

部署到SSRS即为以下效果

2. 思路

1.可以使用SQL Server触发器实现这个需求
2.表头、表体、配件表是有三张表分别存储，所以需要对三张表都进行触发
3.触发器会有一张Inserted表和deleted表用来记录新值和旧值，用这两张表来取值。
4.关联人员档案等。
5.注意点U8在部署触发器时，需要在首尾加上不返回计数（set nocount on）

3. 步骤

3.1 创建一张表用来记录用Inserted和deleted获取的值

-- 自动增加id、修改类型、修改字段、旧值、新值、变更人、变更单号、操作时间
CREATE TABLE ZZReActionTa (audit_id INT IDENTITY(1, 1) PRIMARY KEY,action VARCHAR(50),field VARCHAR(100),old_value VARCHAR(MAX),new_value VARCHAR(MAX),operator VARCHAR(50),modified_id VARCHAR(50),operate_time DATETIME DEFAULT GETDATE()
);

3.2 表头触发器

-- 服务单表头 
CREATE TRIGGER trigger_SR_ServiceBill --触发器名称
ON SR_ServiceBill -- 触发表名
AFTER UPDATE
AS
set nocount on
--BEGIN-- 服务单编号 cSerBillCodeINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','服务单编号',deleted.cSerBillCode,inserted.cSerBillCode,A =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID),B =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID)FROM insertedINNER JOIN deleted WITH (NOLOCK)ON inserted.ID = deleted.IDWHERE inserted.cSerBillCode <> deleted.cSerBillCode;-- 服务类型 SerTypeIDINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','服务类型',deleted.SerTypeID,inserted.SerTypeID,A =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID),B =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID)FROM insertedINNER JOIN deleted WITH (NOLOCK)ON inserted.ID = deleted.IDWHERE inserted.SerTypeID <> deleted.SerTypeID;/*-- 状态 SerStateIDINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','状态',deleted.SerStateID,inserted.SerStateID,A =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID),B =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID)FROM insertedINNER JOIN deleted WITH (NOLOCK)ON inserted.ID = deleted.IDWHERE inserted.SerStateID <> deleted.SerStateID;
*/-- 服务用户编码 cCusCodeINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','服务用户编码',E =(SELECT cCusAbbNameFROM SR_View_SR_ServiceUserAndCustomer WITH (NOLOCK)WHERE SR_View_SR_ServiceUserAndCustomer.cCusCode = deleted.cCusCode),F =(SELECT cCusAbbNameFROM SR_View_SR_ServiceUserAndCustomer WITH (NOLOCK)WHERE SR_View_SR_ServiceUserAndCustomer.cCusCode = inserted.cCusCode),A =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID),B =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID)FROM insertedINNER JOIN deleted WITH (NOLOCK)ON inserted.ID = deleted.IDWHERE inserted.cCusCode <> deleted.cCusCode;-- 服务用户简称 由服务用户编码带出-- 业务员  cReqPersonCode INSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','业务员',deleted.cReqPersonCode,inserted.cReqPersonCode,A =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID),B =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID)FROM insertedINNER JOIN deleted WITH (NOLOCK)ON inserted.ID = deleted.IDWHERE inserted.cReqPersonCode <> deleted.cReqPersonCode;-- 请求日期 dReqDateINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','请求日期',deleted.dReqDate,inserted.dReqDate,A =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID),B =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID)FROM insertedINNER JOIN deleted WITH (NOLOCK)ON inserted.ID = deleted.IDWHERE inserted.dReqDate <> deleted.dReqDate;-- 预计解决日期 dIntendDateINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','预计解决日期',deleted.dIntendDate,inserted.dIntendDate,A =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID),B =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID)FROM insertedINNER JOIN deleted WITH (NOLOCK)ON inserted.ID = deleted.IDWHERE inserted.dIntendDate <> deleted.dIntendDate;-- 索赔金额  mNatCompensateSumINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','索赔金额',deleted.mNatCompensateSum,inserted.mNatCompensateSum,A =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID),B =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID)FROM insertedINNER JOIN deleted WITH (NOLOCK)ON inserted.ID = deleted.IDWHERE inserted.mNatCompensateSum <> deleted.mNatCompensateSum;-- 部门   cSerDepCodeINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','部门',u =(SELECT dbo.Department.cDepNameFROM dbo.Department WITH (NOLOCK)WHERE dbo.Department.cDepCode = deleted.cSerDepCode),v =(SELECT dbo.Department.cDepNameFROM dbo.Department WITH (NOLOCK)WHERE dbo.Department.cDepCode = inserted.cSerDepCode),--deleted.cReqDepCode,--inserted.cReqDepCode,A =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID),B =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID)FROM insertedINNER JOIN deleted WITH (NOLOCK)ON inserted.ID = deleted.IDWHERE inserted.cSerDepCode <> deleted.cSerDepCode;-- 收费金额 mNatChargeSumINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','收费金额',deleted.mNatChargeSum,inserted.mNatChargeSum,A =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID),B =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID)FROM insertedINNER JOIN deleted WITH (NOLOCK)ON inserted.ID = deleted.IDWHERE inserted.mNatChargeSum <> deleted.mNatChargeSum;-- 请求编码 SerRequestIDINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','请求编码',deleted.SerRequestID,inserted.SerRequestID,A =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID),B =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID)FROM insertedINNER JOIN deleted WITH (NOLOCK)ON inserted.ID = deleted.IDWHERE inserted.SerRequestID <> deleted.SerRequestID;-- 备注 cExplainINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','备注',deleted.cExplain,inserted.cExplain,A =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID),B =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.ID = deleted.ID)FROM insertedINNER JOIN deleted WITH (NOLOCK)ON inserted.ID = deleted.IDWHERE inserted.cExplain <> deleted.cExplain;
--END;set nocount off

3.3 表体触发器

-- 服务单表体
CREATE TRIGGER trigger_SR_ServiceBillDetail --触发器名称
ON SR_ServiceBillDetail -- 触发表名
AFTER UPDATE
AS
--begin
set nocount on-- 存货编码 cInvCodeINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','存货编码',deleted.cInvCode,inserted.cInvCode,C =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id)),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.id = deleted.idWHERE inserted.cInvCode <> deleted.cInvCode;-- 数量 fQuantityINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','数量',deleted.fQuantity,inserted.fQuantity,C =(SELECT cModifierFROM SR_ServiceBill WITH (nolock)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (nolock)WHERE dbo.SR_ServiceBillDetail.id = deleted.id)),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (nolock)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (nolock)WHERE dbo.SR_ServiceBillDetail.id = deleted.id))FROM inserted WITH (nolock)INNER JOIN deleted WITH (nolock)ON inserted.id = deleted.idWHERE inserted.fQuantity <> deleted.fQuantity;-- 上门/返厂 SerModeIDINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','上门/返厂',deleted.SerModeID,inserted.SerModeID,C =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id)),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.id = deleted.idWHERE inserted.SerModeID <> deleted.SerModeID;-- 批号 cBatchINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','批号',deleted.cBatch,inserted.cBatch,C =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id)),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.id = deleted.idWHERE inserted.cBatch <> deleted.cBatch;-- 保修期截止日 bRepairINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','保修期截止日',deleted.bRepair,inserted.bRepair,C =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id)),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.id = deleted.idWHERE inserted.bRepair <> deleted.bRepair;-- 到厂日期 dArriveDateINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','到厂日期',deleted.dArriveDate,inserted.dArriveDate,C =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id)),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.id = deleted.idWHERE inserted.dArriveDate <> deleted.dArriveDate;-- 负责人名称 cSerPrincipalCodeINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','负责人名称',deleted.dArriveDate,inserted.dArriveDate,C =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id)),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.id = deleted.idWHERE inserted.dArriveDate <> deleted.dArriveDate;-- 部门  cSerDepCodeINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','部门',u =(SELECT dbo.Department.cDepNameFROM dbo.Department WITH (NOLOCK)WHERE dbo.Department.cDepCode = deleted.cSerDepCode),v =(SELECT dbo.Department.cDepNameFROM dbo.Department WITH (NOLOCK)WHERE dbo.Department.cDepCode = inserted.cSerDepCode),--deleted.cSerDepCode,--inserted.cSerDepCode,C =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id)),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.id = deleted.idWHERE inserted.cSerDepCode <> deleted.cSerDepCode;-- 解决状态 cIntendStatusINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','解决状态',deleted.cIntendStatus,inserted.cIntendStatus,C =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id)),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.id = deleted.idWHERE inserted.cIntendStatus <> deleted.cIntendStatus;-- 故障描述 cFailureMemoINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','故障描述',deleted.cFailureMemo,inserted.cFailureMemo,C =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id)),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.id = deleted.idWHERE inserted.cFailureMemo <> deleted.cFailureMemo;-- 解决情况 cIntendInstanceINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','解决情况',deleted.cIntendInstance,inserted.cIntendInstance,C =(SELECT cModifierFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id)),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceBillDetail.SerBillIDFROM dbo.SR_ServiceBillDetail WITH (NOLOCK)WHERE dbo.SR_ServiceBillDetail.id = deleted.id))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.id = deleted.idWHERE inserted.cIntendInstance <> deleted.cIntendInstance;--end
set nocount off

3.3 配件表触发器

-- 服务配件表  
CREATE TRIGGER trigger_SR_ServiceItems --触发器名称
ON SR_ServiceItems -- 触发表名  表里面只有有修改人，不需要再关联Bills
AFTER UPDATE
AS
--BEGIN
set nocount on-- 保修期 bRepairINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','保修期',deleted.bRepair,inserted.bRepair,C =(SELECT cModifierFROM SR_ServiceItems WITH (NOLOCK)WHERE SR_ServiceItems.AutoID = deleted.AutoID),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceItems.SerBillIDFROM dbo.SR_ServiceItems WITH (NOLOCK)WHERE dbo.SR_ServiceItems.AutoID = deleted.AutoID))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.AutoID = deleted.AutoIDWHERE inserted.bRepair <> deleted.bRepair;-- 付款属性 cTypeClassINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','付款属性',deleted.cTypeClass,inserted.cTypeClass,C =(SELECT cModifierFROM SR_ServiceItems WITH (NOLOCK)WHERE SR_ServiceItems.AutoID = deleted.AutoID),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceItems.SerBillIDFROM dbo.SR_ServiceItems WITH (NOLOCK)WHERE dbo.SR_ServiceItems.AutoID = deleted.AutoID))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.AutoID = deleted.AutoIDWHERE inserted.cTypeClass <> deleted.cTypeClass;-- 项目/配件编码 cInvCodeINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','项目/配件编码',deleted.cInvCode,inserted.cInvCode,C =(SELECT cModifierFROM SR_ServiceItems WITH (NOLOCK)WHERE SR_ServiceItems.AutoID = deleted.AutoID),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceItems.SerBillIDFROM dbo.SR_ServiceItems WITH (NOLOCK)WHERE dbo.SR_ServiceItems.AutoID = deleted.AutoID))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.AutoID = deleted.AutoIDWHERE inserted.cInvCode <> deleted.cInvCode;-- 项目/配件名称 由配件编码带出-- 数量 fQuantityINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','配件数量',deleted.fQuantity,inserted.fQuantity,C =(SELECT cModifierFROM SR_ServiceItems WITH (nolock)WHERE SR_ServiceItems.AutoID = deleted.AutoID),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (nolock)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceItems.SerBillIDFROM dbo.SR_ServiceItems WITH (nolock)WHERE dbo.SR_ServiceItems.AutoID = deleted.AutoID))FROM inserted WITH (nolock)INNER JOIN deleted WITH (nolock)ON inserted.AutoID = deleted.AutoIDWHERE inserted.fQuantity <> deleted.fQuantity;-- 报价 mQuotedUnitPriceINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','报价',deleted.mQuotedUnitPrice,inserted.mQuotedUnitPrice,C =(SELECT cModifierFROM SR_ServiceItems WITH (NOLOCK)WHERE SR_ServiceItems.AutoID = deleted.AutoID),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceItems.SerBillIDFROM dbo.SR_ServiceItems WITH (NOLOCK)WHERE dbo.SR_ServiceItems.AutoID = deleted.AutoID))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.AutoID = deleted.AutoIDWHERE inserted.mQuotedUnitPrice <> deleted.mQuotedUnitPrice;-- 含税单价 mTaxUnitPriceINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','含税单价',deleted.mTaxUnitPrice,inserted.mTaxUnitPrice,C =(SELECT cModifierFROM SR_ServiceItems WITH (NOLOCK)WHERE SR_ServiceItems.AutoID = deleted.AutoID),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceItems.SerBillIDFROM dbo.SR_ServiceItems WITH (NOLOCK)WHERE dbo.SR_ServiceItems.AutoID = deleted.AutoID))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.AutoID = deleted.AutoIDWHERE inserted.mTaxUnitPrice <> deleted.mTaxUnitPrice;-- 税率 fTaxRateINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','税率',deleted.fTaxRate,inserted.fTaxRate,C =(SELECT cModifierFROM SR_ServiceItems WITH (NOLOCK)WHERE SR_ServiceItems.AutoID = deleted.AutoID),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceItems.SerBillIDFROM dbo.SR_ServiceItems WITH (NOLOCK)WHERE dbo.SR_ServiceItems.AutoID = deleted.AutoID))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.AutoID = deleted.AutoIDWHERE inserted.fTaxRate <> deleted.fTaxRate;-- 价税合计 mTotalSumINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','价税合计',deleted.mTotalSum,inserted.mTotalSum,C =(SELECT cModifierFROM SR_ServiceItems WITH (NOLOCK)WHERE SR_ServiceItems.AutoID = deleted.AutoID),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceItems.SerBillIDFROM dbo.SR_ServiceItems WITH (NOLOCK)WHERE dbo.SR_ServiceItems.AutoID = deleted.AutoID))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.AutoID = deleted.AutoIDWHERE inserted.mTotalSum <> deleted.mTotalSum;-- 存货条码 cInvCodeINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','存货条码',deleted.cInvCode,inserted.cInvCode,C =(SELECT cModifierFROM SR_ServiceItems WITH (NOLOCK)WHERE SR_ServiceItems.AutoID = deleted.AutoID),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceItems.SerBillIDFROM dbo.SR_ServiceItems WITH (NOLOCK)WHERE dbo.SR_ServiceItems.AutoID = deleted.AutoID))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.AutoID = deleted.AutoIDWHERE inserted.cInvCode <> deleted.cInvCode;-- 应收金额 fDrawJSSumINSERT INTO ZZActionTa(action,field,old_value,new_value,operator,modified_id)SELECT 'UPDATE','应收金额',deleted.fDrawJSSum,inserted.fDrawJSSum,C =(SELECT cModifierFROM SR_ServiceItems WITH (NOLOCK)WHERE SR_ServiceItems.AutoID = deleted.AutoID),D =(SELECT cSerBillCodeFROM SR_ServiceBill WITH (NOLOCK)WHERE SR_ServiceBill.OID =(SELECT dbo.SR_ServiceItems.SerBillIDFROM dbo.SR_ServiceItems WITH (NOLOCK)WHERE dbo.SR_ServiceItems.AutoID = deleted.AutoID))FROM inserted WITH (NOLOCK)INNER JOIN deleted WITH (NOLOCK)ON inserted.AutoID = deleted.AutoIDWHERE inserted.fDrawJSSum <> deleted.fDrawJSSum;
--END;
set nocount off

3.5 报表

SELECT audit_id 序号,
CASEWHEN action = 'update' THEN'修改'ELSE'null'END AS 操作类型,field 操作字段,-- old_value 修改前的值,CASEWHEN field = '服务类型'AND old_value = '01' THEN'保内'WHEN field = '服务类型'AND old_value = '02' THEN'保外'WHEN field = '上门/返厂'AND old_value = '01' THEN'上门'WHEN field = '上门/返厂'AND old_value = '02' THEN'整机返厂'WHEN field = '上门/返厂'AND old_value = '03' THEN'部件返厂'WHEN field = '上门/返厂'AND old_value = '04' THEN'返厂无订单'WHEN field = '存货条码'AND old_value = '01' THEN'期货'WHEN field = '存货条码'AND old_value = '02' THEN'现货'WHEN field = '保修期'AND old_value = '0' THEN'否'WHEN field = '保修期'AND old_value = '1' THEN'是'WHEN field = '付款属性'AND old_value = '02' THEN'收费'WHEN field = '付款属性'AND old_value = '03' THEN'免费'WHEN field = '付款属性'AND old_value = '04' THEN'索赔'WHEN field = '解决状态'AND old_value = '0' THEN'待处理'WHEN field = '解决状态'AND old_value = '1' THEN'执行'WHEN field = '解决状态'AND old_value = '2' THEN'完工'ELSEold_valueEND AS 修改前的值,--new_value 修改后的值,CASEWHEN field = '服务类型'AND new_value = '01' THEN'保内'WHEN field = '服务类型'AND new_value = '02' THEN'保外'WHEN field = '上门/返厂'AND new_value = '01' THEN'上门'WHEN field = '上门/返厂'AND new_value = '02' THEN'整机返厂'WHEN field = '上门/返厂'AND new_value = '03' THEN'部件返厂'WHEN field = '上门/返厂'AND new_value = '04' THEN'返厂无订单'WHEN field = '存货条码'AND new_value = '01' THEN'期货'WHEN field = '存货条码'AND new_value = '02' THEN'现货'WHEN field = '保修期'AND new_value = '0' THEN'否'WHEN field = '保修期'AND new_value = '1' THEN'是'WHEN field = '付款属性'AND new_value = '02' THEN'收费'WHEN field = '付款属性'AND new_value = '03' THEN'免费'WHEN field = '付款属性'AND new_value = '04' THEN'索赔'WHEN field = '解决状态'AND new_value = '0' THEN'待处理'WHEN field = '解决状态'AND new_value = '1' THEN'执行'WHEN field = '解决状态'AND new_value = '2' THEN'完工'ELSEnew_valueEND AS 修改后的值,operator 操作账户,modified_id 单据编号,operate_time 操作时间
FROM dbo.ZZActionTa;