Post reply

Unable to delete records from table

  • February 26, 2025 at 10:31 am

    #4546386

    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}'

     

    Attachments:
    You must be logged in to view attached files.

  • February 26, 2025 at 10:43 am

    #4546389

    What does it say in the Messages tab after attempting the deletions?

  • February 26, 2025 at 10:57 am

    #4546392

    '{000C1B7A-164A-4243-9952-01BC82236EB0}' is not the same as '000C1B7A-164A-4243-9952-01BC82236EB0'

  • February 26, 2025 at 11:26 am

    #4546415

    After deleting it says rows affected

    Attachments:
    You must be logged in to view attached files.

  • February 26, 2025 at 11:27 am

    #4546417

    After deletion of files it says rows affected and when i search for the particular record it shows up the record.

    Attachments:
    You must be logged in to view attached files.

  • February 26, 2025 at 11:33 am

    #4546422

    Weird. Are there any triggers on this table?

  • February 26, 2025 at 11:45 am

    #4546423

    Yes thats right both are not the same

  • February 26, 2025 at 11:46 am

    #4546424

    Yes there is a trigger

    Attachments:
    You must be logged in to view attached files.

  • February 26, 2025 at 12:25 pm

    #4546440

    narayanamoorthy.a wrote:

    Yes there is a trigger

    Please right-click / Script as Create

    and post the code here.

  • February 26, 2025 at 12:48 pm

    #4546467

     

    /****** 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

    #4546468

    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

    #4546470

    So How can I overcome this. How can i delete the older data on journal table.

  • February 26, 2025 at 1:00 pm

    #4546471

    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

    #4546475

    Thank you Phil

  • February 26, 2025 at 4:11 pm

    #4546603

    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