Trigger programming SQL 2005 vs SQL 2000

Hallo

I have a trigger which works well on SQL Server 2000. Now, I transferd the Database to SQL Server 2005, the "normal" database things works all fine, I have only troubles with 1 trigger. I debugged it and found the problem must be here, but I can't see it:

SET @whereClause = '(xsapnumber LIKE ''%' + @p_sapno + '%'')'

exec ('declare LiteratureCursor Cursor For Select lid, xsapnumber, xcompany, xliteraturetype, xproductrange3, xcountry, xlanguage2, xapplication3, xliteraturdocument, xnewtcdesign, xremarks FROM xsalesliterature WHERE ' + @whereClause + ' ORDER by lid')

OPEN LiteratureCursor <<===== HERE THE TRIGGER FAILS ON 2005 but works on 2000

Has anyone of you an idea

Thanks

Hans

Try opening the cursor inside the batch being executed using EXEC(...) and also declare the cusrsor as "global" explicitly.

Example:

create table dbo.t1 (

c1 int not null identity unique

create trigger tr_t1_ins on dbo.t1

for insert

declare @i int

exec('declare c cursor global static read_only for select orderid from dbo.orders where orderid = 10250 open c')

if CURSOR_STATUS('global', 'c') = 1

while 1 = 1

begin

fetch next from c into @i

if @@error != 0 or @@fetch_status != 0 break

print @i

end

close c

deallocate c

insert into dbo.t1 default values

drop table dbo.t1

AMB

@hunchback

Thanks!! That worked! But why Is it a thing of new permissions

@Arnie,

Thanks also for your Feedback. In my case, the SQL Server Database is generated automatically by an intranet software product. By designing the UI, the Database ist automatically generated for all fields, master/detail pages, etc. So the only way to do some automatics is with triggers or do it in ASP. In my first works I used triggers, now, I only do this automatics in ASP. Doing some queries with user-defined parameters, I show this parameters as textfields in an ASP Page and save it to a SQL Server Table. This table has a trigger which takes this parameters out of the "inserted"-object, delete all records and fill the table with data from other tables by using a cursor. The intranet Software moves now to a new page where this table "xfindsalesliteratu" is shown in a datagrid. So it isn't possible to change the behavior of the intranet software, I have to do it in that way. The whole trigger looks like:

CREATE trigger [dbo].[Salesliterature] ON [dbo].[xfindsalesliteratu]

FOR INSERT, UPDATE

declare @lid int

declare @sapno varchar(255)

declare @company varchar(255)

declare @literaturetype varchar(255)

declare @productrange varchar(255)

declare @country varchar(255)

declare @language varchar(255)

declare @application varchar(255)

declare @document int

declare @newtcdesign int

declare @remarks varchar(3000)

declare @letztelid int

declare @datumedit datetime

declare @user int

declare @p_sapno varchar(255)

declare @p_company varchar(255)

declare @p_literaturetype varchar(255)

declare @p_productrange varchar(255)

declare @p_country varchar(255)

declare @p_language varchar(255)

declare @p_application varchar(255)

declare @p_STcompany varchar(255)

declare @p_STliteraturetype varchar(255)

declare @p_STproductrange varchar(255)

declare @p_STcountry varchar(255)

declare @p_STlanguage varchar(255)

declare @p_STapplication varchar(255)

declare @p_AktuellerUser varchar(255)

declare @whereClause varchar(2000)

SELECT @p_sapno = xsapno FROM inserted

SELECT @p_company = xcompany FROM inserted

SELECT @p_literaturetype = xliteraturetype FROM inserted

SELECT @p_productrange = xproductrange FROM inserted

SELECT @p_country = xcountry FROM inserted

SELECT @p_language = xlanguage FROM inserted

SELECT @p_application = xapplication FROM inserted

SELECT @p_STcompany = xsearchtermcompany FROM inserted

SELECT @p_STliteraturetype = xsearchtermliterat FROM inserted

SELECT @p_STproductrange = xsearchtermproduct FROM inserted

SELECT @p_STcountry = xsearchtermcountry FROM inserted

SELECT @p_STlanguage = xsearchtermlanguag FROM inserted

SELECT @p_STapplication = xsearchtermapplica FROM inserted

SELECT @datumedit = dtedit FROM inserted

SELECT @user = luserid FROM inserted

SELECT @p_AktuellerUser = xaktuelleruser FROM inserted

SET @whereClause=''

if (LEN(@p_sapno)>0)

if (LEN(@whereClause)>0) SET @whereClause = @whereclause + ' AND '

SET @whereClause = @whereClause + '(xsapnumber LIKE ''%' + @p_sapno + '%'')'

if (LEN(@p_company)>0)

if (LEN(@whereClause)>0) SET @whereClause = @whereclause + ' AND '

if (LEN(@p_STcompany)>0)

BEGIN

SET @whereClause = @whereClause + '(xcompany ' + @p_STcompany + ')'

END

ELSE

BEGIN

SET @whereClause = @whereClause + '(xcompany = ''' + @p_company + ''')'

END

if (LEN(@p_literaturetype)>0)

if (LEN(@whereClause)>0) SET @whereClause = @whereclause + ' AND '

if (LEN(@p_STliteraturetype)>0)

BEGIN

SET @whereClause = @whereClause + '(xliteraturetype ' + @p_STliteraturetype + ')'

END

ELSE

BEGIN

SET @whereClause = @whereClause + '(xliteraturetype = ''' + @p_literaturetype + ''')'

END

if (LEN(@p_productrange)>0)

if (LEN(@whereClause)>0) SET @whereClause = @whereclause + ' AND '

if (LEN(@p_STproductrange)>0)

BEGIN

SET @whereClause = @whereClause + '(xproductrange3 ' + @p_STproductrange + ')'

END

ELSE

BEGIN

SET @whereClause = @whereClause + '(xproductrange3 LIKE ''%' + @p_productrange + '%'')'

END

if (LEN(@p_country)>0)

if (LEN(@whereClause)>0) SET @whereClause = @whereclause + ' AND '

if (LEN(@p_STcountry)>0)

BEGIN

SET @whereClause = @whereClause + '(xcountry ' + @p_STcountry + ')'

END

ELSE

BEGIN

SET @whereClause = @whereClause + '(xcountry = ''' + @p_country + ''')'

END

if (LEN(@p_application)>0)

if (LEN(@whereClause)>0) SET @whereClause = @whereclause + ' AND '

if (LEN(@p_STapplication)>0)

BEGIN

SET @whereClause = @whereClause + '(xapplication3 ' + @p_STapplication + ')'

END

ELSE

BEGIN

SET @whereClause = @whereClause + '(xapplication3 LIKE ''%' + @p_application + '%'')'

END

if (LEN(@p_language)>0)

if (LEN(@whereClause)>0) SET @whereClause = @whereclause + ' AND '

if (LEN(@p_STlanguage)>0)

BEGIN

SET @whereClause = @whereClause + '(xlanguage2 ' + @p_STlanguage + ')'

END

ELSE

BEGIN

SET @whereClause = @whereClause + '(xlanguage2 LIKE ''%' + @p_language + '%'')'

END

DELETE FROM xfindsalesliteratu WHERE xaktuelleruser = @p_AktuellerUser

exec ('declare LiteratureCursor Cursor global static read_only For Select lid, xsapnumber, xcompany, xliteraturetype, xproductrange3, xcountry, xlanguage2, xapplication3, xliteraturdocument, xnewtcdesign, xremarks FROM xsalesliterature WHERE ' + @whereClause + ' ORDER by lid OPEN LiteratureCursor')

if CURSOR_STATUS('global', 'LiteratureCursor') = 1

WHILE (0=0)

BEGIN

FETCH NEXT FROM LiteratureCursor INTO @lid, @sapno, @company, @literaturetype, @productrange, @country, @language, @application, @document, @newtcdesign, @remarks

IF (@@error != 0 or @@fetch_status != 0) Break

IF EXISTS (SELECT lid FROM xfindsalesliteratu)

BEGIN

SELECT @letztelid = MAX(lid)+1 FROM xfindsalesliteratu

END

ELSE

BEGIN

SET @letztelid = 1

END

INSERT INTO xfindsalesliteratu (lid, dtedit, luserid, xsapno, xcompany, xliteraturetype, xproductrange, xcountry, xlanguage, xapplication, xliteraturedocumen, xnewtcdesign, xremark, xaktuelleruser)

SELECT @letztelid, @datumedit, @user, @sapno, @company, @literaturetype, @productrange, @country, @language, @application, @document, @newtcdesign, @remarks, @p_AktuellerUser

END

Close LiteratureCursor

Deallocate LiteratureCursor

Sorry, I do not have an explanation about why this change in behavior from 2000 to 2005. I have seem this question several times in the newsgroups and decided to find an approach to make it work.

AMB

Hans,

Thanks for posting the code.

I believe you can eliminate the CURSOR with the following alterations: (I haven't spend any time with the remainer of the code, but I suspect it can be improved as well...)

First, the code marked in Yellow (below) can be replace with a FUNCTION similar to this -whether or not you choose to revise the CURSOR:

And then build the INSERT...SELECT statement and execute as dynamic SQL, replacing the entire CURSOR operation (similar to the way you are building the CREATE CURSOR statement and executing it as dynamic SQL.)

Hello Arnie,

Thanks for your suggestions. I tested this approach, but I have a Problem with this function, because when I write

INSERT INTO xfindsalesliteratu (lid, xsapno, xcompany, xliteraturetype, xproductrange, xcountry, xlanguage, xapplication, xliteraturedocumen, xnewtcdesign, xremark) Select dbo.GetNextLID(), xsapnumber, xcompany, xliteraturetype, xproductrange3, xcountry, xlanguage2, xapplication3, xliteraturdocument, xnewtcdesign, xremarks FROM xsalesliterature WHERE xcompany LIKE '%Austria%' ORDER by lid

I know it, but I can't change this because the tables are autogenerated by the intranet software every time a structural change is made. So my problem now works with the solution above and its ok for now, because the new version of the intranet does this things in ASP for being independant of the database (SQL Server, Access, ...).

Thanks

Hans