logo
down
shadow

Check if delete was caused by CASCADE DELETE


Check if delete was caused by CASCADE DELETE

By : lin.zhang
Date : November 18 2020, 11:13 AM
around this issue I ended up going with the Trigger option as it looks to be the most elegant solution and doesn't look too bad after I actually scripted it out. Here's the script if it helps someone else or anyone wants to comment on it:
code :
/* 
Create the MAILBOXES CRPREDEL1 Trigger 
Creates the Trigger which Deletes all Folders ( and by Cascade all Files ) when a Mailbox gets Deleted
*/
CREATE TRIGGER [dbo].[CRPREDEL1_MBX]
ON [dbo].[MAILBOXES]
INSTEAD OF DELETE
NOT FOR REPLICATION
AS
    DECLARE @SqlIdentity        int

    IF @@ROWCOUNT > 0
    BEGIN
        SET NOCOUNT ON
        DECLARE c_primary CURSOR LOCAL FOR SELECT d.MBX_SQLIDENTITY
                                           FROM deleted d
        BEGIN
            OPEN c_primary
            FETCH NEXT FROM c_primary INTO @SqlIdentity
            WHILE @@FETCH_STATUS = 0
            BEGIN
                -- Disable CRPOSDEL1_MFO so we can delete System Folders
                ALTER TABLE [MAILBOX_FOLDERS] DISABLE TRIGGER [CRPOSDEL1_MFO]

                -- Delete the Mailbox Folders
                DELETE FROM [MAILBOX_FOLDERS] WHERE [MFO_MAILBOX] = @SqlIdentity

                -- Enable CRPOSDEL1_MFO so System Folders can't be deleted anymore
                ALTER TABLE [MAILBOX_FOLDERS] ENABLE TRIGGER [CRPOSDEL1_MFO]

                -- Delete the Actual Mailbox now
                DELETE FROM [MAILBOXES] WHERE [MBX_SQLIDENTITY] = @SqlIdentity  

                FETCH NEXT FROM c_primary INTO @SqlIdentity
            END
            CLOSE c_primary
        END
        DEALLOCATE c_primary
    END
GO


Share : facebook icon twitter icon
Cascade delete performance drop on bigger datasets, can this be caused by lack of indexing?

Cascade delete performance drop on bigger datasets, can this be caused by lack of indexing?


By : user2418512
Date : March 29 2020, 07:55 AM
hop of those help?
I'm assuming that it would need to make a lot of joins for the cascade to find all the related records in other tables, which causes it to slow down on bigger datasets.
Correct approach to delete the user interface data after database on cascade delete

Correct approach to delete the user interface data after database on cascade delete


By : Sahr Lansana
Date : March 29 2020, 07:55 AM
it helps some times Definitely number 1. There's no benefit in asking for an empty dataset. That would require a request, a database query and then a response. The first method simply clears the data that has already been confirmed to be deleted.
Is it possible to tell in a delete trigger whether it was caused by a cascade delete FK?

Is it possible to tell in a delete trigger whether it was caused by a cascade delete FK?


By : Sunny Ahooja
Date : March 29 2020, 07:55 AM
wish help you to fix your issue Cascading deletes don't check permissions. So just don't grant users the right to delete from the table:
Cascade delete not working if Cascade.persist and Cascade.remove used together in parent Entity in Hibernet

Cascade delete not working if Cascade.persist and Cascade.remove used together in parent Entity in Hibernet


By : Cloud
Date : March 29 2020, 07:55 AM
I hope this helps you . This article seems to describe your problem exactly. If what the author says is still true (the article's from 2010), the problem is that there is a mismatch between what actions JPA and Hibernate expect when persisting. If you're using an older Hibernate implementation, I'd first try upgrading to a newer version as I've never seen the problem in recent versions. If you can't upgrade (for example, you're constrained to using the version of Hibernate provided by your app server) or if upgrading doesn't work, replacing the JPA cascade annotations with the Hibernate cascade annotations may fix your problem. Using Hibernate annotations isn't a great solution because now your code isn't implementation-agnostic, but if it works it works.
What version of Hibernate/JPA/app server are you using?
Oracle: delete parent table when child is deleted(Bidirectional Cascade Delete)

