Trigger to update 2 tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger to update 2 tables

Author

Topic

zdstudent
Starting Member

7 Posts

Posted - 2008-11-29 : 07:23:05

Hello,

Still getting my head around the basics; would appreciate some help please!

I have 2 tables; ordered_item and stock(in fact the table that creates the order is order_table but to filter the update across 3 tables would be too complicated for me).

Basically I simply want when the order is placed to reduce stock level.

The code I came up with comes up with all sorts of errors but might as well post it if you could point out where I'm going wrong.

CREATE TRIGGER stockUpdate ON order_item
FOR INSERT
AS
UPDATE s SET s.stock_count =[s.stock_count – i.order_item_count]
FROM stock s JOIN inserted i ON s.stock_id = i.stock_id

the tables look like this:

table order_item:
order_item_id(PK)
order_id(FK)
stock_id(FK)
order_item_count

table stock:
stock_id(PK)
shirt_id(FK)
shirt_size
stock_count
The relationship I want to create is when order_item_count goes up, stock count must come down.

Thanks for any help!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-29 : 08:15:04

Your trigger looks fine. whats was error you got?

zdstudent
Starting Member

7 Posts

Posted - 2008-11-29 : 08:25:20

Msg 207, Level 16, State 1, Procedure stockUpdate, Line 4
Invalid column name 's.stock_count – i.order_item_count'

Thanks for the reply btw.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-29 : 08:32:48

UPDATE s SET s.stock_count =[s.stock_count – i.order_item_count]

replace brackets [] with () because sql server handels all between [ and ] as column name.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

zdstudent
Starting Member

7 Posts

Posted - 2008-11-29 : 08:38:39

If I change the brackets to (), I get another error:

Msg 102, Level 15, State 1, Procedure stockUpdate, Line 4
Incorrect syntax near '–'.

If I remember correctly I've tried that before...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-29 : 08:47:18

[code]
CREATE TRIGGER stockUpdate ON order_item
AFTER INSERT
AS
BEGIN
UPDATE s
SET s.stock_count =s.stock_count – i.order_item_count
FROM stock s
INNER JOIN inserted i
ON s.stock_id = i.stock_id
END
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-29 : 08:54:36

i have tested your code an got same error.
seems like there are special characters in your code.

This works (typed in new):

CREATE TRIGGER stockUpdate 
ON order_item
FOR INSERT
AS
UPDATE s 
SET stock_count=stock_count - i.order_item_count
FROM stock s 
JOIN inserted i ON s.stock_id = i.stock_id

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

zdstudent
Starting Member

7 Posts

Posted - 2008-11-29 : 09:36:16

Hello again,

well this this no error messages!!! But before I started to celebrate I run the transaction and the smile disappeared.

I placed an order for 1 item and when the transaction went through the stock actually increased by 1.

Which isn't really useful.

HOWEVER!!! I replaced the sign one more time with '-' and this time it worked! However, I have no idea why it didn't work the first time round?

I have couple more questions: when stock falls bellow certain level, I want to send a message to the administrator to order new stock. Can I just add it at the end of this trigger, ie
if stock_count < 3 print...

Should a transaction do the stock level checking and should I use some sort of a check_stock trigger which would abort the transaction if stock bellow 0?

zdstudent
Starting Member

7 Posts

Posted - 2008-11-29 : 10:08:56

Hello again,

Firstly, thank you for all the help! I too couldn't see any syntax errors but as long as it works!

Scrap my first follow-up question, I've decided to dump all the low stock items into a view that the administrator can look at and possibly reorder what he wants.

The transaction question I'm still not too sure of so any advise would be appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-29 : 10:41:11

In our company we don't handle so much business logic in triggers. We prefer to have the business logic in our application.
So i'm not the best person to give any advise - sorry.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-29 : 11:24:45

quote:
Originally posted by zdstudent

Hello again,

well this this no error messages!!! But before I started to celebrate I run the transaction and the smile disappeared.

I placed an order for 1 item and when the transaction went through the stock actually increased by 1.

Which isn't really useful.

HOWEVER!!! I replaced the sign one more time with '-' and this time it worked! However, I have no idea why it didn't work the first time round?

I have couple more questions: when stock falls bellow certain level, I want to send a message to the administrator to order new stock. Can I just add it at the end of this trigger, ie
if stock_count < 3 print...

Should a transaction do the stock level checking and should I use some sort of a check_stock trigger which would abort the transaction if stock bellow 0?

I think you need to wrap it in a transaction from stored procedure and then in trigger check if stock below 0 and rollback transaction in that case.
Alternatively you could include all the logic inside your procedure itself.

zdstudent
Starting Member

7 Posts

Posted - 2008-11-29 : 12:32:18

Thank you!

I will get back to work then.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources