trigger causes 'dataset not in edit mode' error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

trigger causes 'dataset not in edit mode' error

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-02-06 : 17:02:36

Hi All,
I can't seem to figure out the problem here. I have this trigger that I wrote to prevent a user from cancelling a ticket(PS_TKT_HDR) that is on the schedule(TimeTable). This works ok if I change @a to a ticket number like '12345' and run it in QA, but if I put it on PS_TKT_HDR as a trigger I get 'dataset not in edit mode' when I try to open a ticket in our app. There are other triggers on this table; could that be the problem? I've looked and looked, but I just can't get it...
Help!

begin
declare
@DAT varchar(12),
@MSG varchar(60),
@1 T_DOC_NO,
@2 T_DOC_NO,
@3 T_DOC_NO,
@4 T_DOC_NO,
@5 T_DOC_NO,
@6 T_DOC_NO,
@7 T_DOC_NO,
@8 T_DOC_NO,
@9 T_DOC_NO,
@10 T_DOC_NO,
@11 T_DOC_NO,
@12 T_DOC_NO,
@13 T_DOC_NO,
@14 T_DOC_NO,
@15 T_DOC_NO,
@16 T_DOC_NO,
@17 T_DOC_NO,
@a T_DOC_NO

set @a=(select i.tkt_no
 from inserted i
inner join ps_tkt_hdr h
on h.tkt_no=i.tkt_no
and h.str_id=i.str_id
and h.sta_id=i.sta_id
where i.doc_stat='C'
and i.DOC_TYP='O')
if @a is not null
begin
DECLARE chk_Cursor CURSOR FOR
SELECT svc_dat,time01,time02,time04,time05,time07,time08,time10,time11,time13,time14,time16,time17
FROM timetable
where svc_dat>getdate()

OPEN chk_Cursor

FETCH NEXT FROM chk_Cursor
into @DAT,@1,@2,@4,@5,@7,@8,@10,@11,@13,@14,@16,@17
WHILE @@FETCH_STATUS = 0
BEGIN
if @a in(@1,@2,@4,@5,@7,@8,@10,@11,@13,@14,@16,@17)
begin
set @MSG='**Cannot Cancel: Ticket is scheduled for '+ @DAT
raiserror(@MSG,18,1)
end
FETCH NEXT FROM chk_Cursor
into @DAT,@1,@2,@4,@5,@7,@8,@10,@11,@13,@14,@16,@17
END
end
CLOSE chk_Cursor
DEALLOCATE chk_Cursor
end

There's never enough time to type code right,
but always enough time for a hotfix...

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-06 : 17:29:02

Haven't looked at it but try adding a set nocount on.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-02-06 : 20:44:31

Hey nr,
No go, I tried that. With my limited knowledge, that was the only thing I could think of. I read something in one of my textbooks (finally going to school) about triggers not working well together if they are poorly written, but I couldn't quite figure it out. If you can tell me what to look for, I would like to try and figure it out on my own (HAHAHAHAHA!) first. No better way to learn how to solve a problem than to jump right into the fire...
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-06 : 21:08:21

I take it the trigger is on ps_tkt_hdr and you are only catering for single row updares?
if so

if exists (select * from inserted where i.doc_stat='C' and i.DOC_TYP='O')
begin
select top 1 @msg = '**Cannot Cancel: Ticket is scheduled for ' + convert(varchar(12),svc_dat)
from timetable t
join insertd i
on i.tkt_no in (time01,time02,time04,time05,time07,time08,time10,time11,time13,time14,time16,time17)
where t.svc_dat > getdate()
and i.doc_stat='C' -- not needed
and i.DOC_TYP='O' -- not needed
if @MSG is not null
begin
raiserror(@MSG,18,1)
rollback trn -- not needed if error is handled in app
end
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-02-08 : 12:31:26

Hey nr,
That did it, and I learned a better way to write the trigger without all those local variables...
THANKS!
Andy

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources