三种类型的触发器

最新推荐文章于 2024-07-04 18:10:37 发布

原创最新推荐文章于 2024-07-04 18:10:37 发布 · 3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#delete #insert #float #c

数据库技术专栏收录该内容

3 篇文章

订阅专栏

本文介绍了如何在SQL中创建并应用三种类型的触发器：插入(insert)触发器、更新(update)触发器及删除(delete)触发器。通过示例展示了如何在进行数据操作时同步更新多个相关表，确保数据的一致性和完整性。

/* 建三个表分别为
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