Oracle: delete parent table when child is deleted(Bidirectional Cascade Delete)


By : user5247531
Date : March 29 2020, 07:55 AM
will be helpful for those in need This kind of cascade usually doesn't make sense. Parent have to exist when child is created. So its common sense that parent shouldn't be delete only because there is no childs.
If you use only 1 to 1 relationship, you just need to change your program logic to delete only parent records, when you need to delete child.
Related Posts Related Posts :
  • Select records based on two tables where one column in one table starts with the records from another tables column usin
  • How to write select using two ids combination in Oracle 11g
  • Frequently use of 'UPDATE STATISTICS WITH FULLSCAN' is normal?
  • need query for this simple pulling data sql server
  • Sql query by using decode to show grades of employee
  • Not a single function error in Oracle
  • Oracle SQL optimizer's behavior when dealing with ORs and row-independent predicates (such as functions returning same v
  • Combining output of two or more select statement
  • Must Declare Scalar Variable Exception
  • Postgres Calculate Difference Using Window Functions
  • SQL Server : unsure how to retrieve selected records
  • SQL unique field: concurrency bugs?
  • Query to get the next identity?
  • SQL Server Adding summing values based on the month
  • Join on month if available else join on latest month
  • How to solve error: ORA-01406 fetched column value was truncated?
  • Selecting Field Multiple Times using OR
  • Which SQL datatype can be used to store mobile numbers in numeric forms, without including characters like brackets and
  • Oracle SQL - "Not a group by expression" after adding CASE statements
  • Runnning total of unique string values in column over days
  • VB.Net to AccessDB by SQL - INSERT INTO with parameters error
  • SQL/Excel/VBA - UPDATE query: 'Syntax Error (missing operator) in query expression'
  • Code to combine two sql queries
  • Setting SQL Date field value based off Week Number and Day Number Field
  • SQL query to find matching records but with differences in case sensitivity?
  • How to sort record inside views in SQL Server 2008
  • How to link the output of teradata sql query to an excel sheet?
  • Matching sets in SQL when cross-referencing
  • error even after following Microsoft format in SQL
  • Increase price by percentage, results rounded
  • Can we Use "Case" in a ColdFusion Query-of-Query
  • displaying 'Active' for a row which has 'A' as value
  • Using pattern search in SQL Server Query
  • SQL - how to group transactions for a certain date pattern?
  • How to Index SQL with multiple AND conditions nested inside OR
  • SQL rank with priority
  • Does BigQuery support SubQueries?
  • Any ideas for persisting H2 Database In-Memory mode transaction?
  • How to improve SQL query performance (correlated subqueries)?
  • Filter repeated columns in Oracle
  • SQL Server join on whichever column has value populated
  • use sql stored procedure to insert data which is returned from a query stored in a table
  • Query not working1
  • SQL - using an alias in a where clause in a subquery
  • How to search for a whole word with special char using oracle contains?
  • Production Perfomance of using SELECT * statements?
  • String concatenation not working as expected in SELECT statement
  • How to detect which stored procedures UPDATE or INSERT into a certain TABLE?
  • Select statement inside CASE - SQL
  • .Net Float error 4 - float(4) = 4.44089209850063E-16
  • How to show as column to recursive rows in a table With SQL Server
  • SQL Server error: Column name or number of supplied values does not match table definition
  • How to find a table name which's column name consist cl_
  • Cannot access existing table from WCF Code
  • SQL CREATE VIEW Error
  • Many Statement in the same transaction PLSQL
  • Tables are not joining properly in sql server 2008
  • DB2 how to sum two column from two different table
  • SQL msAccess: list unfinished courses of a student
  • SQL Server delete multiple rows
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org