Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

MS SQL Trigger Problem

10:29 am on Oct 6, 2005 (gmt 0)

New User

10+ Year Member

joined:Jan 21, 2005
votes: 0

I am new to writing triggers and I'm having a problem with the one below. The idea is that the trigger will record stock transactions in an ordering system, so any changes to the stock held (stk_hld_uoi) will be recorded. However, i only want to add a record when the change is made from the product update form, not when an order is made - this transaction record is handled separately. The variable (tx_ord_check) will be set as 1 in the update for an order so i am trying to test this to find out if the insert to the Stock_Tx table should be made.

Basically it isn't working, but i don't know why 80( , plus i have no real idea how to debug or output things so i am struggling to find out why.

Any help would be much appreciated.

ON Product
if UPDATE(stk_hld_uoi)
declare @tx_type smallint, @tx_uoi float, @tx_prd int, @tx_opn_bal float, @tx_end_bal float, @tx_ord_check bit

select @tx_opn_bal = del.stk_hld_uoi FROM DELETED del
select @tx_end_bal = ins.stk_hld_uoi FROM INSERTED ins
select @tx_prd = ins.product_id FROM INSERTED ins
select @tx_uoi = ins.stk_hld_uoi FROM INSERTED ins

if(@tx_end_bal < @tx_opn_bal)
select @tx_type = '4'
select @tx_type = '3'

if(@tx_ord_check <> 1)
insert into Stock_Tx (product_id, order_id, raised_by,
raised_date, tx_type, tx_uoi,
tx_opn_bal, comment)
values (@tx_prd, 0, 0, getdate(), @tx_type,
@tx_uoi, @tx_opn_bal,'created by trgChgProd')

11:33 am on Oct 6, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member txbakers is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Sept 1, 2001
votes: 0

well, I can't help you with triggers, but I can help you with debugging and output.

If I run into situations where the query isn't working, or isn't firing when supposed to, I go into debug mode.

It's not that difficult, but it is important.

The first friend you have (in ASP) is Response.Write(). In PhP I believe it is echo()

This will write values to the screen so you can test them.

First, comment any execute lines you might have that actually fire the query. Then echo or REsponse.Write the query to the screen, to see what you are sending to the database.

In your statements below you are comparing the values of two variables:

if(@tx_end_bal < @tx_opn_bal)

Do you know what the actual values are at that point? A good candidate for a response.write to actually see what they are.

Debugging is painstaking, but so important. In a nutshell, you need to see the values you are passing into variables to check your logic.

2:28 pm on Oct 6, 2005 (gmt 0)

New User

10+ Year Member

joined:Jan 21, 2005
votes: 0

Thanks for the advice

Found that in fact i hadn't assigned a value to @tx_ord_check so where i was checking it at the bottom it was going wrong. Doh!

3:19 pm on Oct 6, 2005 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 13, 2002
votes: 0

You can workaround it with the flag like you are doing, but the best solution for this is a stored procedure and a transaction.

Triggers are designed for database (not application) level integrity and you can debug a stored procedure much more easily in EM than a trigger.


Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members