今天休息,高興。 下面是插入時可以用的:CREATE TRIGGER tri表1 ON dbo.表1 FOR INSERT AS DECLARE @numD numeric(12,2) declare curTemp cursor for select D from 表3OPEN curtemp while (0=0) BEGIN fetch next from curtemp into @numD IF (@@fetch_status<>0) break INSERT INTO 表2(A,C) SELECT tabA.A+@numD,'C' FROM insertedEND CLOSE curTemp DEALLOCATE curTemp
CREATE TRIGGER 名2 ON 表1 FOR update AS update 表2 set A=aa.A+BB.D from inserted aa,表3 bb where aa.编号=表2.编号 and aa.编号=表3.编号
SELECT tabA.A+@numD,'C' FROM inserted為: SELECT tabA.A+@numD,'C' FROM inserted tabA大力來了,你有救了。趕快問他!
create trigger 表1_ins on 表1 for insert as begin insert into 表2(A) select A from inserted update a set a.A=a.A+sum(b.D) from 表2 a,表3 b end 你的叙述不清楚,我想大概是这样的,当然update和着差不多,你自己写吧
create trigger tr_table1_up on table1 for update asupdate table2 set A=a.A+b.D from inserted a ,table3 b where table2.id=a.id and table2.id=b.idCREATE TRIGGER tr_talbe1_in ON table1 FOR INSERT AS insert table2 select id,a.A+b.D,... from inserted a,table3 b on a.id=b.id
在TaoGeGe(涛哥哥)的基础上修改的。 CREATE TRIGGER tri表1 ON dbo.表1 FOR INSERT,UPDATE AS DECLARE @numD numeric(12,2) declare curTemp cursor for select D from 表3OPEN curtemp while (0=0) BEGIN fetch next from curtemp into @numD WHILE (@@fetch_status<>0) BEGIN IF EXISTS(SELECT * FROM DELETED) BEGIN INSERT INTO 表2(A,C) SELECT tabA.A+@numD,'C' FROM UPDATED END ELSE BEGIN INSERT INTO 表2(A,C) SELECT tabA.A+@numD,'C' FROM inserted END fetch next from curtemp into @numD
END END CLOSE curTemp DEALLOCATE curTemp
表1(A,B),表2(A,C),表3(D,E): 现要求做一触发器,当表1添加/修改时,表2也相应的作添加/修改,表2(A)=表1(A)+表3(D)。 中间要有一循环,即所求结果如下: 表1.insert or 表1.update do while not 表3.eof 表2.insert or 表2.update 表2(A)=表1(A)+表3(D) 表3.movenext loop更新(即UPDATE)可以暂时不管呀,主要是插入呀(INSERT),即表1(A),表(A),表3(D),以上三表中的字段都为字符型 现表1数据为: 1 2 3 现表3数据为: a b c 则表2数据为: 1a 2a 3a 1b 2b 3b 1c 2c 3c 所以当表1为INSERT时,表2所需要以下运算: 表1.insert or 表1.update do while not 表3.eof 表2.insert or 表2.update 表2(A)=表1(A)+表3(D) 表3.movenext loop
补充一条,表2(A)<>表1(A),而是:表2(A)=表1(A)+表3(D)
happydreamer(Very Very Shy)的方法不錯,但如果把表3的關聯方式改為left join可能會更好一點。
看看是不是你想要的: --table create table TblA( A int )create table TblB( A varchar(20) )create table TblC( D varchar(10) )--data insert into TblA values(1) insert into TblA values(2) insert into TblA values(3)insert into TblA values('a') insert into TblA values('b') insert into TblA values('c')--trigger drop trigger Tri_Test go create trigger Tri_Test on TblA for insert as declare @TestOne int, @TestTwo varchar(10) declare curTest cursor for select A,D from inserted,TblC open curTest fetch curTest into @TestOne,@TestTwo insert into TblB values(str(@TestOne)+@TestTwo) print str(@TestOne) + '===' + @TestTwo while(@@fetch_status=0) begin fetch curTest into @TestOne,@TestTwo insert into TblB values(str(@TestOne)+@TestTwo) print str(@TestOne) + '===' + @TestTwo end close curTest deallocate curTest go
CREATE TRIGGER 名1 ON 表1 FOR INSERT AS insert 表2 select cast(aa.A as varchar(10))+bb.D from inserted aa,表3 bb where cast(aa.a as varchar(10))+bb.a not in (select A from 表2)
插入/修改一条数据时,是将C表的所有记录和A表插入/修改记录的A合并,更新B表吗?最后能写一些数据出来
下面是插入時可以用的:CREATE TRIGGER tri表1 ON dbo.表1
FOR INSERT
AS
DECLARE @numD numeric(12,2)
declare curTemp cursor for
select D from 表3OPEN curtemp
while (0=0)
BEGIN
fetch next
from curtemp
into @numD
IF (@@fetch_status<>0) break
INSERT INTO 表2(A,C)
SELECT tabA.A+@numD,'C' FROM insertedEND
CLOSE curTemp
DEALLOCATE curTemp
FOR update
AS
update 表2 set A=aa.A+BB.D from inserted aa,表3 bb where aa.编号=表2.编号 and aa.编号=表3.编号
SELECT tabA.A+@numD,'C' FROM inserted tabA大力來了,你有救了。趕快問他!
for insert
as
begin
insert into 表2(A) select A from inserted
update a set a.A=a.A+sum(b.D)
from 表2 a,表3 b
end 你的叙述不清楚,我想大概是这样的,当然update和着差不多,你自己写吧
for update
asupdate table2
set A=a.A+b.D
from inserted a ,table3 b
where table2.id=a.id and table2.id=b.idCREATE TRIGGER tr_talbe1_in ON table1
FOR INSERT
AS
insert table2
select id,a.A+b.D,...
from inserted a,table3 b
on a.id=b.id
CREATE TRIGGER tri表1 ON dbo.表1
FOR INSERT,UPDATE
AS
DECLARE @numD numeric(12,2)
declare curTemp cursor for
select D from 表3OPEN curtemp
while (0=0)
BEGIN
fetch next
from curtemp
into @numD
WHILE (@@fetch_status<>0)
BEGIN
IF EXISTS(SELECT * FROM DELETED)
BEGIN
INSERT INTO 表2(A,C)
SELECT tabA.A+@numD,'C' FROM UPDATED
END
ELSE
BEGIN
INSERT INTO 表2(A,C)
SELECT tabA.A+@numD,'C' FROM inserted
END
fetch next
from curtemp
into @numD
END
END
CLOSE curTemp
DEALLOCATE curTemp
现要求做一触发器,当表1添加/修改时,表2也相应的作添加/修改,表2(A)=表1(A)+表3(D)。
中间要有一循环,即所求结果如下: 表1.insert or 表1.update
do while not 表3.eof
表2.insert or 表2.update
表2(A)=表1(A)+表3(D)
表3.movenext
loop更新(即UPDATE)可以暂时不管呀,主要是插入呀(INSERT),即表1(A),表(A),表3(D),以上三表中的字段都为字符型
现表1数据为:
1
2
3
现表3数据为:
a
b
c
则表2数据为:
1a
2a
3a
1b
2b
3b
1c
2c
3c
所以当表1为INSERT时,表2所需要以下运算:
表1.insert or 表1.update
do while not 表3.eof
表2.insert or 表2.update
表2(A)=表1(A)+表3(D)
表3.movenext
loop
--table
create table TblA(
A int
)create table TblB(
A varchar(20)
)create table TblC(
D varchar(10)
)--data
insert into TblA values(1)
insert into TblA values(2)
insert into TblA values(3)insert into TblA values('a')
insert into TblA values('b')
insert into TblA values('c')--trigger
drop trigger Tri_Test
go
create trigger Tri_Test
on TblA
for insert
as
declare @TestOne int,
@TestTwo varchar(10)
declare curTest cursor for select A,D from inserted,TblC
open curTest
fetch curTest into @TestOne,@TestTwo
insert into TblB values(str(@TestOne)+@TestTwo)
print str(@TestOne) + '===' + @TestTwo
while(@@fetch_status=0)
begin
fetch curTest into @TestOne,@TestTwo
insert into TblB values(str(@TestOne)+@TestTwo)
print str(@TestOne) + '===' + @TestTwo
end
close curTest
deallocate curTest
go
FOR INSERT
AS
insert 表2 select cast(aa.A as varchar(10))+bb.D from inserted aa,表3 bb where cast(aa.a as varchar(10))+bb.a not in (select A from 表2)
不要著急了,這麼多的回答,整理一下一定有你要的答案