通过触发器获取更新值 [英] Getting an updated value through a trigger

问题描述

我有一张这样的桌子

I have a table which is something like this

create table shop.stock(
shopid int foreign key references shop.name(shopid)
itemid int foreign key references item.item(itemid)
stockremaining int)

其中shop.name包含商店的所有细节，item.item包含商品的所有细节。我想创建一个更新触发器，如果​​特定商品中特定商品的库存少于10，我会预订商品

我使用的代码类似于

where shop.name contains all the details of the shop, item.item contains all the details of the item. I want to create an on update trigger which will book the items if the stocks available in a particular store for a particular item is less than 10
I use a code something like

create trigger [shop].[trigonupdate] on [shop].[stock]
after
update
as
declare @stockremaining int;
declare @itemid int;
declare @shopid int;


if(@stockremaining<=10)
begin
insert into booking.items values(@itemid,@shopid,10);
end

问题是如何知道哪个itemid和哪个shopid更新了？

以上触发器不起作用，因为所有变量都分配给空值

problem is how do i get to know which itemid and which shopid are updated?
The above trigger will not work as all the variables are assigned to null values

推荐答案

我自己解决了这个问题

i创建了一个名为booking的表格

I have solved this myself
i created a table called booking like this

create table booking(
bookingid int identity(1,1) primary key,
itemid int foreign key references item.item(itemid),
shopid int foreign key references shop(shopid),
quantity int
);

现在我创建插入触发器

now i create the trigger for insert as

create  trigger [shop].[trigoninsert] on [shop].[stock]
for 
insert
as
declare @stockremaining int;
declare @itemid int;
declare @shopid int;
select @stockremaining=stockremaining from inserted;
select @itemid=itemid from inserted;
select @shopid=shopid from inserted;

 

if(@stockremaining<=10)
begin
insert into booking values(@itemid,@shopid,10);
end 

这篇关于通过触发器获取更新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！