Triggers In sql server 2008

Question

2.60/5 (2 votes)

See more:

SQL-Server-2008

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

SQL

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

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

SQL

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

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?

Posted 3-Oct-13 21:14pm

Rakshith Kumar

Add a Solution

Comments

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 4-Oct-13 4:17am

How do you insert record to tabla sales.details?

2 solutions

Solution 3

H, i Please find the query for the required logic

SQL

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...

Posted 3-Oct-13 22:41pm

Mahendran Murugesan

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Madhu Nair · Accepted Answer · 2013-10-03T22:20:00

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

SQL

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" 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 -

SQL

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