34,588
社区成员
发帖
与我相关
我的任务
分享
create trigger t_update_jobcity on jobcity
for update
as
begin
if update(JobID)
begin
update JobCityStatistics set jobid=a.jobid from JobCityStatistics b inner join inserted a on a.cityid=b.cityid
end
if update(cityid)
begin
update JobCityStatistics set cityid=a.cityid from JobCityStatistics b inner join inserted a on a.jobid=b.jobid
end
end
JobID CityID
1 3
JobID CityID CreateTime
1 3 2008-01-11 15:46:20.987
--再建个
create trigger trigger_name_insert
on JobCity
for insert
as
begin
insert into JobCityStatistics
select JobID ,CityID,dt=getdate() from inserted
end
create table jobcity(JobID int,CityID int)
create table JobCityStatistics(JobID int,CityID int,CreateTime datetime default getdate())
create trigger tr_jobcity
on jobcity
for insert,update
as
begin
if exists(select 1 from inserted a,deleted b where a.jobid=b.jobid)
update JobCityStatistics set cityid=b.cityid from JobCityStatistics a,inserted b where a.JobID=b.JobID
else
insert into JobCityStatistics(JobID,CityID) select JobID,CityID from jobcity
end
insert into jobcity select 1,2
update jobcity set cityid=3 where jobid=1
select * from jobcity
select * from JobCityStatistics
create trigger trigger_name
on JobCity
for update
as
begin
if update(JobId)
update Jobcity
set JobId= a.JobId,createtime = getedate()
from JobCitystatistics a
left join inserted i on a.cityid = i.cityid
else
update Jobcity
set cityid = a.cityid ,createtime = getedate()
from JobCitystatistics a
left join inserted i on a.JobId= i.JobId
end
create trigger trigger_name
on JobCity
for update
as
begin
if update(JobId)
update Jobcity
set cityid = a.cityid,createtime = getedate()
from JobCitystatistics a
left join inserted i on a.JobId= i.JobId
else
update Jobcity
set JobId= a.JobId,createtime = getedate()
from JobCitystatistics a
left join inserted i on a.cityid = i.cityid
end
create trigger t_insert_jobcity on jobcity
for insert
as
begin
insert into JobCityStatistics select JobID ,CityID,dt=getdate() from inserted
end