February 26, 2025 at 10:31 am
Hi,
Im unable to delete records from a table.Im a little surprised as have not witnessed this. The SQL server runs on MSSQL 2019 RTM.
I use the below queries.
select top (5) * from Journal where createddatetime<'2006-01-01' -- To figure out the records below year 2006
Fetched the 5 top queries
Delete from Journal where RecID='{00053A3E-0859-416C-B700-7BD92FDA28F0}'
Delete from Journal where RecID='{000582F5-8D51-4FD0-98AA-B087E1F6A10A}'
Delete from Journal where RecID='{000AEE91-9C7E-4919-98CF-F637AB4B096C}'
Delete from Journal where RecID='{000B9334-6ED0-4CDE-9E57-E915A2AE952E}'
Delete from Journal where RecID='{000C1B7A-164A-4243-9952-01BC82236EB0}'
After firing the above delete queries when I see the records it still exists.
select * from journal where RECID= '{000582F5-8D51-4FD0-98AA-B087E1F6A10A}'
February 26, 2025 at 10:43 am
What does it say in the Messages tab after attempting the deletions?
February 26, 2025 at 10:57 am
'{000C1B7A-164A-4243-9952-01BC82236EB0}' is not the same as '000C1B7A-164A-4243-9952-01BC82236EB0'
February 26, 2025 at 11:33 am
Weird. Are there any triggers on this table?
February 26, 2025 at 11:45 am
Yes thats right both are not the same
February 26, 2025 at 12:48 pm
/****** Object: Trigger [dbo].[JournalInsteadOfDelete] Script Date: 2/26/2025 7:46:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create TRIGGER [dbo].[JournalInsteadOfDelete]
ON [dbo].[Journal]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON;
Insert into JournalDeleteAudit (journalRecId,journalTypeName,executedOn,parentRecId,userName,c.client_net_address)
select d.RecID,d.JournalTypeName,GETDATE(),d.ParentRecID,CURRENT_USER,c.client_net_address
from deleted d
cross join sys.dm_exec_connections c
where c.session_id = @@SPID
END;
GO
ALTER TABLE [dbo].[Journal] ENABLE TRIGGER [JournalInsteadOfDelete]
GO
February 26, 2025 at 12:52 pm
OK, that explains it. The deletes are being blocked and instead the delete attempts are being logged in table JournalDeleteAudit.
February 26, 2025 at 12:57 pm
So How can I overcome this. How can i delete the older data on journal table.
February 26, 2025 at 1:00 pm
If you are really sure that it's not going to cause issues, you can do this.
Disable the trigger, perform the deletion, then re-enable the trigger.
https://www.mssqltips.com/tutorial/sql-server-disable-and-enable-triggers/
February 26, 2025 at 1:05 pm
Thank you Phil
February 26, 2025 at 4:11 pm
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.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy