SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 How to use DeBUG window in QUERY Analyzer
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

cshekar
Starting Member

USA
49 Posts

Posted - 09/14/2004 :  15:35:47  Show Profile  Reply with Quote
I wanted debug stored procedure step by step is tehre a way
to debug Step by step or line by line in QUERY Analyzer
SQL server 2000

Thanks
chandra

chandra shekar

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 09/14/2004 :  15:39:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
Look up Transact-SQL Debugger in SQL Server Books Online for instructions on how to use.

Tara
Go to Top of Page

cshekar
Starting Member

USA
49 Posts

Posted - 09/14/2004 :  16:13:39  Show Profile  Reply with Quote
Thak you so much I got it instruction from BOL how to use debug window,
but if I wanted debug Trigger step by step how do I do that can I
open Trigger in debug mode in Query Analyzer or how can I debug

chandra shekar
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 09/14/2004 :  16:15:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
I don't think you can debug triggers via that debugger. I use PRINT statements and SELECT * to debug when things aren't working right.

Tara

Edited by - tkizer on 09/14/2004 17:21:13
Go to Top of Page

cshekar
Starting Member

USA
49 Posts

Posted - 09/14/2004 :  16:24:05  Show Profile  Reply with Quote
Thank you so much tara, you mean I can not debug trigger
or is there any other way of doing this,
because I have a problem with Update trigger but some reason
it is not updating the table here is Error that I have got
but I am updateing only one row at a time still I am gettting this
error.

Server: Msg 512, Level 16, State 1, Procedure ut_Currhold, Line 132
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated

chandra shekar
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 09/14/2004 :  16:36:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
Well you've got a subquery that is returning more than one row which is not allowed. So your UPDATE might only be affecting one row, but your subquery is returning more than one. Please post the code.

Tara
Go to Top of Page

cshekar
Starting Member

USA
49 Posts

Posted - 09/14/2004 :  16:52:53  Show Profile  Reply with Quote

Hi tara,
here is the my trigger code which is taking from Inseted table so that means only one row
in inset=rted table..


/* Update Trigger for table Currhold */
CREATE trigger ut_Currhold
on Currhold for update as
declare @currency_code character(3),
@forward_ref integer
select @currency_code = currency_code, @forward_ref = forward_ref from inserted
/* currency_code references primary key FXCurr (currency_code) */
/* Child Update Restrict */
if @currency_code<>NULL
begin
if (select count(*)
from FXCurr, inserted
where FXCurr.currency_code = inserted.currency_code) <> (select count(*) from inserted)
begin
raiserror 30008 'You may not update Currhold unless a corresponding FXCurr exists'
rollback transaction
return
end
end
/* instrument_type references primary key Instrument_Types (instrument_type) */
/* Child Update Restrict */
if update (instrument_type)
begin
if (select count(*)
from Instrument_Types, inserted
where Instrument_Types.instrument_type = inserted.instrument_type) <> (select count(*) from inserted)
begin
raiserror 30009 'You may not update Currhold unless a corresponding Instrument_Types exists'
rollback transaction
return
end
end
/* counter_party references primary key Ratings (counter_party) */
/* Child Update Restrict */
if update (counter_party)
begin
if (select count(*)
from Ratings, inserted
where Ratings.counter_party = inserted.counter_party) <> (select count(*) from inserted)
begin
raiserror 30010 'You may not update Currhold unless a corresponding Ratings exists'
rollback transaction
return
end
end
/* der_type references primary key Derivative_Types (der_type) */
/* Child Update Restrict */
if update (der_type)
begin
if (select count(*)
from Derivative_Types, inserted
where Derivative_Types.der_type = inserted.der_type) <> (select count(*) from inserted)
begin
raiserror 30011 'You may not update Currhold unless a corresponding Derivative_Types exists'
rollback transaction
return
end
end
/* account references primary key Account (account) */
/* Child Update Restrict */
if update (account)
begin
if (select count(*)
from Account, inserted
where Account.account = inserted.account) <> (select count(*) from inserted)
begin
raiserror 30012 'You may not update Currhold unless a corresponding Account exists'
rollback transaction
return
end
end
/* forward_ref references primary key Currhold (currhold_id) */
/* Child Update Restrict */
if @forward_ref<>NULL
begin
if (select count(*)
from Currhold, inserted
where Currhold.currhold_id = inserted.forward_ref) <> (select count(*) from inserted)
begin
raiserror 30013 'You may not update Currhold unless a corresponding Currhold exists'
rollback transaction
return
end
end
/* currhold_id referenced by foreign key Currhold_Sec (currhold_id) */
/* Parent Update Cascade */
if update (currhold_id)
begin
update Currhold_Sec
set Currhold_Sec.currhold_id = inserted.currhold_id
from Currhold_Sec, deleted, inserted
where Currhold_Sec.currhold_id = deleted.currhold_id
end
/* currhold_id referenced by foreign key Currhold (forward_ref) */
/* Parent Update Cascade */
if update (currhold_id)
begin
update Currhold
set Currhold.forward_ref = inserted.currhold_id
from Currhold, deleted, inserted
where Currhold.forward_ref = deleted.currhold_id
end

