[T-SQL] Delete trigger. [modified]
-
Hello everybody. I'm updating an application which makes use of a trigger to maintain relationships between some tables in the database. I was unable to figure out how this could work with a simple constraint, so I thought I would go for a good olde' trigger. :-) Here's the deal: I have two tables: A and B, which both depends on information in table C. Now table C contains some binary data, and some information on how this binary data relates between rows in table A and B. If a row in table A is deleted, all references to that row in table C must also be deleted. The same thing goes for table B. Since entities in table A and B can have the same primary key, the table C use the primary key and a char to determine whether the primary key belongs to table A or B. Therefore I thought a constraint would be out of the question. The trigger problem: The problem is the "DELETED" logical table. As one might have guessed, it only works when there is one row in the DELETED table. The system allows the users to delete multiple rows, so I will have to construct the trigger to maintain relationships for all the rows in the DELETED table. I would most definitely like to avoid using a CURSOR on the DELETED table. How do I do that? :) (Should anyone be able to see how I could construct a constraint for the job, I would also like to know how to do that) Thanks for any help you can provide! :-)
modified on Monday, March 10, 2008 4:58 AM
-
Hello everybody. I'm updating an application which makes use of a trigger to maintain relationships between some tables in the database. I was unable to figure out how this could work with a simple constraint, so I thought I would go for a good olde' trigger. :-) Here's the deal: I have two tables: A and B, which both depends on information in table C. Now table C contains some binary data, and some information on how this binary data relates between rows in table A and B. If a row in table A is deleted, all references to that row in table C must also be deleted. The same thing goes for table B. Since entities in table A and B can have the same primary key, the table C use the primary key and a char to determine whether the primary key belongs to table A or B. Therefore I thought a constraint would be out of the question. The trigger problem: The problem is the "DELETED" logical table. As one might have guessed, it only works when there is one row in the DELETED table. The system allows the users to delete multiple rows, so I will have to construct the trigger to maintain relationships for all the rows in the DELETED table. I would most definitely like to avoid using a CURSOR on the DELETED table. How do I do that? :) (Should anyone be able to see how I could construct a constraint for the job, I would also like to know how to do that) Thanks for any help you can provide! :-)
modified on Monday, March 10, 2008 4:58 AM
If I understand you just need to delete all rows in table C related to the rows being deleted in A or B. C uses a sort of composite key - Primary + TableLetter to reference either table. So why can't your trigger do something like: DELETE FROM C WHERE EXISTS (SELECT * FROM DELETED WHERE C.ID = DELETED.ID) AND C.Letter = 'A' or DELETE C FROM DELETED INNER JOIN C ON C.ID = DELETED.ID WHERE C.Letter = 'A'
-
If I understand you just need to delete all rows in table C related to the rows being deleted in A or B. C uses a sort of composite key - Primary + TableLetter to reference either table. So why can't your trigger do something like: DELETE FROM C WHERE EXISTS (SELECT * FROM DELETED WHERE C.ID = DELETED.ID) AND C.Letter = 'A' or DELETE C FROM DELETED INNER JOIN C ON C.ID = DELETED.ID WHERE C.Letter = 'A'