SQL Server使用trigger实现自动填充字段值

用户需要某个字段自动填充，但是如果在SAPB1内使用format search实现，则需要用某个字段进行联动，这种联动是事后的，需要用户点击放大镜来实现，所以抱怨很多。

所以，在数据库中使用trigger来触发是一个好的办法。

if (object_id('trigger_PO_Insert', 'tr') is not null)
drop trigger trigger_PO_Insert
go

create trigger trigger_PO_Insert
on OPOR -- 指定创建触发器的表
after insert -- insert 触发器
as

set nocount on;

declare @IPOX varchar(20), declare @DocEntry int

select @DocEntry = DocEntry from inserted
select @IPOX = 'IPOX' + convert(varchar(20), @DocEntry + 999) --IPOX字符串拼接

update OPOR set NumAtCard= @IPOX where @DocEntry = DocEntry
go

 另一个实例，给应收发票自动添加memo

if (object_id('trigger_OINV_Insert', 'tr') is not null)
    drop trigger trigger_OINV_Insert
go
create trigger trigger_OINV_Insert
on OINV -- 指定创建触发器的表
after insert ,update-- insert 触发器
as

set nocount on;

declare @numatcard varchar(100)
    , @JrnlMemo varchar(100)
    , @DocEntry int
    --, @DocName varchar(10)
declare @temp01 table(
    cardcode varchar(100),
    U_CustomerRefNo varchar(100),
    U_BatchNo varchar(100)
    )

select @DocEntry 
= DocEntry from inserted
--=10788 
--
insert into @temp01 
select  '应收发票 - '+ inv1.basecard cardcode,inv1.U_CustomerRefNo,inv1.U_BatchNo
from inv1
where inv1.DocEntry =  @DocEntry /*数据源*/

set @numatcard = (select top 1 U_CustomerRefNo from @temp01)

set @JrnlMemo = (
    select  
    top(1) cardcode+'-'+ U_CustomerRefNo+'-'+
    stuff((
        select '-'+U_BatchNo  from @temp01
        for xml path('')),1,1,''
    )
    from @temp01
)

update OINV set JrnlMemo= @JrnlMemo, NumAtCard=@numatcard where @DocEntry = DocEntry
go

 另一个实例，给应收发票的日记账分录自动添加memo

IF OBJECT_ID ('[trigger_OJDT_Insert]','TR') IS NOT NULL
   DROP TRIGGER [trigger_OJDT_Insert] 
GO

CREATE trigger [trigger_OJDT_Insert]
on [dbo].[OJDT] -- 指定创建触发器的表
after insert ,update-- insert,update 触发器
as

set nocount on;

declare @Memo varchar(100), @Transid int, @DocNum int
, @transtype int, @DocEntry int
declare @temp01 table(
    cardcode varchar(100),
    U_CustomerRefNo varchar(100),
    U_BatchNo varchar(100)
    )
select @Transid = ins.TransId
,@DocNum = ins.BaseRef  
,@transtype = ins.TransType 
from inserted ins 
inner join oinv oi on oi.docnum = ins.BaseRef

insert into @temp01
select  '应收发票 - '+ inv1.basecard cardcode,inv1.U_CustomerRefNo,inv1.U_BatchNo
from inv1
where inv1.DocEntry =  @DocEntry /*数据源*/

set @Memo = (
    select
    top(1) cardcode+'-'+ U_CustomerRefNo+'-'+
    stuff((
        select '-'+U_BatchNo  from @temp01
        for xml path('')),1,1,''
    )
    from @temp01
)
if (@transtype = 13)
    begin
        update OJDT set Memo= @Memo where @Transid = TransId
    --print @Memo
    end

go