Unable to delete records from table
- Ant-Green wrote:
That trigger was placed there for a reason.
In financing terms once something is on a journal / ledger, it should ALWAYS remain on that journal / ledger.
If there was an error with that row, you need to do a NEW action which will counteract the bad action.
S0 if it was to pay out $500 then you should do a reverse payment credit of $500.
Before you do anything with deleting journal entries, make sure you know what you are doing and the ramifications this can have, especially in the financials of a company business.
Seek approvals and have backups ready to go before you do anything so the paper trail leads back to someone other than yourself as a cover your ass protection.
Spot on and it's too bad you can't give someone a million LIKEs on this and other forums.
It's amazing to me that people will try to disable systems to make changes that the system was designed to prevent in the first place.
--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) -
Heh... and people wonder why such a thing won't stand up in court. Ant-Green is spot on!
--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) -
Yes that right Jeff. When Phil replied back with solution, I was well aware of it. These are very old records which business wants to erase before 2006 year. I have taken a complete backup and have business is well aware of this. Thank you Jeff and Phil.
-
Before you delete that data, as a minimum I would BCP it out and save it securely somewhere. Long term you might want to look at partitioning the table so old partitions can simply be swapped out without the overhead of deleting.
- Jeff Moden wrote:
Heh... and people wonder why such a thing won't stand up in court. Ant-Green is spot on!
SOX audit gunna eat them alive.
-
If this is a sensitive system that your organisation may rely on as evidence in a court case, then be aware that deleting or changing any data in the audit trail may invalidate the use of that system in evidence. It may also be illegal.
As already said, get written approval to proceed, and to CYOA print that approval and take it home. If the polis come to call, you can then show you tried to keep the right side of the law.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Yes well noted and conveyed the same to business and application owners. They are well are of the repercussions and will take a call on this. Thank you all for your suggestions and insights.
- narayanamoorthy.a wrote:
Yes well noted and conveyed the same to business and application owners. They are well are of the repercussions and will take a call on this. Thank you all for your suggestions and insights.
With the idea of trying to protect you, make sure you have this documented in a ticket somewhere and the send yourself a copy. And, this should not be done manually... it should be done by a script that people have signed off on in order to pass a future audit or to defend your actions in the future. Just sayin'...
--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 wrote:narayanamoorthy.a wrote:
Yes well noted and conveyed the same to business and application owners. They are well are of the repercussions and will take a call on this. Thank you all for your suggestions and insights.
With the idea of trying to protect you, make sure you have this documented in a ticket somewhere and the send yourself a copy. And, this should not be done manually... it should be done by a script that people have signed off on in order to pass a future audit or to defend your actions in the future. Just sayin'...
Slightly OT, but that's true of any change in a financial system, especially if covered by SOX. We spent a lot of time getting deployment pipelines set up with built in approvals and such. When audited, we can point the auditors to the deployment history, which references a ticket and captured the approvals.
-
One thing you can do is change the trigger from INSTEAD OF DELETE to FOR DELETE.
An INSTEAD OF trigger executes the code in place of the requested operation, and it is the trigger's responsibility to complete the action (such as the delete you want).
A regular FOR DELETE will fire after the requested operation is completed, giving the trigger an opportunity to log the action as you are doing, but the operation will have been completed first.
(Alternatively, you could add the final delete statement into your INSTEAD OF trigger).
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply