触发器在sql server 2008中 [英] Triggers In sql server 2008

问题描述

我有一个名为Sales.details的表，我按如下方式创建它

I have a table whose name is Sales.details and i created it as follows

create table sales.details
salesid int identity(1,1) primary key
shopid int foreign key references shop.name(shopid)
itemid int foreign key references item.item(itemid)
dateofpurchase date

现在我插入一个记录到这个表中我想要一个表cal中存在的数量因为shop.stock被减少了1.因此我为sales.details表创建了一个插入触发器

now as soon as i insert a record into this table i want the quantity present in one more table called as shop.stock to be reduced by 1. Hence i created an on insert trigger for the sales.details table as

create trigger [sales].[trigoninsert] on [sales].[details]
for insert
as
declare @itemid int;
declare @shopid int;
update shop.stock
set stock_remaining=stock_remaining-1 where itemid=@itemid and shopid=@shopid

但当我在sales.details表中插入记录时，我发现库存剩余减少1.库存剩余保持不变



有人可以纠正我吗？

But when i insert a record to my sales.details table i dont find stock remaining reduced by 1. Stock remaining stays the same

Can someone please correct me?

推荐答案

假设您在sales.details表中一次只插入一条记录，则应按以下方式编写触发器 -

The trigger should be written in the following way assuming you are inserting only one record at a time in sales.details table-

create trigger [sales].[trigoninsert] on [sales].[details]
for insert
as
declare @itemid int;
declare @shopid int;

select @itemid=itemid from inserted
select @shopid=shopid from inserted

update shop.stock
set stock_remaining=stock_remaining-1 where itemid=@itemid and shopid=@shopid

itemid& shopid字段名称应替换为sales.details表中的实际字段名称



如果要处理多个记录更新（以及单个记录更新），那么触发器应按以下方式创建 -

"itemid" & "shopid" field name should be replaced with actual field name in sales.details table

If you want to handle multiple record updates(as well as single record update) then the trigger should be created in the following way -

create trigger [sales].[trigoninsert] on [sales].[details]
for insert
as
--declare @itemid int;
--declare @shopid int;

--select @itemid=itemid from inserted
--select @shopid=shopid from inserted

update shop.stock
set stock_remaining=stock_remaining-1
from shop.stock inner join inserted on itemid=inserted.itemid and shopid=inserted.shopid

H，
i请找到所需逻辑的查询

H, i Please find the query for the required logic

drop table shopname

Create Table shopname (shopid int identity(1,1) primary key,ShopName varchar(150))
Insert shopname values ('Poorvika')
Insert shopname values ('Univercell')

Create Table item (itemid int identity(1,1) primary key,itemName varchar(150))
Insert item values ('Mobile')
Insert item values ('Mp3Player')

Create Table Stock ( stockid int identity(1,1) primary key, shopid int foreign key references shopname(shopid),
itemid int foreign key references item(itemid), Stockremaining int)


Insert Stock values(1,1,10)
Insert Stock values(1,2,8)
Insert Stock values(2,1,3)
Insert Stock values(2,2,3)


create table salesdetails
(
salesid int identity(1,1) primary key,
shopid int foreign key references shopname(shopid),
itemid int foreign key references item(itemid),
dateofpurchase date)

--Trigger usage 
CREATE TRIGGER salestrigoninsert on salesdetails
      AFTER INSERT
AS 
BEGIN
	Declare @shopid int
	Declare @itemid int
	
	Select  @shopid  = shopid, @itemid=i.itemid from inserted i;	 -- getting the value from Insert Statement
	
	Update stock set Stockremaining = Stockremaining-1 where Shopid =@shopid and itemid = @itemid
	
END

Insert salesdetails values (1,1,'2013-10-04')
Select * from shopname
Select * from item
Select * from stock
Select * from salesdetails

注意：表名不完全相同



问候，马赫......

Note: Table names are not exactly the same

Regards,Mahe...

这篇关于触发器在sql server 2008中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！