---创建联系人表由work,education,language 触发集合数据create table demo( [id] int primary key identity(1,1) ,cvid int default(0) ---候选人id ,truename nvarchar(50) --真实姓名 ,gender varchar(50) --性别 ,birth datetime ---出生日期 ,worktime varchar(50)--工作年限 ,comfrom varchar(50) --来源 ,joblevel varchar(100) --现职位级别 ,address nvarchar(200)---现居住地 ,jobtrade varchar(50) ---现在工作行业 ,jobcategory varchar(100)--现在工作职位 ,jobaddress varchar(100)---期望工作地点 ,jobqwtrade varchar(100) --期望工作行业 ,jobqwcategory varchar(150) --期望工作职位 ,job_qwtype varchar(50) --工作性质 ,tel1 varchar(50) --电话号码 ,tel2 varchar(50) ,tel3 varchar(50) ,intime datetime --发布时间 ,specialty varchar(1000) ---专业 ---->education ,degree varchar(1000) --学历 ----->education ,company varchar(1000) --公司 ---->work ,language varchar(1000) ---语言---->language)select * from demo----职位合并xm ,cvsession create table janc( [id] int primary key identity(1,1) ,cvid int default(0) ---侯选人id ,xmid int default(0)---职位id ,jobname nvarchar(100) ---职位名称 ,cvsession nvarchar(50)---职位申请状态 ,cvsession1 nvarchar(50) ---星级 ,type int default(1) --活动/拒绝)drop table janc---work 触发器ALTER trigger triworkon workafter insert ,update ,delete as declare @cp varchar(100) declare @cp1 varchar(100) declare @cvid int begin if Exists(Select 1 From deleted) AND NOT Exists(Select 1 From inserted) begin -- 触发删除 select @cp=company ,@cvid=cvid from deleted
if exists(select cvid from demo where cvid=@cvid) begin update demo set company=replace(isnull(company,''),','+@cp,'') where cvid=@cvid end end else IF NOT Exists(Select 1 From deleted) AND Exists(Select 1 From inserted) begin -- 触发插入 select @cp=company ,@cvid=cvid from inserted if exists(select cvid from demo where cvid=@cvid) begin update demo set company=isnull(company,'')+','+@cp where cvid=@cvid
end end else IF Exists(Select 1 From deleted) AND Exists(Select 1 From inserted) begin -- 触发更新 select @cp=company ,@cvid=cvid from inserted select @cp1=company from deleted if exists(select cvid from demo where cvid=@cvid) begin update demo set company=replace(isnull(company,''),','+@cp1,','+@cp) where cvid=@cvid end end end ---test----delete trigger triworkinsert work values(getdate(),getdate(),'似的看见防空洞','s','ss','sss','sss','sss','aaa',getdate(),123925)select * from demodelete from work where cvid=123925select * from work where company like '%似的看见防空洞%' update work set company='aaaaa' where id=3418 ----test--------education 触发器create trigger trieducationon educationafter insert ,update ,delete as declare @deg varchar(100) declare @deg1 varchar(100) declare @sp varchar(50) declare @sp1 varchar(50) declare @cvid int begin if Exists(Select 1 From deleted) AND NOT Exists(Select 1 From inserted) begin -- 触发删除 select @sp=specialty ,@deg=degree,@cvid=cvid from deleted
if exists(select cvid from demo where cvid=@cvid) begin update demo set specialty=replace(isnull(specialty,''),','+@sp,''),degree=replace(isnull(degree,''),','+@deg,'') where cvid=@cvid end end else IF NOT Exists(Select 1 From deleted) AND Exists(Select 1 From inserted) begin -- 触发插入 select @sp=specialty ,@deg=degree,@cvid=cvid from inserted if exists(select cvid from demo where cvid=@cvid) begin update demo set specialty=isnull(specialty,'')+','+@sp ,degree=isnull(degree,'')+','+@deg where cvid=@cvid
end end else IF Exists(Select 1 From deleted) AND Exists(Select 1 From inserted) begin -- 触发更新 select @sp=specialty ,@deg=degree ,@cvid=cvid from inserted select @sp1=specialty ,@deg1=degree from deleted if exists(select cvid from demo where cvid=@cvid) begin update demo set specialty=replace(isnull(specialty,''),','+@sp1,','+@sp),degree=replace(isnull(degree,''),','+@deg1,','+@deg) where cvid=@cvid end end end ---test-----insert into education values(getdate(),getdate(),'aaaa','ffffff','博士','','',getdate(),123925)select * from education cvid=123925select * from demodelete from education where id=37update education set specialty='dddddd' where id=36-----test----------
---language 触发器alter trigger trilanguageon languageafter insert ,update ,delete as declare @deg varchar(100) declare @deg1 varchar(100) declare @sp varchar(50) declare @sp1 varchar(50) declare @cvid int begin if Exists(Select 1 From deleted) AND NOT Exists(Select 1 From inserted) begin -- 触发删除 select @sp=kind ,@deg=[level],@cvid=cvid from deleted
if exists(select cvid from demo where cvid=@cvid) begin update demo set language=replace(isnull(language,''),','+@sp+'|'+@deg,'') where cvid=@cvid end end else IF NOT Exists(Select 1 From deleted) AND Exists(Select 1 From inserted) begin -- 触发插入 select @sp=kind ,@deg=[level],@cvid=cvid from inserted if exists(select cvid from demo where cvid=@cvid) begin update demo set language=isnull(language,'')+','+@sp +'|'+@deg where cvid=@cvid
end end else IF Exists(Select 1 From deleted) AND Exists(Select 1 From inserted) begin -- 触发更新 select @sp=kind ,@deg=[level] ,@cvid=cvid from inserted select @sp1=kind ,@deg1=[level] from deleted if exists(select cvid from demo where cvid=@cvid) begin update demo set language=replace(isnull(language,''),','+@sp1+'|'+@deg1,','+@sp+'|'+@deg) where cvid=@cvid end end end ----test---insert into language values('法语','一般','','',getdate(),123925)select * from language cvid=123925select * from demoupdate demo set language='' where cvid=123925delete from language where id=58update language set kind='法语',[level]='好' where id=56----test------select * from cvsession---cvsession 触发器alter trigger tricvsessionon cvsessionafter insert ,update ,delete as declare @cvid int declare @xmid int declare @id int declare @jobname varchar(100) declare @cvsession varchar(50) declare @cvsession1 varchar(50) declare @type intbegin if Exists(Select 1 From deleted) AND NOT Exists(Select 1 From inserted) begin -- 触发删除 select @xmid=xmid,@cvid=cvid from deleted
delete from janc where cvid=@cvid and xmid=@xmid
end else IF NOT Exists(Select 1 From deleted) AND Exists(Select 1 From inserted) begin -- 触发插入 select @xmid=xmid,@cvsession=cvsession,@cvsession1=cvsession1,@type=type,@cvid=cvid from inserted select @jobname=jobname from xm where [id]=@xmid insert into janc values(@cvid,@xmid,@jobname,@cvsession ,@cvsession1,@type) end else IF Exists(Select 1 From deleted) AND Exists(Select 1 From inserted) begin -- 触发更新 select @id=[id],@xmid=xmid,@cvsession=cvsession,@cvsession1=cvsession1,@type=type,@cvid=cvid from inserted update janc set cvid=@cvid, xmid=@xmid,cvsession=@cvsession,cvsession1=@cvsession1,type=@type where cvid=@cvid and xmid=@xmid endend ----test------------select * from cvsessionselect top 1 * from xmselect * from demoinsert into cvsession values(123925,20,20,22,'sssss','db',234,getdate(),'ss',getdate(),0,0)select * from jancdelete from cvsession where id=173152update cvsession set cvsession='aaaaa' where id=173152------test-------------cvresume 触发器create trigger tricvresumeon cvresumeafter insert ,update ,delete as declare @id int declare @cvid int declare @truename nvarchar(50) --真实姓名 declare @gender varchar(50) --性别 declare @birth datetime ---出生日期 declare @worktime varchar(50)--工作年限 declare @comfrom varchar(50) --来源 declare @joblevel varchar(100) --现职位级别 declare @address nvarchar(200)---现居住地 declare @jobtrade varchar(50) ---现在工作行业 declare @jobcategory varchar(100)--现在工作职位 declare @jobaddress varchar(100)---期望工作地点 declare @jobqwtrade varchar(100) --期望工作行业 declare @jobqwcategory varchar(150) --期望工作职位 declare @job_qwtype varchar(50) --工作性质 declare @tel1 varchar(50) --电话号码 declare @tel2 varchar(50) declare @tel3 varchar(50) declare @intime datetime begin if Exists(Select 1 From deleted) AND NOT Exists(Select 1 From inserted) begin -- 触发删除 select @cvid=id from deleted delete from demo where cvid=@cvid
end else IF NOT Exists(Select 1 From deleted) AND Exists(Select 1 From inserted) begin -- 触发插入 select @cvid=id,@truename=truename,@gender=gender,@birth=birth,@worktime=worktime,@comfrom=comfrom,@joblevel=joblevel,@address=address, @jobtrade=jobtrade,@jobcategory=jobcategory,@jobaddress=jobaddress,@jobqwtrade=jobqwtrade,@jobqwcategory=jobqwcategory,@job_qwtype=job_qwtype,@tel1=tel1,@tel2=tel2,@tel3=tel3,@intime=intime from inserted insert into demo (cvid,truename,gender,birth,worktime,comfrom,joblevel,address,jobtrade,jobcategory,jobaddress,jobqwtrade,jobqwcategory,job_qwtype,tel1,tel2,tel3,intime) values(@cvid,@truename,@gender,@birth,@worktime,@comfrom,@joblevel,@address,@jobtrade,@jobcategory,@jobaddress,@jobqwtrade,@jobqwcategory,@job_qwtype,@tel1,@tel2,@tel3,@intime) end else IF Exists(Select 1 From deleted) AND Exists(Select 1 From inserted) begin -- 触发更新 select @cvid=id,@truename=truename,@gender=gender,@birth=birth,@worktime=worktime,@comfrom=comfrom,@joblevel=joblevel,@address=address, @jobtrade=jobtrade,@jobcategory=jobcategory,@jobaddress=jobaddress,@jobqwtrade=jobqwtrade,@jobqwcategory=jobqwcategory,@job_qwtype=job_qwtype,@tel1=tel1,@tel2=tel2,@tel3=tel3,@intime=intime from inserted update demo set truename=@truename,gender=@gender,birth=@birth,worktime=@worktime,comfrom=@comfrom,joblevel=@joblevel,address=@address, jobtrade=@jobtrade,jobcategory=@jobcategory,jobaddress=@jobaddress,jobqwtrade=@jobqwtrade,jobqwcategory=@jobqwcategory,job_qwtype=@job_qwtype,tel1=@tel1,tel2=@tel2,tel3=@tel3,intime=@intime where cvid=@cvid endend ----test-------delete from cvresume where id=123925select * from demo----test----------xm 触发器create trigger trixmon xmafter update as declare @xmid int declare @jobname nvarchar(50) --职位名称 begin -- 触发更新 select @xmid=id,@jobname=jobname from inserted update janc set jobname=@jobname where xmid=@xmid end
