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.
| Author |
Topic |
|
Audiosears
Starting Member
2 Posts |
 Posted - 2009-09-30 : 14:55:54
|
| This may be more appropriate for an Access forum - using a SQL Server trigger on a delete from one table to also delete records in a second table. The second table relates to the first via linked subforms. There may be a better way to construct the trigger to avoid the "key column information is insufficient..." error message in Access.Basically, I have a main form set up such that creating a record in Subform A will show in Subform B any records that Table B has in relation to Table A, referenced via an ID field. If you delete a record in Table A, the trigger properly fires and deletes referenced records in Table B (A->B is a 1 to many relationship). However, the "key column" error fires.Table A = DC_TEST_ITEMTable B = DC_TEST_ITEM_TOL--------ALTER TRIGGER [dbo].[On_Delete] ON [dbo].[DC_TEST_ITEM] AFTER DELETE AS BEGIN DELETE dbo.DC_TEST_ITEM_TOL FROM dbo.DC_TEST_ITEM_TOL a INNER JOIN Deleted d ON a.[Item ID] = d.ID END-----Thanks in advance for assistance!Regards,Audiosears IT |
|
|
Audiosears
Starting Member
2 Posts |
Posted - 2009-09-30 : 15:40:11
|
| It looks like I actually solved this after a little more searching..This error is caused specifically by not explicitly setting nocount to On in the trigger, so the following was done:--------ALTER TRIGGER [dbo].[On_Delete] ON [dbo].[DC_TEST_ITEM] AFTER DELETE AS BEGIN SET NOCOUNT ON; DELETE dbo.DC_TEST_ITEM_TOL FROM dbo.DC_TEST_ITEM_TOL a INNER JOIN Deleted d ON a.[Item ID] = d.ID END------This eliminated the error messages - the transactions were being carried out in the tables before but Access was halting on the error, preventing further vbcode processing.Regards,Audiosears IT |
 |
|
|
|
|
|
Triggered Delete