November 25, 2008 at 7:36 am
Hi All,
I'm struggling to migrate trigger logic from Interbase/Firebird to MS SQL server. My obstacle seems to be the fact that SQL server triggers are executed per statement instead of per row.
Consider the following situation:
I have an ITEM table containing item names and a PROP table containing descriptions for items. Both tables are linked by a many-to-many link table ITEMPROPLINK. The idea is that an "item" contains multiple "properties" while a single "property" can be used for multiple "items", hence the many-to-many relationship.
When an "item" is removed, the link table records are removed by cascade delete, this works perfect.
Now the tricky part comes: I wrote an "after delete" trigger in Interbase/Firebird on the link table that removes records from the PROPS table when they are no longer linked to any "item", i.e. when no links records exist anymore.
In Interbase this looks like:
CREATE TRIGGER ITEMPROPLINK_AFTDEL FOR ITEMPROPLINK ACTIVE AFTER DELETE POSITION 0 AS
DECLARE VARIABLE ORPHAN INTEGER;
BEGIN
/* retrieve # of link entries */
SELECT COUNT(*) FROM ITEMPROPLINK WHERE (LNK_PROP = OLD.LNK_PROP) INTO ORPHAN;
/* if 0 entries, remove "orphaned" detail record */
IF (ORPHAN = 0) THEN DELETE FROM PROPS WHERE (PRP_ID = OLD.LNK_PROP);
END ^
In SQL server I get to the point where I can identify the case when to remove the detail record, only at that position I don't have the PROP primary key value anymore. I was thinking about something like the following:
CREATE TRIGGER ITEMPROPLINK_AFTDEL ON ITEMPROPLINK AFTER DELETE AS
BEGIN
IF NOT EXISTS(SELECT COUNT (*) FROM ITEMPROPLINK WHERE LNK_PROP IN (SELECT LNK_PROP FROM DELETED))
BEGIN
/* where can i get the LNK_PROP value I used in the select from deleted statement */
DELETE FROM PROP WHERE (PRP_ID = xxx)
END
END
I didn't find anything similar to a FOR SELECT clause that could be used to evaluate each row.
Would it be possible to call a stored procedure for each entry in the deleted virtual table?
When all else fails I can still manage this behavior in my client app, but that involves more round-trips which i want to avoid, additionally this fails to performs this logic when deletes are performed from other connections.
Thanks in advance for some thoughts,
Koen
November 25, 2008 at 7:47 am
In SQL Server the correct way to avoid orphaned rows is to use DRI (Declared Referential Integrity) also known as foreign key constraints. When using DRI you can define what you want to happen when you update/delete a parent row(CASCADE options). You can choose to not allow the parent row to be updated/deleted until child rows are updated/deleted, cascade the action to the child rows, or set the foreign key column to the default value (if there is one) on the child rows. This is set individually on Update and Delete.
You can do it in a trigger as well, but it is not a best practice with SQL Server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 25, 2008 at 7:52 am
Here is an idea I'd do it if I were to do it in a trigger, which I wouldn't:
[font="Courier New"]CREATE TRIGGER ITEMPROPLINK_AFTDEL ON ITEMPROPLINK
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON -- you want this so you don't get a message back the UI
-- this will delete all prop rows where the id is in the virtual deleted table
DELETE
FROM
prop
WHERE
EXISTS (SELECT * FROM deleted WHERE deleted.lnkprop = prop.propid
END
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 25, 2008 at 8:11 am
Hi Jack,
In the link table I use foreign keys to ensure referential integrity. In thet PROPLINK table, the reference to the ITEM table is declared to cascade delete. When an ITEM table record is deleted containing multiple PROPS links, the PROPLINK table entries are automatically removed also. That's also the case when the virtual Deleted table in the AFTER DELETE trigger contains multiple rows.
As far as I can make up from the trigger example you posted the trigger will always (try to) delete records from the PROPS table when their links to the deleted ITEM record are removed from the link table (by the cascade delete mechanism). This will fail when those PROPS records contain links to other "item" entries, is such an approach acceptable? I only want to delete a prop when the virtual Deleted table contains the last link record referencing the prop record.
Does that make sense?
Greetings,
Koen
November 25, 2008 at 11:09 am
I think you understand my trigger better than I understand your design. Can you post the DDL for the 3 tables including the relationships like, itemproplink.prop_id = prop.prop_id ?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 25, 2008 at 1:15 pm
Hi Jack,
I composed some DDL (since this was an imaginary example) and provided some data as an example.
/* ITEM */
CREATE TABLE ITEM
(
ITM_ID INT NOT NULL,
ITM_DESCRIPTION VARCHAR(250) NULL,
CONSTRAINT PK_ITEM PRIMARY KEY NONCLUSTERED (ITM_ID)
)
GO
/* PROP */
CREATE TABLE PROP
(
PRP_ID INT NOT NULL,
PRP_DESCRIPTION VARCHAR(250) NULL,
CONSTRAINT PK_PROP PRIMARY KEY NONCLUSTERED (PRP_ID)
)
GO
/* ITEMPROP */
CREATE TABLE ITEMPROPLINK
(
LNK_ITEM INT NOT NULL,
LNK_PROP INT NOT NULL,
CONSTRAINT PK_ITEMPROPLINK PRIMARY KEY NONCLUSTERED (LNK_ITEM, LNK_PROP)
)
GO
ALTER TABLE ITEMPROPLINK ADD CONSTRAINT FK_LNK_ITEM
FOREIGN KEY (LNK_ITEM) REFERENCES ITEM
(ITM_ID)
ON DELETE CASCADE
ON UPDATE NO ACTION
GO
ALTER TABLE ITEMPROPLINK ADD CONSTRAINT FK_LNK_PROP
FOREIGN KEY (LNK_PROP) REFERENCES PROP
(PRP_ID)
ON DELETE CASCADE
ON UPDATE NO ACTION
GO
INSERT INTO ITEM (ITM_ID, ITM_DESCRIPTION) VALUES (1, 'Glass')
GO
INSERT INTO ITEM (ITM_ID, ITM_DESCRIPTION) VALUES (2, 'Cardboard')
GO
INSERT INTO PROP (PRP_ID, PRP_DESCRIPTION) VALUES (1, 'Transparent');
GO
INSERT INTO PROP (PRP_ID, PRP_DESCRIPTION) VALUES (2, 'Writable');
GO
INSERT INTO PROP (PRP_ID, PRP_DESCRIPTION) VALUES (3, 'Breakable');
GO
INSERT INTO PROP (PRP_ID, PRP_DESCRIPTION) VALUES (4, 'Feels cold');
GO
INSERT INTO PROP (PRP_ID, PRP_DESCRIPTION) VALUES (5, 'Used as packaging');
GO
INSERT INTO ITEMPROPLINK (LNK_ITEM, LNK_PROP) VALUES (1, 1)
GO
INSERT INTO ITEMPROPLINK (LNK_ITEM, LNK_PROP) VALUES (1, 2)
GO
INSERT INTO ITEMPROPLINK (LNK_ITEM, LNK_PROP) VALUES (1, 3)
GO
INSERT INTO ITEMPROPLINK (LNK_ITEM, LNK_PROP) VALUES (1, 4)
GO
INSERT INTO ITEMPROPLINK (LNK_ITEM, LNK_PROP) VALUES (1, 5)
GO
INSERT INTO ITEMPROPLINK (LNK_ITEM, LNK_PROP) VALUES (2, 2)
GO
INSERT INTO ITEMPROPLINK (LNK_ITEM, LNK_PROP) VALUES (2, 5)
GO
I want (would like very much :D) an "after delete" trigger or some other mechanism that removes records from the PROP table with PRP_ID 1,3 and 4 when I perform DELETE FROM ITEM WHERE (ITM_ID = 1).
Like now, due to cascading the related links (ITEMPROPLINK records) are removed but the prop records become orphaned. I want PROP records to be removed automatically when there are no more link records in the ITEMPROPLINK table.
DRI allows specifying "cascade", "update" or "no action" but no fancy stuff like what triggers allow...
Does this shed some light on what I'm looking for?
Anyway, already thanks a lot for looking into this..
Greetings from Belgium,
koen
November 25, 2008 at 2:01 pm
I think I understand what you are looking for now. I do have a questions.
Your design allows for a many to many relationship between prop and item through the ItemProp table, but in the sample data you do not have an instance where a single property applies to many items. Can this happen?
Technically, props that are not linked to an item are not orphans as there is not a direct relationship between Items and Props. How do Props and Items get linked? Is it possible that a prop that has the only item it is linked to deleted may be used for another item later? In this case you would not want to delete that prop since it may be used later, otherwise you would need to re-create a prop you deleted.
Here is a trigger that will do what you want, but as my questions indicate, I'm not sure it is necessary:
[font="Courier New"]CREATE TRIGGER trg_item_prop_link_del ON dbo.itemproplink
FOR DELETE
AS
SET NOCOUNT ON
/* The left join is the key */
DELETE
FROM
prop
WHERE
EXISTS (SELECT
*
FROM
deleted D LEFT JOIN
itemproplink IP ON
IP.lnk_prop = D.lnk_prop AND
D.lnk_item != IP.lnk_item
WHERE
IP.lnk_prop IS NULL
prop.prp_id = D.IP.lnk_prop)
RETURN
[/font]
I also suggest that you adopt a uniform naming convention so itm_id is itm_id in all tables, etc... Most SQL Server developers will automatically understand that an id column in a table with a different name is a foreign key while not necessarily knowing what lnk_item is. Also, with SQL Server there is no real need to abbreviate so best practices would suggest limiting abbreviations.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 26, 2008 at 1:10 am
Hi Jack,
Thanks for the reply, this actually does the trick. 🙂
In response to your questions:
Your design allows for a many to many relationship between prop and item through the ItemProp table, but in the sample data you do not have an instance where a single property applies to many items. Can this happen?
In the sample data provided, PROP records 2 and 5 (writable & used as packaging) are linked to both items (Glass and Cardboard). Since each ITEM record has multiple props and some props are linked to more then 1 item I consider this as many-to-many.
Technically, props that are not linked to an item are not orphans as there is not a direct relationship between Items and Props. How do Props and Items get linked? Is it possible that a prop that has the only item it is linked to deleted may be used for another item later? In this case you would not want to delete that prop since it may be used later, otherwise you would need to recreate a prop you deleted.
OK, I understand that technically records are orphaned in a genuine master-detail relationship where the foreign key field in the detail table is set to null. That makes indeed more sense.
Maybe a bit of explanation on what I'm doing will make things more clear: We have a home-brew ORM (Object-Relational Mapping) code layer in Delphi which we created in 2000, closely bound to Interbase using IBX. You could compare this to NHibernate. We are currently rewriting that layer to support other databases, at the moment I already support IB/FB/Oracle/MSSQL/MySQL/DB2. The idea is to do a performance comparison with real-life data when that layer is reworked.
Objects in memory are multidimensional, a default mapping to the 2-dimensional database can be generated from within the classes and that can be overruled by specifying something else (xml text). A simple master-detail list in an object can therefore be stored as a one-to-many or many-to-many using a link table as in the example, based on the mapping. I'm just providing support to remove "orphaned" (my meaning ;)) records in the detail table when this would be required. By simply not using an after delete trigger on the link the detail records stay once created.
I also suggest that you adopt a uniform naming convention so itm_id is itm_id in all tables, etc... Most SQL Server developers will automatically understand that an id column in a table with a different name is a foreign key while not necessarily knowing what lnk_item is. Also, with SQL Server there is no real need to abbreviate so best practices would suggest limiting abbreviations.
Yeah, i know, sorry for that but this was just a quick and dirty example. We try make sure fieldnames they are self-explanatory, but in this case i generated the default DDL which does not append '_ID' to foreign key fieldnames.
I must admit, this is a pretty neat site, with a good forum, and fast response. I take my hat off for this.
Cheers,
Koen
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply