求教:SQL中触发器、存储过程(SQL 2000)
发布网友
发布时间：2022-04-11 17:23
共3个回答
热心网友
时间：2022-04-11 18:52
create table a(pid char(30),price char(10),ptime smalldatetime,gid char(10));
create table b(pid char(30),price1 char(10),price2 char(10),ptime1 smalldatetime,gid1 char(10));
创建触发器：
create trigger insertlast
on b after insert as
begin
declare @price2 char(10),@ptime1 smalldatetime,@gid1 char(10),@pid char(30);
set @pid=(select pid from inserted);
set @price2=(select top 1 price from a where pid=(select pid from inserted) order by ptime desc);
set @ptime1=(select top 1 ptime from a where pid=(select pid from inserted) order by ptime desc);
set @gid1=(select top 1 gid from a where pid=(select pid from inserted) order by ptime desc);
update b set price2=@price2,ptime1=@ptime1,gid1=@gid1
where b.pid=@pid;
end;
数据表的字段属性不一定符合你的要求，你可以适当改正。
经测试，通过！
热心网友
时间：2022-04-11 20:10
instead of insert,update
as
begin
delete 表B from 表B inner join deleted on ...--表B主键关联
；
go
insert into 表B(品号,本次进价,本次供应商,上次进价,上次进货时间,上次供应商代码)
select a.品号,a.本次进价,a.本次供应商,b.进价,b.时间,b.供应商代码
from inserted a,
(select 品号,进价,时间,供应商代码 from 表A where 时间=(select max(时间) from 表A as MT where MT.品号=表A.品号) b
where a.品号=b.品号;
go
end
热心网友
时间：2022-04-11 21:45
create trigger b_insert
on b
AFTER insert
as
begin
declare @品号 varchar(20)
select @品号=品号 from inserted
update t1 set 上次进价=t2.进价,上次进货时间=t2.时间,上次供应商代码=t2.供应商代码 from B t1
inner join (select top 1 t1.* from A t1
inner join inserted t2 on t1.品号=t2.品号
order by 时间 desc) t2 on t1.品号=t2.品号 and t1.上次供应商代码 is null
end
