homepage Welcome to WebmasterWorld Guest from 54.211.97.242
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
MS SQL Trigger Problem
SoulAssassin

5+ Year Member



 
Msg#: 67 posted 10:29 am on Oct 6, 2005 (gmt 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.

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

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



 
Msg#: 67 posted 11:33 am on Oct 6, 2005 (gmt 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.

SoulAssassin

5+ Year Member



 
Msg#: 67 posted 2:28 pm on Oct 6, 2005 (gmt 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!

aspdaddy

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 67 posted 3:19 pm on Oct 6, 2005 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved