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
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,
values (@tx_prd, 0, 0, getdate(), @tx_type,
@tx_uoi, @tx_opn_bal,'created by trgChgProd')