Deleting multiple records based on rule conditions

Deleting multiple records based on rule conditions is similar to reading records without using a primary key. The entities used in the rule must be extended to the database. The rule then loads all the records from the database tables into memory and processes them. All records that satisfy the rule conditions are deleted through the rule action, which uses the .remove operator.

Let’s assume that all HEAVY containers must be replaced by next generation containers that meet new regulatory requirements. In this case, you want to remove all the current HEAVY containers from the database.

Create a Rulesheet named DeleteMultiple.ers. Model a rule as shown here:

Switch to Advanced View by selecting Rulesheet > Advanced View. In the Scope pane, right-click Cargo and select Extend to Database.

Save the Rulesheet.

The two HEAVY containers in the Cargo table—625B and 625D—will get deleted when we run the Ruletest without supplying primary key values:

Create a new Ruletest named DeleteMultiple.ert, configure the Database Access setting to Read/Update, and run the test (you don’t need to provide any input).

You get this output:

The Cargo record shown in the Output (625A) is the remaining record in the dbo.Cargo table.

Let’s verify this in the Transportation database. In SQL Server Management Studio, right-click dbo.Cargo and select Select Top 1000 Rows. You should be able to see the following result:

Only one record remains, indicating that the rule deleted all records that represented HEAVY containers.

Congratulations! You have now modeled rules that use EDC to read, write, and delete database records.