AFTER DELETE trigger question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

AFTER DELETE trigger question

Author

Topic

akashenk
Posting Yak Master

111 Posts

Posted - 2009-08-20 : 15:31:56

I have a table that contains an "ordering" column. This colummn is an integer which defines the order in which a subset of records should be retrieved. So my table contains columns:

Item_ID nchar(15)
Item_Name nvarchar(50)
Item_Category nvarchar(15)
Item_DisplayOrder int

Some example data is:

"Id1", "Item G", "Category A", 1
"Id2" "Item H", "Category A", 2
"Id3", "Item C", "Category A", 3
"Id4", "Item A", "Category A", 4
"Id5", "Item E", "Category A", 5
"Id6", "Item F", "Category B", 1
"Id7", "Item T", "Category B", 2

The quetion I have is.. is there a way, to update the DisplayOrder olumn when a record or records are deleted so that the order is always 1, 2, 3... etc up to the highest integer for the given category

My first attempt was to create a Trigger AFTER delete on this table:

CREATE TRIGGER [t_deleteItem]
ON [dbo].[Items]
AFTER DELETE
AS

SET NOCOUNT ON

DECLARE @tempTable TABLE
(
T_ID nchar(15) NOT NULL PRIMARY KEY,
T_DisplayOrder int,
T_Category nvarchar(15)
)

INSERT INTO @tempTable(T_ID, T_DisplayOrder, T_Category)
SELECT D.Item_ID, D.Item_DisplayOrder, D.Item_Category
FROM deleted as D

UPDATE dbo.[Items]
SET displayorder=displayorder-1
WHERE
EXISTS
(SELECT T1.T_ID
FROM @tempTable T1
WHERE T1.T_Category = Item_Category AND DisplayOrder > T1.T_DisplayOrder)

This works when I am only deleteing a single row. I modified it in the following way to handle deletion of multiple rows:

I added the following lines:

DECLARE @deleteCount int
SET @deleteCount = 0
SELECT @deleteCount = COUNT(T_ID)
FROM @tempTable --(By the way, is there some quicker way of accessing the size of the Deleted table?)

and changed the SET clause in the update statement from

SET displayorder=displayorder-1

to

SET displayorder=displayorder-@deleteCount

My problem is... this only works if the records I am deleting are contiguous ie, let's say I wanted to delete Id3 and Id4 from my sample data. In this case, the records would be deleted and the display order for Id4 and Id5 would be correctly set to 2 and 3, respectivley. However, I don't always delete contiguious records. What if I wanted to delete Id3 and Id5? My trigger doesn't work in this case. Can anyone help?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-20 : 15:43:00

Well, first question is - no matter what rows you delete the display order will still be correct, right? They may not be sequential but does that matter?

Be One with the Optimizer
TG

akashenk
Posting Yak Master

111 Posts

Posted - 2009-08-20 : 15:50:57

quote:
Originally posted by TG

Well, first question is - no matter what rows you delete the display order will still be correct, right? They may not be sequential but does that matter?

Be One with the Optimizer
TG

Well, you're right that the display order will be fine regardless of which records I delete, so in theory, I don't have to update this column. however, at some point I will implement an ability to "insert" new items in between existing ones, and I fear that having display orders which are not properly sequenced (ie without gaps) would cause issues.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-20 : 16:32:22

How about this then:


declare @t table
(Item_ID nchar(15)
,Item_Name nvarchar(50)
,Item_Category nvarchar(15)
,Item_DisplayOrder int)

insert @t

select 'Id1', 'Item G', 'Category A', 1 union all
select 'Id2', 'Item H', 'Category A', 2 union all
select 'Id3', 'Item C', 'Category A', 3 union all
select 'Id4', 'Item A', 'Category A', 4 union all
select 'Id5', 'Item E', 'Category A', 5 union all
select 'Id6', 'Item F', 'Category B', 1 union all
select 'Id6', 'Item T', 'Category B', 2

select * from @t

delete @t where item_category = 'Category A' and Item_ID in ('Id3', 'Id5')

update t set
       t.item_displayOrder = (select count(*) 
                            from @t 
                            where item_category = t.item_category 
                            and item_displayOrder <= t.item_displayorder)
from   @t t
where  t.item_category = 'Category A'

select * from @t

OUTPUT:
Item_ID         Item_Name                                          Item_Category   Item_DisplayOrder
--------------- -------------------------------------------------- --------------- -----------------
Id1             Item G                                             Category A      1
Id2             Item H                                             Category A      2
Id3             Item C                                             Category A      3
Id4             Item A                                             Category A      4
Id5             Item E                                             Category A      5
Id6             Item F                                             Category B      1
Id6             Item T                                             Category B      2


Item_ID         Item_Name                                          Item_Category   Item_DisplayOrder
--------------- -------------------------------------------------- --------------- -----------------
Id1             Item G                                             Category A      1
Id2             Item H                                             Category A      2
Id4             Item A                                             Category A      3
Id6             Item F                                             Category B      1
Id6             Item T                                             Category B      2

EDIT:
To convert this to an after deltete trigger then replace the WHERE clause with this:


inner join (select item_category from deleted group by item_category) d
       on d.item_category = t.item_category

Be One with the Optimizer
TG

akashenk
Posting Yak Master

111 Posts

Posted - 2009-08-20 : 17:31:53

Thanks! That seems to do the trick. You may want to fix a typo in your data display (i fixed it in my original post)... There should be an Id6 and Id7 in the example records, not two Id6 rows.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources