1、在查询分析器用T-SQL对一个表建立后触发器后(delete触发器),是不是只要对这个表中的记录有delete操作,系统就会自动的去执行创建的触发器。有以下两张表:
A表(id,name,yw,sx,yw) A表中有编号、名字、语文、数学、英语字段
B表(id, zf, pjf) B表有编号,总分,平均分字段2、要求写一个触发器完成:只要A表中插入一条记录,在B表中就增加一条记录?例如:A表(001,张三,89,68,89)相就的B表:(001,246,82)3、A表中的name,yw,sx,yw字段的数据更新了在B表相应的数据也会变换?写一个触发器完成此功能.4、删除了A表中的一条记录相应的B表中也删除这条记录,要求也写一个触发器完成此功能?
A表(id,name,yw,sx,yw) A表中有编号、名字、语文、数学、英语字段
B表(id, zf, pjf) B表有编号,总分,平均分字段2、要求写一个触发器完成:只要A表中插入一条记录,在B表中就增加一条记录?例如:A表(001,张三,89,68,89)相就的B表:(001,246,82)3、A表中的name,yw,sx,yw字段的数据更新了在B表相应的数据也会变换?写一个触发器完成此功能.4、删除了A表中的一条记录相应的B表中也删除这条记录,要求也写一个触发器完成此功能?
-------------------------------------------------------
只要不屏敝触发器,就会自动执行。
有以下两张表:
A表(id,name,yw,sx,yw) A表中有编号、名字、语文、数学、英语字段
B表(id, zf, pjf) B表有编号,总分,平均分字段2、要求写一个触发器完成:只要A表中插入一条记录,在B表中就增加一条记录?例如:A表(001,张三,89,68,89)相就的B表:(001,246,82)
------------------------------------------------------
create trigger ti_A on A
for insert
as
insert into B(id,zf,pjf)
select id,
isnull(yw,0)+isnull(sx,0)+isnull(yw,0),
isnull(yw,0)+isnull(sx,0)+isnull(yw,0)/3.0
from inserted3、A表中的name,yw,sx,yw字段的数据更新了在B表相应的数据也会变换?写一个触发器完成此功能.
------------------------------------------------------
create trigger tu_A on A
for update
as
if update(yw) or update(sx) or update(yw)
begin
update B
set zf=t.zf,
pjf=t.pjf
from B,(select id,
isnull(yw,0)+isnull(sx,0)+isnull(yw,0) as zf,
isnull(yw,0)+isnull(sx,0)+isnull(yw,0)/3.0 as pjf
from inserted)t,deleted d
where B.id=t.id and t.id=d.id
end
4、删除了A表中的一条记录相应的B表中也删除这条记录,要求也写一个触发器完成此功能?
------------------------------------------------------
create trigger td_A on A
for delete
as
delete from B where id in (select id from deleted)
没有测试环境,不知道是否正确。
for insert,update,delete
as
begin
if not exists(select 1 from deleted)
begin
insert into b select id,sum(yw+sx+yy) as zf,cast(sum(yw+sx+yy)/3 as float) as pjf
from inserted
group by id
endif not exists(select 1 from inserted)
begin
delete from b where id in (select id from deleted)
endif exists(select 1 from inserted) and exists(select 1 from inserted)
begin
update b set zf=i.zf,pjf=i.pjf from (select id, sum(yw+sx+yy) as zf, cast(sum(yw+sx+yy)/3 as float) as pjf
from inserted group by id
) i,b
where i.id=b.idendend
另,楼主这个功能可以不用两表来实现,也不用总分和平均分字段,只要A表就行,查询的时候汇总一下得出总分和平均分字段。
create table b(id int not null,zf numeric(20,6) null,pjf numeric(20,6) null)
go
create trigger it_a on a
for insert
as
beginif @@rowcount=0 return--select @zf=inserted.yw+inserted.sx+inserted.yy,@pjf=(inserted.yw+inserted.sx+inserted.yy)/3 from inserted
insert into b(id,zf,pjf) select id,inserted.yw+inserted.sx+inserted.yy,(inserted.yw+inserted.sx+inserted.yy)/3 from inserted
end
go
create trigger id_a on a
for delete
as
beginif @@rowcount=0 returndelete from b from deleted where deleted.id=b.id
end
go
create trigger ut_a on a
for update
as
beginif @@rowcount=0 returnupdate b set zf=inserted.yw+inserted.sx+inserted.yy,pjf=(inserted.yw+inserted.sx+inserted.yy)/3 from inserted where inserted.id=b.id
end
insert into a(yw,sx,yy) select 90,95,96 union all select 58,74,95 union all select 88,74,95 union all select 58,94,85 select * from a
select * from bupdate a set yw=100 where id=1delete from a where id=1