这样简单点
if exists (select * from inserted) and not exists (select * from deleted) --insert
begin
。。。。。。
end
else if exists (select * from deleted) and not exists (select * from inserted) --DELETE
begin
。。。。。。。。
end
CREATE TRIGGER dbo.tr_insertpaydis ON dbo.joinman
FOR update,insert AS
--------------------------------------人员有新增加时侧新增加
BEGIN
begin
declare @rows int
select @rows = @@rowcount
if @rows = 0
return
--------------------以下为新增加时
INSERT INTO paydis
SELECT inserted.ucode,inserted.pcode,inserted.thisyear,
isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-01-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0),
0,
1990-1-1,
isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-02-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0),
0,
1990-1-1,
isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-03-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0),
0,
1990-1-1,
isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-04-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0),
0,
1990-1-1,
isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-05-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0),
0,
1990-1-1,
isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-06-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0),
0,
1990-1-1,
isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-07-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0),
0,
1990-1-1,
isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-08-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0),
0,
1990-1-1,
isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-09-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0),
0,
1990-1-1,
isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-10-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0),
0,
1990-1-1,
isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-11-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0),
0,
1990-1-1,
isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-12-01') as datetime) then inserted.thisyearmoney - round((inserted.thisyearmoney/12),2)*11 else 0 end,2),0) ,
0,
1990-1-1
from inserted ----------这里用什么条件??????????
--------------------------以下为更新时执行
update paydis set
paydis.m1=isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-01-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0) ,
paydis.m2=isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-02-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0) ,
paydis.m3=isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-03-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0) ,
paydis.m4=isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-04-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0) ,
paydis.m5=isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-05-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0) ,
paydis.m6=isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-06-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0) ,
paydis.m7=isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-07-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0) ,
paydis.m8=isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-08-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0) ,
paydis.m9=isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-09-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0) ,
paydis.m10=isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-10-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0) ,
paydis.m11=isnull(round(case when inserted.jiondate<=cast ((str(year(inserted.thisyear)) +'-11-01') as datetime) then inserted.thisyearmoney/12 else 0 end,2),0) ,
paydis.m12=isnull(round(case when jiondate<=cast ((str(year(inserted.thisyear)) +'-12-01') as datetime) then thisyearmoney - round((inserted.thisyearmoney/12),2)*11 else 0 end,2),0)
from paydis,inserted
where inserted.pcode=paydis.pcode and inserted.thisyear=paydis.thisyear ----------这里用什么条件???
end
if @@error <> 0
begin
RAISERROR ('error', 16, 1)
rollback transaction
return
end