Dawn This is intriguing and certainly not immediately obvious what the issue is. An AFTER INSERT trigger should work exactly the same whether the rows are inserted via a form, or an INSERT command.
Since the insert IS working and the rows in Jobs ARE correctly updated, the error message is troubling, but not a show stopper, right? Several things to try to track it down: 1. Try inserting just ONE row and see if you get the same error. If not, try TWO. Keep pushing it til you get the error. It COULD be a timing issue: the trigger procedure may be trying to run while the next row is being inserted. Remember, in multi-user, an INSERT DOES require exclusive access to the table in question. However, your trigger is NOT inserting or updating rows in SlzeData. 2. Try changing the procedure so it creates a temp table, inserts the rows from the view into that table, and then does the rest off that table. Does that remove the error? David 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: Saturday, March 19, 2005 9:19 PM Subject: [RBG7-L] - RE: Lock error on table with trigger David Sorry I wasn't very clear. SizeData is the name of the table that has the trigger - it contains information about the images on a job. The AFTER INSERT trigger procedure is the one I pasted into the email, though there are also after update and after delete triggers on the same table that do the same thing. Basically, I'm trying to lookup all the colors required for each image on a job, calculate the total square inches of each color and insert (or update) the summarized color list into the jobcol table. (Many images on a job, many colors for each image and not necessarily all the same colors for each image.) Any time something in the SizeData table changes, I want to recalculate the color information. (I discussed this in part of my conference presentation - this currently is a manual step initiated in the prepress dept AFTER the whole order has been entered. We're moving it "up the line" so it happens on order entry. Since we're using the info for more and more things, it's important for it to be accurate at all times.) As you can see by looking at the code, the procedure declares a cursor on the jobcolorview view. This view consists of two tables - one being the sizedata table, the other is the colors required for each part# in sizedata. Like I said, the procedure itself works as I want it to. If I edit a job and change or add a part number, it recalcs and updates the jobcol table. The lock error message occurred when I was trying to insert more than one row into the SizeData table through some other code that I want to use to convert a quote into a work order. Insert partno, etc. Into Sizedata select partno, etc. fro Qimages where quotenum = .vquotenum There were four rows in the Qimages table, all four were inserted, but I got that 'internal lock error' message. Since I figure that's definitely not good, even though the insert works, I must be doing something wrong - or not exactly right. Dawn -----Original Message----- From: David M. Blocker [mailto:[EMAIL PROTECTED] Sent: Saturday, March 19, 2005 12:59 PM To: RBG7-L Mailing List Subject: [RBG7-L] - RE: Lock error on table with trigger 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
