ON DELETE CASCADE with Trigger AFTER DELETE

Question

ON DELETE CASCADE with Trigger AFTER DELETE

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

1

I have a problem when it comes to making a few tables for tables and I hope they can help me.
Let's imagine that I have a Grandfather table created in the following way:

    CREATE TABLE ABUELO{
       IDABUELO INT PRIMARY KEY
    }

And a Father

table

    CREATE TABLE PADRE{
    IDPADRE INT PRIMARY KEY
    IDABUELO INT CONSTRAINT FK_IDABUELO FOREIGN KEY IDABUELO REFERENCES ABUELO(IDABUELO) ON DELETE CASCADE
    }

The Father table is referenced to Grandpa's by IDABUELO .
Now, I also have a Son table declared in the following way.

   CREATE TABLE HIJO{
    IDHIJO INT PRIMARY KEY
    IDPADRE INT CONSTRAINT FK_IDPADRE  FOREIGN KEY IDPADRE REFERENCES  PADRE(IDPADRE ) ON DELETE CASCADE
    }

Which is this reference to Father by its IDPADRE code.
Besides these, I have a blog tables ABUELO_Log , PADRE_Log , and HIJO_Log that have the same attributes.
When I delete a row of Grandpa , being ON DELETE CASCADE, the parents that have the code referenced to that grandfather will be deleted. Once, all the children that have their code referenced to father are deleted.
The problem is to make an AFTER DELETE Trigger (it can not be INSTEAD OF) that saves the record of the deleted row in its respective logbook table.
When the Grandfather row is deleted, I can recover the deleted data from the temporary table DELETED accessible during the trigger, and transfer this data to ABUELO_Log .
But, where are the deleted data of Father and Son saved to recover them and save them in their respective bitacoras?
Are they stored in the same DELETED table, or are several DELETED tables created according to the deleted tables?
I know how to do this problem with an INSTEAD OF trigger, but the problem requires an AFTER trigger.

sql-server

asked by JosueFdz 29.05.2016 в 00:45

source

3 answers

does not show the value of the value in console.log Mysql Query Date (date)

score 1 · Answer 1

Create the tables as you indicate in your approach and then I made a trigger for each table, and both in FATHER and SON the trigger is triggered and the tables are supported.

These are the triggers that you create

CREATE TRIGGER [dbo].[TGR_ABUELO_DELETE] 
ON [dbo].[ABUELO]
AFTER DELETE AS
BEGIN
    INSERT INTO dbo.ABUELO_Log
    SELECT *
    FROM   deleted
    ;
END

CREATE TRIGGER [dbo].[TGR_PADRE_DELETE] 
ON [dbo].[PADRE]
AFTER DELETE AS
BEGIN
    INSERT INTO dbo.PADRE_Log
    SELECT *
    FROM   deleted
    ;
END

CREATE TRIGGER [dbo].[TGR_HIJO_DELETE] 
ON [dbo].[HIJO]
AFTER DELETE AS
BEGIN
    INSERT INTO dbo.HIJO_Log
    SELECT *
    FROM   deleted
    ;
END

I hope you find it useful

Greetings

score 1 · Answer 2

Instead of using TRIGGERS for the development of the log, consider the CDC configuration.

TRIGGERS chained when you have a large volume of data will greatly affect the performance of your system.

I leave a link for you to analyze, the configuration is simple:

link

score 0 · Answer 3

You can not capture the modified rows in a table from a trigger in another table, what you have to do is create an AFTER DELETE trigger for each table .

When the DELETE is executed in the Grandfather table it will trigger the corresponding DELETE in the Father and Son tables and, therefore, the AFTER DELETE triggers that you have put in these tables will be triggered, keeping the records deleted in the corresponding LOG .