CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
IMAbev
IMAbev

Reputation: 188

SQL Server: Require multiple conditions before delete

When deleting rows in a certain table, I would like to only allow the delete if at least 2 specific columns are referenced.

For example:

Delete From TransTable
/* delete fails - need where condition*/

Delete From Transtable
WHERE TransID = @TransID AND UserID = @UserID
/* rows deleted */

My reason for doing this is to add a second layer of security to a delete process. Is this possible?

Upvotes: 1

Views: 938

Answers (1)

gbn
gbn

Reputation: 432230

If either @TransID or @UserID are NULL this will delete zero rows anway. All comparisons against null evaluate to false (unknown strictly, for the inevitable pedants, but practically it is false here).

However, for more complex logic, such as throwing an error, use a stored procedure with 2 mandatory parameters and some logic. (Edit: I'd do this)

There is no way to enforce 2 conditions unless your code restricts this as above. No-one should be able to DELETE anyway directly with SQL tools or such, or bypassing your checks.

Upvotes: 3

Related Questions