SqlServer触发器的应用

更新事务类似于在删除之后执行插入；首先旧行被复制到 deleted 表中，然后新行被复制到触发器表和 inserted 表中。

 

1.插入操作(Insert)

      Inserted表有数据，Deleted表无数据

2.删除操作(Delete)

      Inserted表无数据，Deleted表有数据

3.更新操作(Update)

      Inserted表有数据(新数据)，Deleted表有数据(旧数据)

 

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

 

create trigger [TG_SIMCARDRESOURCE_UPDATE] on [dcms_hy15].[SIM_CARD_RESOURCE_]   
for update    
as    
begin  
	declare @OLD_LOCKED varchar(20), @NEW_LOCKED varchar(20)    
    set @OLD_LOCKED=(select IS_LOCKED_ from DELETED)   
    set @NEW_LOCKED=(select IS_LOCKED_ from INSERTED)  
	if(@NEW_LOCKED='N')
	begin
		update dcms_hy15.SIM_CARD_RESOURCE2_ set org_id_=b.org_id_,commission_id_=b.commission_id_,
			packer_=b.packer_,amount_=b.amount_,is_locked_=b.is_locked_,
			locked_request_=b.locked_request_,plan_commission_=b.plan_commission_,
			msisdn_=b.msisdn_,big_box_id_=b.big_box_id_,little_box_id_=b.little_box_id_,
			zha_id_=b.zha_id_,is_ready_overdue_=b.is_ready_overdue_,
			is_warning_=b.is_warning_,input_date_time_=b.input_date_time_,
			sales_date_time_=b.sales_date_time_,active_date_=b.active_date_,
			status_date_=b.status_date_,wait_back_date_=b.wait_back_date_,
			call_back_date_=b.call_back_date_,is_integral_=b.is_integral_,
			is_active_=b.is_active_,request_id_=b.request_id_,
			status_=b.status_,life_status_=b.life_status_,
			original_box_=b.original_box_,original_case_=b.original_case_,
			original_package_=b.original_package_,old_card_=b.old_card_,
			last_updated_stamp_=b.last_updated_stamp_,last_updated_tx_stamp_=b.last_updated_tx_stamp_
		from dcms_hy15.SIM_CARD_RESOURCE2_ a,INSERTED b
		where a.id_=b.id_
	end
end