/* A.G. */
/* If forward reference is set to some value for existing forward it means that
existing FX Forward is unwound.
Create Marked To Spot journal entry. */
/* Modified: 7/02/03 to calculate unrealized Gain / Loss differently and to set
discount to what's currently is sitting in discount field of the holding
instead of calculating it.
4/30/04 to add a condition when updating forward_ref field: do further
processing only if the holding of 'FR' derivative type since some 'IR'
holdings will be using forward_ref to indicate if IR Swap originated
from Swaption.
5/21/04 to create journal entry for IR or CD Swaps to reverse
statement value amount (composed of all 'MTFV' entries' amounts) when
hedge effectiveness flag for the holding is fliped from ineffective to
effective. */

DECLARE @currhold_id integer,
@transac_date datetime,
@tran_type varchar(8),
@entered_by varchar(30),
@unrl_gl_me numeric(14,4),
@unrl_gl_to_date numeric(14,4),
@unrl_gl_total numeric(14,2),
@discount numeric(14,2),
@fgn_notional numeric(14,2),
@der_type varchar(8),
@sv numeric(14,4)

SET @der_type = (SELECT der_type FROM inserted)
SET @currhold_id = (SELECT currhold_id FROM inserted)
SET @entered_by = (SELECT edited_by FROM inserted)

IF UPDATE (forward_ref)
BEGIN

IF @der_type = 'FR'
BEGIN
SET @fgn_notional = (SELECT fgn_notional FROM inserted)

IF (SELECT forward_ref FROM deleted) IS NULL AND
(SELECT forward_ref FROM inserted) IS NOT NULL
IF @fgn_notional > 0
BEGIN
/* Set variables values */

SET @transac_date = (SELECT trade_date
FROM currhold
WHERE currhold_id = (SELECT forward_ref FROM inserted))


/* Unrealized G/L for the period of time between last month-end
and the date when this update is happening */
SET @unrl_gl_to_date = dbo.Gain_Loss(@fgn_notional,
(SELECT fx_rate FROM FXCurr WHERE currency_code = @currency_code),
(SELECT spot_rate FROM inserted))

SET @unrl_gl_me = (SELECT der_unrl_gl_ytd_c FROM deleted)

SET @unrl_gl_total = (SELECT der_unrl_gl_ytd_c FROM inserted)

SET @discount = (SELECT discount FROM deleted)

/* Create journal entry */
EXEC Ins_Entry_Header_FR_MTS
@currhold_id,
@transac_date,
@entered_by,
@unrl_gl_to_date,
@unrl_gl_me,
@unrl_gl_total,
@discount
END /* @fgn_notional > 0 */
END /* IF @der_type = 'FR' */
END /* IF UPDATE (forward_ref) */

IF UPDATE (hedge_effective_flag)
BEGIN

IF @der_type IN ('IR', 'CD') AND
(SELECT hedge_effective_flag FROM deleted) = 0 AND
(SELECT hedge_effective_flag FROM inserted) = 1
BEGIN

SET @sv = (SELECT sv FROM deleted)

/* Create journal entry */
EXEC Ins_Entry_Header_IR_CD_RVRSMTFV
@currhold_id,
@sv,
@entered_by

END /* IF @der_type IN ('IR', 'CD') ... */
END /* IF UPDATE (hedge_effective_flag) */


chandra shekar
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/14/2004 :  16:54:15  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
one way to debug triggers is to create a "Trigger Log" table, and add entries to that table during the course of your trigger. then check out the table after your trigger has executed to see how it did. of course, it is up to you to decide what to store in that table, how often, and in what format.

and don't forget to disable that feature when you move to production !

- Jeff
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 09/14/2004 :  16:57:00  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
holy smokes -- are you familiar with foreign key constraints ? the first ~50 lines of your trigger can all be handled with a few basic SQL constraints. Why are you trying to do this manually?

And it is extremely imporant to understand that you need to always work with INSERTED on the assumption that it contains sets of rows that are inserted, not just one row ! That is why you are getting the specific error you mentioned.

My advice -- write down the business requirements of this trigger and start over from scratch. and use foreign key constraints between your tables to handle them.

- Jeff

Edited by - jsmith8858 on 09/14/2004 16:58:22
Go to Top of Page

cshekar
Starting Member

USA
49 Posts

Posted - 09/14/2004 :  16:57:18  Show Profile  Reply with Quote
Could please tell me how do I create log file on teh table
Trigger Log table, is that I have to put with in trigger to write to
log file or do I have to give log file name or what Thanks
help


chandra shekar
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 09/14/2004 :  16:59:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by cshekar


Hi tara,
here is the my trigger code which is taking from Inseted table so that means only one row
in inset=rted table..





That isn't true. inserted table contains all of the rows affected by the INSERT statement.

But that's not necessarily why it is failing. It has to do with the subquery returning more than one row. So if the subquery references the inserted table, then it's because the trigger isn't designed to handle more than one row in the inserted table. If the subquery references a real user table, then you need to figure out it is returning more than one row. You can usually fix this by changing the WHERE clause.

Your code isn't formatted with code tags so it's very hard to read and I can't find the subquery in there. Please edit your post with code tags.

Tara

Edited by - tkizer on 09/14/2004 17:13:18
Go to Top of Page

cshekar
Starting Member

USA
49 Posts

Posted - 09/14/2004 :  17:02:31  Show Profile  Reply with Quote
but same trigger works in Beta and developemnet but I do not known
some reason it is nor working in production I am having this problem
Server: Msg 512, Level 16, State 1, Procedure ut_Currhold, Line 132
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.


chandra shekar
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 09/14/2004 :  17:04:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
It has to do with the data. Please either post the query that contains the subquery or modify your post with code tags so that it is formatted for us.

Tara
Go to Top of Page

cshekar
Starting Member

USA
49 Posts

Posted - 09/14/2004 :  17:08:05  Show Profile  Reply with Quote
Here is the Sub Query with in trigger


IF @der_type = 'FR'
BEGIN
SET @fgn_notional = (SELECT fgn_notional FROM inserted)

IF (SELECT forward_ref FROM deleted) IS NULL AND
(SELECT forward_ref FROM inserted) IS NOT NULL
IF @fgn_notional > 0
BEGIN
/* Set variables values */

SET @transac_date = (SELECT trade_date
FROM currhold
WHERE currhold_id = (SELECT forward_ref FROM inserted))


/* Unrealized G/L for the period of time between last month-end
and the date when this update is happening */
SET @unrl_gl_to_date = dbo.Gain_Loss(@fgn_notional,
(SELECT fx_rate FROM FXCurr WHERE currency_code = @currency_code),
(SELECT spot_rate FROM inserted))


chandra shekar
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 09/14/2004 :  17:11:29  Show Profile  Visit tkizer's Homepage  Reply with Quote
Ok, your subquery is selecting from the inserted table, which can contain more than one row. That is not allowed in a subquery, thus the failure. Your trigger needs to be able to handle more than one row in the inserted table. A complete redesign of this trigger will be needed to correct.

Tara
Go to Top of Page

cshekar
Starting Member

USA
49 Posts

Posted - 09/14/2004 :  17:17:43  Show Profile  Reply with Quote
Thank you, I understand that Inserted table got all rows that's why it is taking more than one row but in development same trigger same subquery I am using I am able to update to table


chandra shekar
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 09/14/2004 :  17:20:47  Show Profile  Visit tkizer's Homepage  Reply with Quote
It's because in development, the data is different. Your INSERT statement in development only affected one row, thus only one row in the inserted table. You will have the same problem in development if you performed the actions that caused the INSERT to insert more than one row.

Tara
Go to Top of Page

cshekar
Starting Member

USA
49 Posts

Posted - 09/14/2004 :  17:28:03  Show Profile  Reply with Quote
I did try to update only one from Query analyzer still I got same error
I have used this update statement
UPDATE currhold
SET inc_recv_ytd = 0
WHERE expire_flag = 0

still i got same error


chandra shekar
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 09/14/2004 :  17:30:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
So how many rows does this query return:

SELECT *
FROM currhold
WHERE expire_flag = 0

Tara
Go to Top of Page

cshekar
Starting Member

USA
49 Posts

Posted - 09/14/2004 :  17:31:48  Show Profile  Reply with Quote
There are about 500 rows effect by this query

chandra shekar
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 09/14/2004 :  17:35:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
Then your UPDATE statement that you posted will cause 500 rows to be put into the inserted table causing your error.

Tara
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000