Trigger Problems 1

[bryant89]

I am using Interdev developer and MSSQL Server 2000. I have written a trigger where when a change is made in a table(tblFirm) the trigger catches that and updates another table(tblExternalTeam). So when the firm name is updated in the firm table the trigger updates any of the same firm names that exist in the tblExternalTeam table.

Now the trigger works fine when entering in the data directly to the table.

The problem exists when I use a recordset in my interdev application to make the changes. The db is independant of the application so the trigger shouldnt matter. So if I make changes to the firm name using a recordset in the application developed with interdev I can go look in the tables and the changes are made but I get a recordset update error when using the web application even though the changes are made. The error is this:
Error Type:
Microsoft Cursor Engine (0x80004005)
Key column information is insufficient or incorrect. Too many rows were affected by update.
/networkforms/_ScriptLibrary/Recordset.ASP, line 311

This is very confusing because the update is made on the table and the trigger fires and the updates are made in the other table which should be completely independant from the application. But if I remove the trigger from the db the application works fine only thing is my updates to tblExternalTeam are not made. Anyone have any suggestions as to why I would be getting this recordset error? All the recordset does is update the record in the tblFirm from there the application shouldn't even know what the db(trigger) does.

Thanks in advance
Bryant

 

[tlbroadbent]

The trigger isn't really run separately from the application. The application doesn't "know" about the trigger but the trigger fires and attempts to update tblExternalTeam in the same transaction as the update to tblFirm. It is all run under the same login.

The problem could be caused by lack of a primary key or unique index on one or both of the tables. Or it could happen if the primary key of tblFirm is not part of the recordset. Terry

http://members.home.net/tlbroadbent/index.htm

 

[bryant89]

both tables have a unique key the for instance the firmid of 13 is stored in the tblFirm table and then the firmid is also stored in the tblExternalTeam along with another key unique identifier. I thought it might be a problem with cascade update and delet but these two tables aren't in a relationship and I tried turning cascading off on both tables but I still get the recordset error. The recordset does use firmID to find the appropriate record to update. The recordset is based on a stored procedure that selects the info from the table then I just do rcs.update record which should just affect that record in the tblFirm table then the trigger should take care of the updates to tblExternalTeam. When I remove the trigger from tblFirm I dont get a recordset error but then my updates arent made to tblExternalTeam so I am thinking of making another recordset and doing the update in my asp code but thats bad news if I want to connect to this db with some other interface.

I dunno man
Thanks for the help

 

[tlbroadbent]

Try adding SET NOCOUNT ON as the first line of the trigger.
Terry

http://members.home.net/tlbroadbent/index.htm

 

[bryant89]

Thanks very much Terry. That did it. But why???
I put the SET NOCOUNT ON right after the as in my trigger code and it works fine now.
I am not positive but I think the SET NOCOUNT ON tells the trigger not to return any field names or anything like that. right. I am not sure but thanks very much it works...

Bryant

 

[tlbroadbent]

You are right about the NO COUNT ON inhibiting rows from returning. Under most conditions (all?) you don't want to return any rows from a Trigger. It doesn't make sense to return the rows affected count from a trigger because the application isn't set up to handle output from a Trigger. Terry

http://members.home.net/tlbroadbent/index.htm

 

[bryant89]

ya that makes total sense now. Thanks alot Terry

Bryant

 

[sguslan]

I wrote an insert/update trigger to verify some columns by doing some simple cotextual analysis to fix a
one-byte-to-two-bytes conversion problems. The source data is an ugly mainframe db2/ebcdic, while the target is of course a pretty sql7.
I am facing a problem updating the data with just sql statements. It seems i can only update/insert using a CURSOR in the trigger logic AND commiting after every row updated/inserted by the PowerBuilder program which fetches data from db2.
Please help ...How can i do away with the cursor and commit every 100 rows for example ? ....