I. Insert trigger ----------------
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
Ii. Update trigger
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