触发器的使用简单例子

Create Trigger tmp_translator       --触发器的名字
On t_translator_time                --在t_translator_time表中创建触发器
for Insert                          --为什么事件触发（insert,update,delete）
As                                  --事件触发后所要做的事情
DECLARE @REALNAME varchar(100)      --声明一个变量
SELECT @REALNAME=username From inserted  ----取得登记译者的姓名（系统表inserted，deleted）
Delete tmp_t_translator_detail WHERE realname=@REALNAME --执行删除操作

上面是简单的一个insert触发delete操作。下面是update来触发，update操作

-----下面的触发器
ALTER  Trigger time_translator
on t_translator
for Insert   ----当译者表中插入时
as 
--当前日期的星期天是哪天begin
declare @WeekDay datetime
declare @NowWeekDay nvarchar(20)
set @NowWeekDay=(--当前星期几
select datename(dw,getdate()))
set @WeekDay=(
select
case @NowWeekDay 
when '星期日' then getdate()
when '星期一' then dateadd(day,6,getdate())
when '星期二' then dateadd(day,5,getdate())
when '星期三' then dateadd(day,4,getdate())
when '星期四' then dateadd(day,3,getdate())
when '星期五' then dateadd(day,2,getdate())
when '星期六' then dateadd(day,1,getdate())
end
)
--当前日期的星期天是哪天end
Declare @RREALNAME varchar(100) 
SELECT @RREALNAME=[name] from inserted ----取得登记译者的姓名
begin
    declare @id int
    set @id = (select max(id) from t_translator)
    insert into tmp_t_translator_detail SELECT name, @id,     '', CONVERT(varchar(100), @WeekDay, 23),'上午(9:00 - 11:30)','未填','','未填','','未填','','未填','','未填','','未填','','未填','', major,master_major,master_research,doctor_major,doctor_research,postdoctor_major,postdoctor_research,way_to_charge,phone,home_telephone,mail,mail2,source1,target1,source2,target2,source3,target3,subject11,subject12,subject13,subject21,subject22,subject23,subject31,subject32,subject33,band11,band12,band21,band22,band31,band32,source4,source5,source6,target4,target5,target6,category_professional,category_word,passed,translator_id ,remark1,Product FROM inserted where name = @RREALNAME
    insert into tmp_t_translator_detail SELECT name, @id + 1, '', CONVERT(varchar(100), @WeekDay, 23),'下午(13:00 - 17:00)','未填','','未填','','未填','','未填','','未填','','未填','','未填','', major,master_major,master_research,doctor_major,doctor_research,postdoctor_major,postdoctor_research,way_to_charge,phone,home_telephone,mail,mail2,source1,target1,source2,target2,source3,target3,subject11,subject12,subject13,subject21,subject22,subject23,subject31,subject32,subject33,band11,band12,band21,band22,band31,band32,source4,source5,source6,target4,target5,target6,category_professional,category_word,passed,translator_id ,remark1,Product FROM inserted where name = @RREALNAME
    insert into tmp_t_translator_detail SELECT name, @id + 2, '', CONVERT(varchar(100), @WeekDay, 23),'晚间(17:00 - 次日9:30)','未填','','未填','','未填','','未填','','未填','','未填','','未填','', major,master_major,master_research,doctor_major,doctor_research,postdoctor_major,postdoctor_research,way_to_charge,phone,home_telephone,mail,mail2,source1,target1,source2,target2,source3,target3,subject11,subject12,subject13,subject21,subject22,subject23,subject31,subject32,subject33,band11,band12,band21,band22,band31,band32,source4,source5,source6,target4,target5,target6,category_professional,category_word,passed,translator_id ,remark1,Product FROM inserted where name = @RREALNAME
end

-----下面的触发器
ALTER  Trigger time_translator1
on t_translator
for Update   ----当译者表中插入时
as 
--当前日期的星期天是哪天begin
declare @WeekDay datetime
declare @NowWeekDay nvarchar(20)
set @NowWeekDay=(--当前星期几
select datename(dw,getdate()))
set @WeekDay=(
select
case @NowWeekDay 
when '星期日' then getdate()
when '星期一' then dateadd(day,6,getdate())
when '星期二' then dateadd(day,5,getdate())
when '星期三' then dateadd(day,4,getdate())
when '星期四' then dateadd(day,3,getdate())
when '星期五' then dateadd(day,2,getdate())
when '星期六' then dateadd(day,1,getdate())
end
)
--当前日期的星期天是哪天end

begin
Declare @RREALNAME varchar(100) 
SELECT @RREALNAME=[name] from inserted ----取得登记译者的姓名
if((select count(*) from t_translator_time where username=@RREALNAME and week= CONVERT(varchar(100), @WeekDay, 23)))=0  --如果在临时表和时间登记中都没有记录
     update t set t.major=b.major,t.master_major=b.master_major,t.master_research=b.master_research,t.doctor_major=b.doctor_major,t.doctor_research=b.doctor_research,t.postdoctor_major=b.postdoctor_major,
t.postdoctor_research=b.postdoctor_research,t.way_to_charge=b.way_to_charge,t.phone=b.phone,t.home_telephone=b.home_telephone,t.mail=b.mail,t.mail2=b.mail2,t.source1=b.source1,t.target1=b.target1,t.source2=b.source2,t.target2=b.target2,
t.source3=b.source3,t.target3=b.target3,t.subject11=b.subject11,t.subject12=b.subject12,t.subject13=b.subject13,t.subject21=b.subject21,t.subject22=b.subject22,t.subject23=b.subject23,t.subject31=b.subject31,t.subject32=b.subject32,
t.subject33=b.subject33,t.band11=b.band11,t.band12=b.band12,t.band21=b.band21,t.band22=b.band22,t.band31=b.band31,t.band32=b.band32,t.source4=b.source4,t.source5=b.source5,t.source6=b.source6,t.target4=b.target4,t.target5=b.target5,t.target6=b.target6,
t.category_professional=b.category_professional,t.category_word=b.category_word,t.passed=b.passed,t.translator_id=b.translator_id ,t.remark1=b.remark1,t.Product=b.Product
    FROM tmp_t_translator_detail t right join inserted b ON t.realname=@RREALNAME  //更新记录  right join
    else
    begin
     rollback Transaction
    end
 end