--创建触发器
Alter trigger tbytzc_insert
on t_ysk_ytzcmb
after insert --插入之后触发
as
--定义变量
declare @ytxh varchar(15),@px varchar(20),@cx varchar(250),@fdj varchar(80),@qd varchar(80),@pf varchar(80),@xsbsc varchar(80), @jxsdm varchar(1000),@tbytsql varchar(3000),@jznd varchar(4),@cx_count int,@cx_join varchar(300),@fdj_join varchar(300),@qd_join varchar(300),@pd_join varchar(300),@xsbsc_join varchar(300),@jxsdm_join varchar(600);
--在inserted表中查询已经插入记录信息
select @px = 品系,@cx = 车型,@fdj = 发动机,@qd = 驱动,@pf = 排放,@xsbsc = 销售办事处, @jxsdm = 经销商代码,@jznd=jznd,@ytxh=预提序号 from inserted;
set @tbytsql = 'a.品系部='''+@px+'''';
if(isnull(@cx,'')<>'' )--车型
begin
set @tbytsql = @tbytsql +' and ';
set @cx_join = REPLACE (@cx,'、','%'' or a.车型 like ''%')
set @cx_join = '(a.车型 like ''%'+@cx_join+'%'')';
set @tbytsql = @tbytsql +@cx_join;
if(isnull(@fdj,'')<>'' )--发动机
begin
set @tbytsql = @tbytsql +' and ';
set @fdj_join = REPLACE (@fdj,'、','%'' or a.发动机 like ''%')
set @fdj_join = '(a.发动机 like ''%'+@fdj_join+'%'')';
set @tbytsql = @tbytsql +@fdj_join;
if(isnull(@qd,'')<>'' )--驱动
begin
set @tbytsql = @tbytsql +' and ';
set @qd_join = REPLACE (@qd,'、','%'' or a.驱动 like ''%')
set @qd_join = '(a.驱动 like ''%'+@qd_join+'%'')';
set @tbytsql = @tbytsql +@qd_join;
if(isnull(@pf,'')<>'' )--排放
begin
set @tbytsql = @tbytsql +' and ';
set @pd_join = REPLACE (@pf,'、','%'' or a.排放 like ''%')
set @pd_join = '(a.排放 like ''%'+@pd_join+'%'')';
set @tbytsql = @tbytsql +@pd_join;
if(isnull(@xsbsc,'')<>'' )--销售办事处
begin
set @tbytsql = @tbytsql +' and ';
set @xsbsc_join = REPLACE (@xsbsc,'、','%'' or upper(a.销售办事处) like ''%')
set @xsbsc_join = '(upper(a.销售办事处) like ''%'+@xsbsc_join+'%'')';
set @tbytsql = @tbytsql +@xsbsc_join;
if(isnull(@jxsdm,'')<>'' )--付款方
begin
set @tbytsql = @tbytsql +' and ';
set @jxsdm_join = REPLACE (@jxsdm,'、','%'' or upper(a.付款方) like ''%')
set @jxsdm_join = '(upper(a.付款方) like ''%'+@jxsdm_join+'%'')';
set @tbytsql = @tbytsql +@jxsdm_join;
end
end
end
end
end
end
--更新数据：
update t_ysk_ytzcmb set tbytsql=@tbytsql where jznd=@jznd and 预提序号=@ytxh;
go
