触发器事例

---创建联系人表由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 triwork
on work
after 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 triwork
insert work values(getdate(),getdate(),'似的看见防空洞','s','ss','sss','sss','sss','aaa',getdate(),123925)
select * from demo
delete from work  where cvid=123925
select * from work where company like '%似的看见防空洞%'
 update work set company='aaaaa' where id=3418 
----test-----
---education 触发器
create  trigger trieducation
on education
after 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=123925
select * from demo
delete from education where id=37
update education  set specialty='dddddd' where id=36
-----test----------

---language 触发器
alter  trigger trilanguage
on language
after 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=123925
select * from demo
update demo set language='' where cvid=123925
delete from language where id=58
update language  set kind='法语',[level]='好' where id=56
----test------
select * from cvsession
---cvsession 触发器
alter  trigger tricvsession
on cvsession
after 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 int
begin
 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
   
 end
end
----test------------
select * from cvsession
select top 1 * from xm
select * from demo
insert into cvsession values(123925,20,20,22,'sssss','db',234,getdate(),'ss',getdate(),0,0)
select * from janc
delete from  cvsession where id=173152
update cvsession set cvsession='aaaaa' where  id=173152
------test----------
---cvresume 触发器
create  trigger tricvresume
on cvresume
after 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
   
 end
end
----test-------
delete  from cvresume where id=123925
select * from demo
----test-------
---xm 触发器
create  trigger trixm
on xm
after 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