create trigger tr
on t1
for Inster,Delete,UpDate
as
begin
declare @total int
set @total=sum(t1.Price * t1.Quality)
update t2
set TotalMoney=@total
from t1,t2
where (t1.id=t2.id)
group by id
end
on t1
for Inster,Delete,UpDate
as
begin
declare @total int
set @total=sum(t1.Price * t1.Quality)
update t2
set TotalMoney=@total
from t1,t2
where (t1.id=t2.id)
group by id
end
解决方案 »
- 求SQL两个表的输入问题
- 哪位好心人帮我这个sql语句从一个表变成2个表,在线等,谢谢啦
- 请看这条语句...
- 求一种select查詢方法
- 这个sql语句怎么写呢?两个表通过id字段关联起来了,并且id作为表1的主键了,怎么同时修改(update)两个表的id值?
- 2k professional环境,mssql 2000(personal)装不上去,都装了两天了,系统格了N次了
- 有关access和sqlserver的数据交换,数据同步
- 分离再附加数据库后,数据文件和日志文件不增大,急啊!!!
- 长了一星,散分。
- 请教:统计附件字段的数量后增加记录的存储过程?
- 求触发器
- 将数据库从本地服务器导出到远程服务器,所有的表的标识列都失效,插入记录时,都提示原来的标识列不能为空,而不是自动给值,为什么?怎
FOR INSERT
AS
insert t2 select id,sum(price*quality) from t2 where id in (select id from inserted)
and id not in (select id from t2) group by id
update t2 set totalmoney=tem.aa from (select id,sum(price*quality) aa from t1 where id in (select id from inserted)
group by id) tem where tem.id=t2.id
CREATE TRIGGER 名2 ON t1
FOR update
AS
update t2 set totalmoney=tem.aa from (select id,sum(price*quality) aa from t1 where id in (select id from inserted)
group by id) tem where tem.id=t2.id
CREATE TRIGGER 名3 ON t1
FOR delete
AS
update t2 set totalmoney=tem.aa from (select id,sum(price*quality) aa from t1 where id in (select id from deleted)
group by id) tem where tem.id=t2.id
--如果为0的要被删掉就加下面这句
delete t2 where totalmoney<=0
For Insert
As
Insert Into T2 (ID,TotqlMoney) Select ID,Sum(Price*Quality) Where ID In (Select ID From Inserted) Group By IDCreate Trigger UpdRec On T1
For Update
As
Update A Set TotalMoney=(Select Sum(Price*Quality) From T1 B Where B.ID=A.ID) From T2 A Where A.ID In (Select ID From Inserted)Create Trigger DelRec On T1
For Delete
As
Declare @rsCount Int
Select @rsCount=(Select Count(*) From T1 Where ID In (Select ID From Deleted))
Case
When @rsCount>0
Update A Set TotalMoney=(Select Sum(Price*Quality) From T1 B Where B.ID=A.ID) From T2 A Where A.ID In (Select ID From Deleted)
Else
Delete From T2 Where ID In (Select ID From Deleted)
End
Create Trigger Intr On T1
For Insert
As
if exists( (select id from inserted) in (select id from t1 ))
update t2
set totalmoney=(Select Sum(Price*Quality) From T1 Where ID=(select id from inserted))
where ID = (Select ID From Inserted)
else
Insert Into T2
Select ID,Sum(Price*Quality)
from t1
Where ID In (Select ID From Inserted)
Group By ID
endCreate Trigger Uptr On T1
For update
As
update t2
set totalmoney=(Select Sum(Price*Quality) From T1 Where ID=(select id from updated))
where ID = (Select ID From updated)Create Trigger Detr On T1
For delete
As
if exists( (select id from deleted) in (select id from t1 ))
update t2
set totalmoney=(Select Sum(Price*Quality) From T1 Where ID=(select id from deleted))
where ID = (Select ID From deleted)
else
delete t2
where ID In (Select ID From Deleted)
end
FOR INSERT, delete, update
AS
if exists (select * from t2, inserted where t2.id = inserted.id)
update t2 set TotalMoney = t2.TotalMoney + (select sum(price * quality) from inserted where inserted.id = t2.id) from t2
else
insert t2 select id, sum(price * quality) from inserted group by idif exists (select * from t2, deleted where t2.id = deleted.id)
update t2 set TotalMoney = t2.TotalMoney - (select sum(price * quality) from deleted where deleted.id = t2.id) from t2
http://expert.csdn.net/Expert/topic/1816/1816716.xml?temp=.2939112(简化版)
呵呵,小弟系新手,望指点