自己动手编写触发器

t1为主表,t2为附表,t1,t2是一对多的关系,通过id
其实这个触发器的思路是根据我在公司编写的一个程序对数据库操作而来,由于程序不能直接操作触发器,只能在操作的t1的时候,在同时对t2进行操作,最近看到触发器,完全符合程序中的各项要求,当然我不可能直接去手动操作数据库,而且如果程序改动的话,再更改触发器就麻烦大了.这儿就当作是练习.
对于t1的触发器:
tr_insert_t1    ----当t1插入一条记录后,t2表会自动生成一条记录,并且id与t1的id相对应.pName和t1的pName相同
tr_update_t1  ----当t1更新记录后,t2中与之对应id的pName会改变,detail清空(null),isFinish清空(0或null)
tr_delete_t1   ----当t1记录删除后,相应id的t2的记录也删除
对于t2的触发器:

tr_delete_t2  ----当t2中某个id在t2中仅有一条,被删除时,将detail清空,isfinish清空,pName不变(就是始终保持t2中至少有一条记录与t1的对应,即使t2中的这个id仅有一条时,被删除时,也不是真正的被删除.)

表结构(记住表建好后在此测试阶段不要再删除,否则之后创建的触发器也将被删除)

if object_id('t1','U') is not null and object_id('t2','U') is not nulldrop table t1,t2create table t1(id int identity primary key,pName varchar(10))create table t2(iid int identity primary key,id int,pName varchar(10),detail varchar(10),isFinish bit)

=========================================================================

触发器tr_insert_t1的写法

create trigger tr_insert_t1on t1After insertasdeclare @_id int,@_pName varchar(10)select @_id=id,                  @_pName=pNamefrom inserted    ---一定要学会运用inserted和deleted临时表insert into t2 (id,pName,detail,isFinish) values(@_id,@_pName,null,0)

触发器tr_update_t1的写法

create trigger tr_update_t1on t1After updateasdeclare @_id int,@_pName varchar(10)select @_id=id,@_pName=pNamefrom insertedupdate t2 set pName=@_pName,detail=null,isFinish=0 where id=@_id

触发器tr_delete_t1的写法

create trigger tr_delete_t1on t1After deleteasdeclare @_id intselect @_id=idfrom deleteddelete from t2 where id=@_id

触发器tr_delete_t2的写法

============================================================

鉴于错误百出，主要原因是不支持批量操作,每次变量只取inserted或deleted中最后一条记录进行操作，以上等号中间部分全部作废。（错误原因见http://topic.csdn.net/u/20111115/15/8dda57f9-073d-4b4f-b3c7-d1fd94573d2a.html）在此感谢csdn的大神们。

查阅资料得之，触发器进行批量操作时有两种思路，一种是变量结合遍历游标，一种是表关联。所以以下都是将用这两种思路给出写法。

tr_insert_t1

变量遍历游标

create trigger tr_insert_t1on t1After insertasdeclare @_id int, @_pName varchar(10)declare curTemp cursor for select id,pName from inserted order by idopen curTempfetch next from curTemp into @_id,@_pNamewhile @@fetch_status=0begininsert into t2 (id,pName,detail,isFinish) values(@_id,@_pName,null,0)fetch next from curTemp into @_id,@_pNameendclose curTempdeallocate curTemp

其中

DEALLOCATE删除游标引用。当释放最后的游标引用时，组成该游标的数据结构由 Microsoft® SQL Server™ 释放。

表关联

create trigger tr_insert_t1  on t1  After insert  as    insert into t2 (id,pName,detail,isFinish) select id,pName,null,0 from inserted

tr_update_t1

变量遍历游标

create trigger tr_update_t1on t1After updateasdeclare @_id int,@_pName varchar(10)declare curTemp cursor forselect id,pName from insertedopen curTempfetch next from curTemp into @_id,@_pNamewhile @@fetch_status=0beginupdate t2 set pName=@_pName,detail=null,isFinish=0 where id=@_idfetch next from curTemp into @_id,@_pNameendclose curTempdeallocate curTemp

表关联

create trigger tr_update_t1on t1for updateas update a set pName=b.pName from t2 as ajoin inserted as b on a.id=b.id

tr_delete_t1

变量遍历游标

create trigger tr_delete_t1on t1After deleteasdeclare @_id intdeclare curTemp cursor forselect id from deletedopen curTempfetch next from curTemp into @_idwhile @@fetch_status=0begindelete from t2 where id=@_idfetch next from curTemp into @_idendclose curTempdeallocate curTemp

表关联

create trigger tr_delete_t1on t1for deleteasdelete a from t2 as a where exists (select 1 from deleted where id=a.id)

tr_delete_t2

变量遍历游标

create trigger tr_delete_t2on t2Instead ofdeleteasdeclare @_iid int,@_id int,@_count intdeclare curTemp cursor forselect iid,id from deletedopen curTempfetch next from curTemp into @_iid,@_idwhile @@fetch_status=0beginselect @_count=count(id) from t2 where id=@_idif(@_count=1)beginupdate t2 set detail=null,isFinish=0 where iid=@_iid endelsebegindelete from t2 where iid=@_iidendfetch next from curTemp into @_iid,@_idendclose curTempdeallocate curTemp

表关联

create trigger tr_delete_t2  on t2  Instead of delete  as  BEGINSET NOCOUNT ON;  --设置不返回影响行数UPDATE aSET detail=NULL,isFinish=0FROM t2 AS a INNER JOIN deleted AS d ON a.ID=d.ID AND NOT EXISTS(SELECT 1 FROM t2 WHERE ID=a.ID AND iid<a.iid)DELETE aFROM t2 AS a INNER JOIN deleted AS d ON a.ID=d.ID AND  EXISTS(SELECT 1 FROM t2 WHERE ID=a.ID AND iid<a.iid)END

(多谢大版Roy及各位cdsn大神指点.原来写一篇原创blog还真得下功夫啊)