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

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

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

 1 if (object_id('trigger_PO_Insert', 'tr') is not null)
 2 drop trigger trigger_PO_Insert
 3 go
 4 
 5 create trigger trigger_PO_Insert
 6 on OPOR -- 指定创建触发器的表
 7 after insert -- insert 触发器
 8 as
 9 
10 set nocount on;
11 
12 declare @IPOX varchar(20), declare @DocEntry int
13 
14 select @DocEntry = DocEntry from inserted
15 select @IPOX = 'IPOX' + convert(varchar(20), @DocEntry + 999) --IPOX字符串拼接
16 
17 update OPOR set NumAtCard= @IPOX where @DocEntry = DocEntry
18 go

 另一个实例

 1 if (object_id('trigger_OINV_Insert', 'tr') is not null)
 2     drop trigger trigger_OINV_Insert
 3 go
 4 create trigger trigger_OINV_Insert
 5 on OINV -- 指定创建触发器的表
 6 after insert ,update-- insert 触发器
 7 as
 8 
 9 set nocount on;
10 
11 declare @numatcard varchar(100)
12     , @JrnlMemo varchar(100)
13     , @DocEntry int
14     --, @DocName varchar(10)
15 declare @temp01 table(
16     cardcode varchar(100),
17     U_CustomerRefNo varchar(100),
18     U_BatchNo varchar(100)
19     )
20 
21 select @DocEntry 
22 = DocEntry from inserted
23 --=10788 
24 --
25 insert into @temp01 
26 select  '交货 - '+ inv1.basecard cardcode,inv1.U_CustomerRefNo,inv1.U_BatchNo
27 from inv1
28 where inv1.DocEntry =  @DocEntry /*数据源*/
29 
30 set @numatcard = (select top 1 U_CustomerRefNo from @temp01)
31 
32 set @JrnlMemo = (
33     select  
34     top(1) cardcode+'-'+ U_CustomerRefNo+'-'+
35     stuff((
36         select '-'+U_BatchNo  from @temp01
37         for xml path('')),1,1,''
38     )
39     from @temp01
40 )
41 
42 update OINV set JrnlMemo= @JrnlMemo, NumAtCard=@numatcard where @DocEntry = DocEntry
43 go