|
|
You're in the
sql server programming
group:Accessing parent record from child table's DELETE trigger
sql server programming:
Hello all,
We have a parent table (say ABC) and its child table(say XYZ). The sturctures are simple as shown below. ABC 1. abc_id 2. abc_name XYZ 1. xyz_id 2. xyz_name 3. abc_id_fk (foreign key relation with abc_id of abc table) So there is a foreign key in XYZ table. ON DELETE CASCADE is set for the parent. So when I delete a record in ABC table, related records from XYZ also is removed. This is fine. In the DELETE trigger of XYZ (child), we are trying to access the parent record for the record currently being deleted (this is to get the NAME_ABC for kind of logging). The full trigger is as follows: CREATE TRIGGER dbo.trgTest ON dbo.xyz AFTER DELETE AS BEGIN SET NOCOUNT ON; Declare @idno int Declare @fkid int Declare @sAddinfo varchar(50) Select @idno=xyz_id ,@fkid = abc_id_fk from deleted Select @sAddinfo=abc_name from abc where abc_id=@fkid INSERT INTO [dbo].DeleteLog VALUES ( 'xyz', @idno, GETDATE(), USER_NAME(), @sAddInfo ); END GO When I delete a record directly from the child XYZ table, this works fine. But if I delete a record from parent ABC thereby causing a cascade, this does not work. The " Select @sAddinfo=abc_name from abc where abc_id=@fkid " does not return anything and seems that this query fails. And subsequently, the next step of INSERT fails since there is a NULL value constraint in the DeleteLog table. The same problem occurs if we try to do the same work for a CASCADE UPDATE too. Anybody has an idea why such a problem occurs and how we should do this correctly? Any suggestion is appreciated. Regards, Sacher
Hi, Sacher
I encountered the same problem a couple of years ago, when I started to use triggers heavily for implementing business logic. It seems that's the way things work with cascade deletes (i.e. when the triggers on the referencing tables are fired, the rows in the referenced table are already deleted). The only solution that I've found is to use an INSTEAD OF DELETE trigger instead of the ON DELETE CASCADE option. The trigger would look like this: CREATE TRIGGER ABC_xD_CascadeDelete ON ABC INSTEAD OF DELETE AS IF @@ROWCOUNT>0 BEGIN SET NOCOUNT ON DELETE XYZ WHERE abc_id_fk IN (SELECT abc_id FROM deleted) DELETE ABC WHERE abc_id IN (SELECT abc_id FROM deleted) END I have not studied the problem in case of cascade updates. I guess that an INSTEAD OF trigger would work, but if you need help, post a scenario for that case and I'll think about that too. Razvan [quoted text, click to view] Sacher wrote:
> Hello all, > > We have a parent table (say ABC) and its child table(say XYZ). The > sturctures are simple as shown below. > > ABC > 1. abc_id > 2. abc_name > > XYZ > 1. xyz_id > 2. xyz_name > 3. abc_id_fk (foreign key relation with abc_id of abc table) > > So there is a foreign key in XYZ table. ON DELETE CASCADE is set for > the parent. So when I delete a record in ABC table, related records > from XYZ also is removed. This is fine. > > In the DELETE trigger of XYZ (child), we are trying to access the > parent record for the record currently being deleted (this is to get > the NAME_ABC for kind of logging). The full trigger is as follows: > > CREATE TRIGGER dbo.trgTest > ON dbo.xyz > AFTER DELETE > AS > BEGIN > SET NOCOUNT ON; > Declare @idno int > Declare @fkid int > Declare @sAddinfo varchar(50) > > Select @idno=xyz_id ,@fkid = abc_id_fk from deleted > Select @sAddinfo=abc_name from abc where abc_id=@fkid > > INSERT INTO [dbo].DeleteLog > VALUES > ( > 'xyz', > @idno, > GETDATE(), > USER_NAME(), > @sAddInfo > ); > > END > GO > When I delete a record directly from the child XYZ table, this works > fine. But if I delete a record from parent ABC thereby causing a > cascade, this does not work. The " Select @sAddinfo=abc_name from abc > where abc_id=@fkid " does not return anything and seems that this query > fails. And subsequently, the next step of INSERT fails since there is a > NULL value constraint in the DeleteLog table. > The same problem occurs if we try to do the same work for a CASCADE > UPDATE too. > > Anybody has an idea why such a problem occurs and how we should do this > correctly? > > Any suggestion is appreciated. > > Regards, > Sacher
Hi Razvan, Hi Tom,
Thanks for the response. At first we were also thinking that the records in the parent table is deleted first and that is why the problem occures. In our situation, the trigger fails because the INSERT statement fails. So at the end nothing is deleted from either the parent and child table. Is it due to an automatic rollback? OR is there a chance that the server is locking the parent record? I am aware that there are special locks which prevents me from querying the records. Since the server knows that this particular record is marked for deletion, can it be that this record is read-locked? This is just a thought; I am not sure about this. And thanks for Tom for giving the hint about the real behaviour of triggers. To my horror, I just dicovered it yesterday. I am not a person normally working with SQL DB programming. So any further help is greatly valued. Thanks & regards, Sacher
[quoted text, click to view]
Sacher wrote: > [...] In our situation, the trigger fails because the > INSERT statement fails. So at the end nothing is deleted from either > the parent and child table. Is it due to an automatic rollback? Yes, if an error occurs while executing a trigger, a ROLLBACK is automatically issued. This also applies if an error is encountered in a stored procedure that is called from a trigger. Note that this doesn't apply to a RAISERROR (in this case you need to issue the ROLLBACK). For more information about error handling, see the following article by Erland Sommarskog, SQL Server MVP: http://www.sommarskog.se/error-handling-I.html [quoted text, click to view] > [...] Since the server knows that this particular record is > marked for deletion [...] The row is not "marked for deletion"; it is actually deleted, but this occurs in a transaction that is not committed yet. The whole locking stuff applies in order to prevent another connection seeing uncommitted actions, but the current connection is seeing that the delete is already done. Razvan
This is occurring because when you have both a foreign key with CASCADE
action and triggers, the actions occur in the following order. First the update/delete on the original table and all the casading updates or deletes are done, second, the trigger is called on the original table (even if no rows were actually deleted or updated), and finally, the triggers are called on the cascaded tables, but only if one or more rows were deleted or updated in that table. So the row in ABC is gone by the time you get to the XYZ trigger (for that matter, so are the rows in XYZ, but in that case you have the deleted table available to find the original values). AFAIK, there is no way to change this. If you need this value for auditing and cannot get it in any other way than the row in the ABC table (maybe it is enough to have the ABC trigger audit it?), probably your best bet is to get rid of the CASCADE option on the foreign keys and do the deletes/updates through stored procedures that do all the deletes, updates, and auditing in the correct order so you save the data you need and the foreign key constraints are never violated. Incidently, there is a common flaw in your trigger logic. Triggers are called once per statement, not once per row. If a statement inserts/updates/deletes multiple rows, then the inserted and/or deleted tables will have multiple rows in them. If, for example, you deleted a row from ABC, and that cascaded to delete 5 rows from XYZ, then the XYZ trigger would be called only one time with 5 rows in the deleted table. But your code would only audit one of them. It is good practice to always write triggers so that they work if the inserted/deleted tables have multiple rows. Tom [quoted text, click to view] "Sacher" <sacherdos@rediffmail.com> wrote in message news:1159331541.881822.263040@m73g2000cwd.googlegroups.com... > Hello all, > > We have a parent table (say ABC) and its child table(say XYZ). The > sturctures are simple as shown below. > > ABC > 1. abc_id > 2. abc_name > > XYZ > 1. xyz_id > 2. xyz_name > 3. abc_id_fk (foreign key relation with abc_id of abc table) > > So there is a foreign key in XYZ table. ON DELETE CASCADE is set for > the parent. So when I delete a record in ABC table, related records > from XYZ also is removed. This is fine. > > In the DELETE trigger of XYZ (child), we are trying to access the > parent record for the record currently being deleted (this is to get > the NAME_ABC for kind of logging). The full trigger is as follows: > > CREATE TRIGGER dbo.trgTest > ON dbo.xyz > AFTER DELETE > AS > BEGIN > SET NOCOUNT ON; > Declare @idno int > Declare @fkid int > Declare @sAddinfo varchar(50) > > Select @idno=xyz_id ,@fkid = abc_id_fk from deleted > Select @sAddinfo=abc_name from abc where abc_id=@fkid > > INSERT INTO [dbo].DeleteLog > VALUES > ( > 'xyz', > @idno, > GETDATE(), > USER_NAME(), > @sAddInfo > ); > > END > GO > When I delete a record directly from the child XYZ table, this works > fine. But if I delete a record from parent ABC thereby causing a > cascade, this does not work. The " Select @sAddinfo=abc_name from abc > where abc_id=@fkid " does not return anything and seems that this query > fails. And subsequently, the next step of INSERT fails since there is a > NULL value constraint in the DeleteLog table. > The same problem occurs if we try to do the same work for a CASCADE > UPDATE too. > > Anybody has an idea why such a problem occurs and how we should do this > correctly? > > Any suggestion is appreciated. > > Regards, > Sacher >
Hi Razvan,
[quoted text, click to view] > The row is not "marked for deletion"; it is actually deleted, but this > occurs in a transaction that is not committed yet. The whole locking > stuff applies in order to prevent another connection seeing uncommitted > actions, but the current connection is seeing that the delete is > already done. If the parent record is actually deleted before the child is deleted, the parent's delete trigger should happen before the child's trigger is executed. But it is not so. Why does it happen so? Thanks for your help. Regards, Sacher
As far as I have understood, the sequence is the following:
1. the "DELETE parent WHERE condition" statement is started 2. the rows of the parent table that are affected by the statement are being searched for (and stored in an in-memory structure, maybe the deleted pseudo-table) 3. the affected rows are deleted from the parent table 4. the foreign keys referencing the parent table are checked a) for the ones with cascading option, the same process is done, recursively: 4.1 something like "DELETE child WHERE id IN (SELECT id FROM deleted)" is being executed 4.2 the rows of the child table that are affected by the statement are being searched for (and stored in another in-memory structure, maybe another deleted pseudo-table) 4.3 the affected rows are deleted from the child table 4.4 the foreign keys referencing the child table are checked (if any) 4.5 the delete triggers on the child table are fired b) for the foreign keys that are non-cascading, if there are rows in a child table that reference the deleted rows from the parent table, an error is raised and the transaction is rolled-back 5. the delete triggers on the parent table are fired I have seen no official documentation that states that this is the way things work, but that's the way I understand them. Razvan [quoted text, click to view] Sacher wrote:
> Hi Razvan, > > > The row is not "marked for deletion"; it is actually deleted, but this > > occurs in a transaction that is not committed yet. The whole locking > > stuff applies in order to prevent another connection seeing uncommitted > > actions, but the current connection is seeing that the delete is > > already done. > > If the parent record is actually deleted before the child is deleted, > the parent's delete trigger should happen before the child's trigger is > executed. But it is not so. Why does it happen so? > > Thanks for your help. > > Regards, > Sacher
Hi Razvan,
Thanks very much for your time and effort for clarifying my doubt. Regards, Sacher
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |