September 4, 2004 at 3:52 pm
I have a trigger on a "expense report" table that fires on inserts & updates. Basically the trigger does 2 things: 1) if an expense is of "type" visa, it validates that the "merchant" field is valid, and 2) if the "expense description" is personal car mileage (miles > 0), it automatically calculates the expense amount using a user-defined function; also, I use a cursor on the inserted table so that the trigger can handle multi-row inserts/updates. The trigger compiles fine, however, when I try to update a row in table, I always get the error: "Another user has modified the contents of this table...A cursor with the name "curInserted" does not exist..." Any ideas on what the problem could be (trigger code below).
Also, really dumb question: How do I open the debugger? I see it referenced in online books, but for the life of me I cannot figure out how I am supposed to get into debugger mode...
CREATE TRIGGER trgExpenseIU ON dbo.tblExpenses
FOR INSERT, UPDATE
AS
DECLARE
@id int,
@miles int,
@date datetime,
@type nvarchar(50),
@merchant nvarchar(50)
DECLARE curInserted CURSOR
FOR SELECT Expense_PK, TransDate, Miles, Type, Merchant FROM Inserted
OPEN curInserted
FETCH NEXT FROM curInserted
INTO @id, @date, @miles, @type, @merchant
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@type = 'Visa' and @merchant is null)
BEGIN
RAISERROR ('Merchant must be specified for Visa Expenses', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
IF @miles > 0
BEGIN
UPDATE dbo.tblExpenses
SET Amount = dbo.fncMileageRate(@date) * Miles,
ExpDesc_FK = 'Personal Car Mileage',
Type = 'Cash'
WHERE Expense_PK = @id
END
FETCH NEXT FROM curInserted
INTO @id, @date, @miles, @type, @merchant
END
CLOSE curInserted
DEALLOCATE curInsterted
September 6, 2004 at 12:27 am
Your First Problem is that you did the spelling mistake in deallocating the cursor curInsterted instead of curInserted.
To debug your trigger ,first install the SQL server debugger and then in query analyzer execute the system strored procedure sp_sdidebug with parameter 'legacy_on'. now open object browser with f8 key and right click on the procedure and select the last option debug. thats 'it now you start debugging.
But, you will come to know that there is no option to debug the triggers and user defined function here works a trick...
create one temporary procedure for debugging purpose only . i generally create procedure named AAA because in object browser it comes first to select, in order.
now in strored procedure write the code which invokes you sp/udfn or trigger for example
Alter Procedure AAA
AS
update table set F=F+1
Now debug the procedue AAA which will take you to the trigger which is for update
So happy debugging,
Rohit
Rohit
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply