MSSQL server audit

MSSQL Server Auditing on AWS RDS Enable MSSQL Server Auditing on AWS RDS To enable MSSQL server auditing on AWS RDS, please follow these steps: Login to AWS console and go to RDS. Create an option group under RDS > Option groups Give name as 'SqlServerAudit' Provide description as 'Option group for SQL Server audit' Choose the engine as same as the one used while creating the database in RDS. Choose the latest engine version. Save. Go to RDS > Option groups and select the radio button on the option group you just created Click on Add Option. Select SQLSERVER_AUDIT under “Option name” Choose the S3 bucket name where you want to keep the audit files once they grow more than the specified limit. Choose the appropriate IAM role with write access to the S3 bucket. Scheduling > Immediately and then click on “Add Option“....

Delete rows from multiple tables in one query

Hello Reader,

In this blog post we will learn how to cascade the delete operation to child table. In a parent-child relationship there are situations when we don't want to leave the Child orphan. Meaning if the parent row is getting deleted we want to delete the associated childs also. The classic way to achieve this situation is to delete all the child rows first and then delete the parent row. The drawback of this approach is that we need to fire 2 queries and both of them should be in a same transaction else it would leave stale data in DB. For eg. if children were deleted successfully but parent was not deleted successfully, now in DB we still have this parent but no children.

If we want to delete this relation in a single query then there are then there are two ways to achieve it:

1) FOREIGN KEYS with ON DELETE CASCADE
2) Using JOIN in DELETE query


Below example is for mysql:

1) FOREIGN KEYS with ON DELETE CASCADE: This options allows creating FOREIGN KEYS in such a way that DELETE operations on parent will automatically cascade through child. See the below sql snippet:

CREATE TABLE PARENT (
    id int unsigned not null primary key,
    name VARCHAR(255) default null
);

CREATE TABLE CHILDREN (
    children_id int unsigned not null primary key,
    parent_id int unsigned not null,
    FOREIGN KEY (category_id) REFERENCES PARENT (id) ON DELETE CASCADE
);

So a query like DELETE FROM PARENT WHERE ID = 4 will also delete the records from the CHILDREN table.


2) If ON DELETE CASCADE are not setup on foreign keys then one can use JOIN queries. You can create a JOIN query where you join the related tables on Foreign keys and primary keys. By executing a DELETE with JOIN query, rows from all the related tables with a foreign key relation will be deleted. For eg:


DELETE parent, children FROM PARENT parent
LEFT JOIN CHILDREN children ON parent.ID = children.PARENT_ID
WHERE PARENT.ID = 4;

Above query will delete the rows from PARENT and CHILDREN where the PARENT.ID is 4.
Hope it helps.

Comments

  1. Nice post!

    We usually don't find much information on this topic over internet.

    I am thankful to you since it resolved my problem for which I was stuck for the days!

    Keep up the good work!

    ReplyDelete

Post a Comment

Popular posts from this blog

Unmarshall SOAP Message (XML) to Java Object using JAXB

Circuit breaker implementation in spring boot2

Hibernate inserts duplicate child on cascade