Debug a trigger

[durug]

Hi everybody!

I use SQL 7.0 and I implemented an insert trigger on a table. Now, apparently nothing is happening.

How can I debug the trigger? Or how can I make sure it's working?

Thanks,
Durug

 

[tklear]

I hope this isn't more elementary than you're looking for but...

If you're on a development server, you can modify the trigger to include some print statements through out the code. Then perform an insert statement from Query Analyzer.

You can even rollback the transaction if you want:
begin tran
insert into tableInQuestion(blah)values('blah')
rollback tran

This should show your print statements and give you an idea of what's happening (or not happening).

I'm sure there must be some debug tools but I've never used them.

 

[SQLSister]

First, I hope it wasn't a production database you put an untested trigger on. Always create and test triggers in a development database. For one you will not take your users down while you get the bugs worked out and second, you won't be inserting test data or deleting real records while you test your trigger.

Second, you can post your trigger here and we may be able to spot what is wrong. But you should learn to debug yourself.

Some things to think about in triggers. Don't use select statements (except as part of a subquery to to populate a variable) in triggers. Do not use cursors in a trigger. Usually you will be using the information in the inserted and/or deleted pseudotables. If you aren't, then question why this needs to be trigger. Do not do anything in a trigger that can be done using a constraint; constraints run faster than triggers.

To test triggers write insert, update or delete statements for a wide range of the data inputs expected (include obviously bad data and multiple records). Try to make sure any branches of the code get exercised in your test cases. After each statement run a select stament on any tables that should be affected by the trigger operating and see if the data did get inserted, updated or deleted properly.

So if I have a trigger that inserts into an audit table everytime I delete a record, I would write a delete statement to delete an existing record, then run a select statement on both the base table (to see if the record I intended to delete was deleted) and the audit table to see if the information I intended to add to it was inserted. You get the idea. You can also see that this kind of testing is not possible on a production database or you will destroy real records, add junk records or corrupt existing data!

 

[durug]

Thank you, you convinced me to post my "secret" trigger

I think only the select statemnt is causing some problems

SELECT @PR_ID = m.PR_ID , @title = p.TITLE, @Code_F1 = m.CODE_F1
FROM inserted m, grs1000.proginfo p where m.INFO_ID = p.INFO_ID
AND m.CODE_F1 is not null

since when I tested with hardcoded values for @PR_ID, @title and @Code_F1 everything went fine

So, the logical table inserted should contain the data that was inserted, right? In order to extract the values that I need I have to join this with another table.
All this values are inserted at the end of the trigger in a linked sql server.

Do you see something wrong with the code?
Thanks,
Durug

 

[tlbroadbent]

I see potential problems but unless you post the rest of your secret trigger, we can't be of much help. You didn't post enough code to give us much idea of what you are trying to accomplish. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.