insert the records in Table 2 whenever the records delete in Table 1.

Question

insert the records in Table 2 whenever the records delete in Table 1.

adisql

SSCrazy

Points: 2893
More actions
October 1, 2018 at 7:46 am

#368910

Hi,
I would like to insert the records in Table 2 whenever the records delete in Table 1.
How to achieve this.
Please suggest.
Thank you !!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

adisql - Monday, October 1, 2018 7:46 AM
Hi,
I would like to insert the records in Table 2 whenever the records delete in Table 1.
How to achieve this.
Please suggest.
Thank you !!

Trigger.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

below86 SSChampion Points: 11532 More actions · Answer 2

If you are running a DELETE SQL statement you could use the OUTPUT statement to write the rows deleted to another table.
https://msdn.microsoft.com/en-us/library/ms177564.aspx

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

That'll work but, if you ever need to disable that particular functionality (especially temporarily), you'll need to do a code change rather than just disabling (or dropping) a trigger.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

below86 SSChampion Points: 11532 More actions · Answer 4

Jeff Moden - Monday, October 1, 2018 8:04 AM
That'll work but, if you ever need to disable that particular functionality (especially temporarily), you'll need to do a code change rather than just disabling (or dropping) a trigger.

So dropping and then later adding back a trigger would be easier than temporarily commenting out a few lines of code? I guess I would go with commenting out the code.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Jonathan AC Roberts SSCoach Points: 18356 More actions · Answer 5

below86 - Monday, October 1, 2018 8:28 AM
Jeff Moden - Monday, October 1, 2018 8:04 AM
That'll work but, if you ever need to disable that particular functionality (especially temporarily), you'll need to do a code change rather than just disabling (or dropping) a trigger.
So dropping and then later adding back a trigger would be easier than temporarily commenting out a few lines of code? I guess I would go with commenting out the code.

The other good thing about a trigger is that it will insert the rows into the table even if you do a manual delete of the data not going through your code.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

below86 - Monday, October 1, 2018 8:28 AM
Jeff Moden - Monday, October 1, 2018 8:04 AM
That'll work but, if you ever need to disable that particular functionality (especially temporarily), you'll need to do a code change rather than just disabling (or dropping) a trigger.
So dropping and then later adding back a trigger would be easier than temporarily commenting out a few lines of code? I guess I would go with commenting out the code.

Yes.

Ideally, you would NOT drop the trigger if it were for temporary reasons. You'd just disable it with a single line of code. Depending on how things are setup for changes to prod, "commenting out a few lines of code" may require running through a "dev cycle"/change control, etc whereas temporarily disabling a trigger may not. If it's a permanent rather than temporary change, then you'd just drop the trigger. Either way, no actual code changes would be required, which would be especially painful if there were many places such DELETE code existed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

Jonathan AC Roberts - Monday, October 1, 2018 8:38 AM
below86 - Monday, October 1, 2018 8:28 AM
Jeff Moden - Monday, October 1, 2018 8:04 AM
That'll work but, if you ever need to disable that particular functionality (especially temporarily), you'll need to do a code change rather than just disabling (or dropping) a trigger.
So dropping and then later adding back a trigger would be easier than temporarily commenting out a few lines of code? I guess I would go with commenting out the code.
The other good thing about a trigger is that it will insert the rows into the table even if you do a manual delete of the data not going through your code.

Exactly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

below86 SSChampion Points: 11532 More actions · Answer 8

Jeff Moden - Monday, October 1, 2018 9:02 AM
Exactly.

I can see your points. But as with many things in SQL I will say "It depends". If you have SQL that does a delete in more than one job(why would you? I don't know) and you only want to temporarily stop one of the deletes from writing to this output table but keep any others going. It may be difficult figuring out the timing with the trigger, to be sure you ignore the one delete but catch any others.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.