MS SQL Trigger Problem

[SoulAssassin]

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.

CREATE TRIGGER trgChgProd
ON Product
FOR UPDATE
AS
if UPDATE(stk_hld_uoi)
begin
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'
else
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')
end

[txbakers]

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.

[SoulAssassin]

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!

[aspdaddy]

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.