触发器里主子表语句

declare @DLID int --发货单id
declare @clnvCode varchar(100) --发货单子表物料代码
declare @iverifystate int --审核状态
declare @order_no varchar(50) --订单号
declare @fahuo_no varchar(60) -- 发货单号
declare @ywy varchar(100) --业务员
declare @faddress varchar(255) --地址
declare @fcontact varchar(100) --联系电话
declare @khname varchar(200) --联系人
declare @fcontent varchar(255) --服务内容（取备注）
declare @machine_type varchar(255) --机器型号
declare @machine_name varchar(255) --存货名称
declare @fnumber decimal --数量
declare @farea varchar(100) --区域
declare @shangjia varchar(255) --经销商
declare @jd_date varchar(50)
declare @yuyue_date date --预约日期

declare @cPersonCode varchar(50)

declare @cInvCode varchar(50)

select @DLID = DLID from inserted --主键
select @cInvCode =cInvCode from DispatchLists where DLID=@DLID --发货单子表物料代码
select @iverifystate=iverifystate from inserted --审核状态
select @order_no=cSOCode from inserted --订单号
select @fahuo_no =cDLCode from inserted --发货单号
select @ywy =cDefine13 from inserted --业务员
select @faddress=cShipAddress from inserted --地址
select @fcontact=cDefine12 from inserted --联系电话
select @khname = cDefine2 from inserted --联系人
select @fcontent = cMemo from inserted --服务内容
select @machine_type =cInvStd from Inventory where cInvCode = @cInvCode --机器型号
select @machine_name =cInvName from Inventory where cInvCode = @cInvCode --机器名称
select @fnumber =iQuantity from DispatchLists where DLID=@DLID --数量
select @farea =cDefine9 from inserted --区域

select @yuyue_date = cDefine6 from inserted -- 预约日期
select @shangjia = cDefine14 from inserted --经销商

if update(iverifystate) and (@iverifystate = 2)

begin
if exists(select 1 from zz_paidan where order_no=@order_no)
begin
delete from zz_paidan where order_no =@order_no;
end

insert into zz_paidan(order_no,fahuo_no,wlcode,farea,shangjia,faddress,yuyue_date,fcontact,fkhname,ywy,fcontent,machine_type,machine_name,fnumber,order_time,fstatus,from_type)
 
SELECT @order_no,@fahuo_no,d.cInvCode,@farea,@shangjia,@faddress,@yuyue_date,@fcontact,@khname,@ywy,@fcontent,v.cInvStd,v.cInvName,d.iQuantity,GETDATE(),0,'实业' FROM DispatchLists d inner join Inventory v on d.cInvCode=v.cInvCode WHERE dlid=@DLID and( d.cInvCode='004.070.001.010' or SUBSTRING(d.cInvCode,1,3) in (002,003));

 

 

其中子表 多条的话，select 后面需要是表里的字段，，多个表的话，就关联取值