一、insert触发器----------------
create trigger tr_COMMISSIONINFO_INSERT on COMMISSION_INFO_
for insert
as
begin
declare @COMMISSIONID varchar(20),@DATE datetime
set @COMMISSIONID=(select ID_ from INSERTED)
set @DATE=getdate()
insert into COOPERATE_YEARS_(COMMISSION_ID_, CONTRACT_START_DATE_, RE_CONTRACT_START_DATE_) VALUES(@COMMISSIONID, @DATE, @DATE)
end
二、update触发器
create trigger tr_COMMISSIONINFO_UPDATE on COMMISSION_INFO_
for update
as
begin
declare @COMMISSIONID varchar(20), @OLD_COMM_CATEGORY int, @NEW_COMM_CATEGORY int, @DATE datetime
set @COMMISSIONID=(select ID_ from INSERTED)
set @OLD_COMM_CATEGORY=(select COMM_CAGEGORY_ from DELETED)
set @NEW_COMM_CATEGORY=(select COMM_CAGEGORY_ from INSERTED)
set @DATE=getdate()
if((@OLD_COMM_CATEGORY<103 or @OLD_COMM_CATEGORY>106) and (@NEW_COMM_CATEGORY>=103 and @NEW_COMM_CATEGORY<=106))
begin
if exists(select 1 from COOPERATE_YEARS_ where COMMISSION_ID_=@COMMISSIONID)
begin
update COOPERATE_YEARS_ set RE_CONTRACT_START_DATE_=@DATE where COMMISSION_ID_=@COMMISSIONID
end
else
begin
insert into COOPERATE_YEARS_(COMMISSION_ID_, CONTRACT_START_DATE_, RE_CONTRACT_START_DATE_)VALUES(@COMMISSIONID, @DATE, @DATE)
end
end
end