SqlServer觸發器的應用

一、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