Delete Statement

The DELETE statement is used to delete rows in a database table. We can create a visitor method to grab the DeleteStatement. In this chapter, we are going to write code to check if a DELETE statement contains a WHERE clause. However, if the DELETE statement is a temporary table or table variable, we won’t flag it as an error.

Another consideration to check for is the combination of TOP x with an ORDER BY (which could basically randomly determine which rows to delete). The following code snippet can create a visitor method to grab DELETE statements.

The node parameter will be a DeleteStatement type, and the key property will be the delete specification object. If a clause is not found in the statement, the property for that clause will be null.

Listing 42 shows a sample stored procedure that deletes data.

Code Listing 42: Delete statements

Target table

Code Listing 43: Check target name if missing WHERE clause

WHERE clause

The primary property of the WHERE clause is the search condition object, which returns the first and second expressions. For a simple WHERE (one condition), the object type is a Boolean comparison expression. In the WHERE filter (such as affiliation = ‘?’), the search condition object will contain a first expression (in this case, a column reference expression object) and a second expression (a string literal). The comparison type (equals) shows how the two expressions are compared. Table 26 shows the mapping between the code and the object.

Table 26: Example WHERE parsing

The object types will vary, such as column reference or string literal. The comparison type will be an enumeration, such as equals, greater than, or less than.

Example

An example DELETE statement is shown in Figure 14.

Figure 14: Example DELETE statement

Summary

The DELETE statement has a few properties with the content of the DELETE command. You can use this visitor method to find the table having rows deleted, and hopefully, a WHERE expression, so the code doesn’t delete the entire table.