三種類型的觸發器

 /* 建三個表分別為
stu.info : stu_id int ,stu_nam char(10) ,stu_tel char(10)
stu_e : stu_id int,stu_hei char(10),sex bit
stu_cj : stu_id int,stu_cj float
*/
--觸發器 insert
if exists(select * from sysobjects where name='T_test1')
begin
 drop Trigger T_test1
end
go
 create Trigger T_test1 on stu_info
for insert
as

declare  @stu_id int,
  @stu_nam char
select
 @stu_id =stu_id,
 @stu_nam =stu_nam
from inserted
 

begin transaction

if not exists (select * from stu_e where stu_id=@stu_id)
begin
 insert stu_e values(@stu_id,'178',1)
end

if not exists (select * from stu_cj where stu_id=@stu_id)
begin
 insert stu_cj values(@stu_id,56.00)
end
commit transaction

insert stu_info values(2,'bb','2222222')

select * from stu_info
select * from stu_e
select * from stu_cj
--------------------------------------------------------------------
--觸發器update
if exists(select * from sysobjects where name='T_test2')
begin
 drop Trigger T_test2
end
go
 create Trigger T_test2 on stu_info
for update
as

begin transaction
if update(stu_id)
 begin
 update stu_e set stu_id = i.stu_id
 from stu_e e,deleted d,inserted i
 where e.stu_id = d.stu_id
 end
 begin
 update stu_cj set stu_id = i.stu_id
 from stu_e c,deleted d,inserted i
 where c.stu_id = d.stu_id
 end
commit transaction
---
update stu_info set stu_id =2 from stu_info where stu_id=4
select * from stu_info
select * from stu_e
select * from stu_cj

---------------------------------------------------------------------------
--觸發器delete

if exists(select * from sysobjects where name='T_test3')
begin
 drop Trigger T_test3
end
go
 create Trigger T_test3 on stu_info
for delete
as

declare  @stu_id int,
  @stu_nam char
select
 @stu_id =stu_id,
 @stu_nam =stu_nam
from deleted
begin transaction
if exists(select * from stu_e where stu_id=@stu_id)
begin
  Delete stu_e
         From stu_e e , deleted d
         Where e.stu_id=d.stu_id
end
if exists(select * from stu_cj where stu_id=@stu_id)
begin
  Delete stu_cj
         From stu_cj c , deleted d
         Where c.stu_id=d.stu_id
end
commit transaction

delete stu_info where stu_id=2

select * from stu_info
select * from stu_e
select * from stu_cj