Dawn It would help if you gave us more info.
Which table has the trigger? It looks like it's SizeData, yes? But your original email said it was the "images" table. What kind of trigger is it? INSERT, UPDATE, DELETE? BEFORE or AFTER. It looks like an INSERT trigger, yes? And the trigger is placed on a VIEW, not a table? What is the view? David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: "Dawn Oakes" <[EMAIL PROTECTED]> To: "RBG7-L Mailing List" <[email protected]> Sent: Friday, March 18, 2005 9:35 PM Subject: [RBG7-L] - RE: Lock error on table with trigger Well the half hour ride from the office to home gave me some time to think about this. I just tried creating a temp table, inserting the rows into the temp table, doing updates to the temp table and appending the finished rows to the table with the trigger. No errors, no hanging. Solves the problem for now, but still wondering if there's a problem with my logic. Here's the stored procedure that the insert trigger calls. If someone can see something that would cause the error, let me know. Thanks SELECT jonum INTO pvjonum FROM sizedata WHERE CURRENT OF SYS_NEW SET VAR fvnumcol TEXT = NULL SET VAR vnumexists TEXT = NULL SET VAR vmaxcolnum INTEGER = NULL SET VAR vcolnum INTEGER = NULL DECLARE c1 CURSOR FOR SELECT numcol FROM jobcolorview ---contains table sizedata WHERE jonum = .pvjonum AND numcol IS NOT NULL OPEN c1 FETCH c1 INTO fvnumcol WHILE SQLCODE <> 100 THEN SET VAR vnumexists = numcol IN jobcol WHERE jonum = .pvjonum AND numcol = .fvnumcol IF vnumexists IS NOT NULL THEN UPDATE jobcol SET wire = t2.wire, collac = t2.collac, sqins = t2.colsqins + FROM jobcol t1, jobcolorview t2 WHERE t1.jonum = t2.jonum AND t1.numcol = t2.numcol and t1.jonum = .pvjonum AND t1.numcol = .fvnumcol UPDATE jobcol SET inkloading = (ROUND(t2.newloading,3)), targetvisc = t2.newvisc + FROM jobcol t1, colornumbers t2 WHERE t2.numcol = t1.numcol AND t1.jonum = .pvjonum and t1.numcol = .fvnumcol ELSE SET VAR vmaxcolnum = (MAX(colnum)) IN jobcol WHERE jonum = .pvjonum IF vmaxcolnum > 0 THEN SET VAR vcolnum = (.vmaxcolnum + 1) ELSE SET VAR vcolnum = 1 ENDIF INSERT INTO jobcol jonum,numcol,colnum,sqins,collac,wire + SELECT .pvjonum, .fvnumcol, .vcolnum, colsqins, collac, wire + FROM jobcolorview WHERE jonum = .pvjonum AND numcol = .fvnumcol UPDATE jobcol SET inkloading = (ROUND(t2.newloading,3)), targetvisc = t2.newvisc + FROM jobcol t1, colornumbers t2 WHERE t2.numcol = t1.numcol AND t1.jonum = .pvjonum ENDIF FETCH c1 INTO fvnumcol ENDWHILE CLEAR VAR fvnumcol, pvjonum, vmaxcolnum, vcolnum, vnumexists DROP CURSOR c1 RETURN -----Original Message----- From: Dawn Oakes Sent: Friday, March 18, 2005 5:42 PM To: RBG7-L Mailing List Subject: [RBG7-L] - Lock error on table with trigger I am trying to automate a process that would otherwise require users to click a button to 'compile' information from one table (formulas) based on info entered in a workorder (images) and update another table (colors). This is very important, I'd rather not rely on users remembering to do that. So I added triggers to the images table. The triggers run stored procedures that declare a cursor on a VIEW that contains the table that has the trigger (confused?). I don't know if that is a problem or not - it works like I want it to, but now I'm seeing something not so good. In another part of the program, I'm inserting rows into this images table through code (INSERT INTO TABLENAME (COLUMNS) SELECT COLUMNS FRO ANOTHER TABLE WHERE COLUMN = VARIABLE). With multi set on, the insert works, but I get an error message saying "Internal lock error. Have all users disconnect from the database. Then have on user re-connect." Which of course, I immediately do. (This is a test copy, I'm not really concerned about anyone else using it.) If I connect with multi off, I do not get that message, but the program hangs on a declare cursor (on that table) a few lines down from the insert. I reloaded, etc with no results. I suspected the triggers were the issue so I removed the triggers from the table, and voila no errors. So...is it not a good idea to trigger a cursor on a view that contains the table that has the trigger? (I feel like I've read that in a Dr. Seuss book!) Or is that OK, and I have a different problem? Dawn
