自己动手编写触发器

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 null
drop table t1,t2
create 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_t1
on t1
After insert
as
	declare @_id int,@_pName varchar(10)
	select @_id=id,      
            @_pName=pName
	from inserted    ---一定要学会运用inserted和deleted临时表
insert into t2 (id,pName,detail,isFinish) values(@_id,@_pName,null,0)

触发器tr_update_t1的写法

create trigger tr_update_t1
on t1
After update
as
	declare @_id int,@_pName varchar(10)
	select @_id=id,
		@_pName=pName
	from inserted
update t2 set pName=@_pName,detail=null,isFinish=0 where id=@_id

触发器tr_delete_t1的写法

create trigger tr_delete_t1
on t1
After delete
as
	declare @_id int
	select @_id=id
	from deleted
delete 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_t1
on t1
After insert
as
	declare @_id int, @_pName varchar(10)
	declare curTemp cursor for 
	select id,pName from inserted order by id
open curTemp
fetch next from curTemp into @_id,@_pName
while @@fetch_status=0
begin
	insert into t2 (id,pName,detail,isFinish) values(@_id,@_pName,null,0)
	fetch next from curTemp into @_id,@_pName
end
close curTemp
deallocate 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_t1
on t1
After update
as
declare @_id int,@_pName varchar(10)
declare curTemp cursor for
	select id,pName from inserted
open curTemp
fetch next from curTemp into @_id,@_pName
while @@fetch_status=0
begin
	update t2 set pName=@_pName,detail=null,isFinish=0 where id=@_id
	fetch next from curTemp into @_id,@_pName
end
close curTemp
deallocate curTemp

表关联

create trigger tr_update_t1
on t1
for update
as 
update a set pName=b.pName from t2 as a
join inserted as b on a.id=b.id

tr_delete_t1

变量遍历游标

create trigger tr_delete_t1
on t1
After delete
as
declare @_id int
declare curTemp cursor for
	select id from deleted
open curTemp
fetch next from curTemp into @_id
while @@fetch_status=0
begin
	delete from t2 where id=@_id
	fetch next from curTemp into @_id
end
close curTemp
deallocate curTemp

表关联

create trigger tr_delete_t1
on t1
for delete
as
delete a from t2 as a where 
exists (select 1 from deleted where id=a.id)

tr_delete_t2

变量遍历游标

create trigger tr_delete_t2
on t2
Instead of
delete
as
declare @_iid int,@_id int,@_count int
declare curTemp cursor for
	select iid,id from deleted
open curTemp
fetch next from curTemp into @_iid,@_id
while @@fetch_status=0
begin
	select @_count=count(id) from t2 where id=@_id
	if(@_count=1)
		begin
			update t2 set detail=null,isFinish=0 where iid=@_iid 
		end
	else
		begin
			delete from t2 where iid=@_iid
		end
	fetch next from curTemp into @_iid,@_id
end
close curTemp
deallocate curTemp

表关联

create trigger tr_delete_t2  on t2  
Instead of delete  
as  
BEGIN
SET NOCOUNT ON;  --设置不返回影响行数
UPDATE a
SET detail=NULL,isFinish=0
FROM 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 a
FROM 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还真得下功夫啊)