multiple line triggers

Spacedog

I've written a few triggers like this for the document imaging system I've been working on, this one should update the size of a document on the doc' table when images are deleted from the system. however, if a range if images are deleted, only the first document in the reange is updated. do I have to put a loop into the script to cycle through the deleted recordset or is there a way of combining the tables so it can be accomplished in one pass?


/****** Object: Trigger dbo.trigDelImages Script Date: 07/04/2003 00:07:06 ******/
CREATE TRIGGER trigDelImages ON [dbo].[Images]
FOR DELETE
AS

declare @DocNo int
declare @Size bigint
declare @NoPages int

--@docno = deleted image doc, @Size = size of remaining files
--@no pages = # of pages in document
select @DocNo = docno from deleted
select @Size = sum(imagesize) from images where docno = @docNo
select @NoPages = count(*) from images where docno = @docno

UPDATE document
SET DocSize = @Size, NoPages = @NoPages
WHERE docno = @DocNo

Typedef

Use a cursor Luke.

Spacedog

Luke?

cursor - elaborate please.

kayos

ahhhh why dont you just join deleted in to the update statement and get rid of the variables altogether... as for cursors stay away from them, they are evil .... SQL is a set based language and cursors do things on a row by row basis and carry a large over head...

kayos

Evil Phil

<asking as I'm new to SQL Server tbh />

So Kayos would

UPDATE 
document, images, deleted 
SET 
document.DocSize = (Select sum(imagesize) from images where images.docno = deleted.docno),
document.NoPages = (SELECT count(*) from images where images.docno = deleted.docno)
WHERE 
images.docno = deleted.docno

do the same thing?

amen

cusors do have their use though
ms sql was a set based from 2000 onwards prior to that it was a bit of row and set based

Space dog how are you deleting the images ?
the trigger should fire every time u delete an image from the table

bonkey

No need for a cursor there at all at all. Horrible slow things they are too Avoid unless absolutely necessary.

Here's how I'd do it....I think.

Not having the tables to hand, I've made one or two small assumptions, and I havent been able to test it obviously

I tried recoding it to avoid the nested query, but I dont think I can work the grouping functions into the table-definitions supported by UPDATE syntax, so this was the easiest way around it.

If performance was an issue, I'd consider just adding/subtracting values from docSize and NoPages on insert/update/delete rather than recalculating them from scratch. Probably a bit more work overall, but also probably faster...although like I said it would depend on your data-set size.

Anyway...the code.

CREATE TRIGGER trigDelImages ON [dbo].[Images] 
FOR DELETE 
AS

UPDATE document
SET  document.DocSize = t1.docSize,
     NoPages = t1.docCount
FROM document, (SELECT  deleted.DocNo
                        sum(images.imageSize) as docSize,
                        count(*) as docCount
                  FROM  deleted,
                        images
                  WHERE deleted.docno = images.docno
                  GROUP BY deleted.docno
               ) t1
WHERE document.DocNo = t1.DocNo

bonkey

Originally posted by amen
ms sql was a set based from 2000 onwards prior to that it was a bit of row and set based

Emmm....huh?

SQL 2000 was an incremental release over SQL 7.0 - which was the first complete rewrite since MS acquired the product. So if anything changed, it was in the 6.5 to 7.0 transition, not 7.0 to 2000.

And I'm not sure what you mean that it used to be "row and set based" anyway....if anything, SQL 7.0 gave more and more capability to operate at a row level then ever before. Hell, 6.5 didnt even support row-level locking.

jc

Typedef

Don't use a cursor if you can avoid one.

If however you want to 'loop' a cursor is how you'd do it, also it's useful to know what one is, just in case.