Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Automated Trigger To Require a WHERE Clause

By Rahul Kumar Ghosh, (first published: 2011/01/25)

The Problem

I was working on an ERP project for an electrical company. As a DBA my main task was to maintain database security and data consistency. A database developer was appointed on the client side to maintain the database. By mistake the developer used an update query in an important monetary transaction table and updated all the rows instead of updating just a group of rows. This generated an incorrect payment status report from the front end ASP.NET application.

Now as a DBA, my task was to scrutinize the table, determine the error, and finally fix it. I used a SELECT query in the transaction table and saw that the payment status column data has become 'Paid' for all rows. But this was not expected; the status should be set to 'Paid' for only a few rows. The developer used the update query without a WHERE clause.

I determined the problem and its cause, and now it's time to implement some preventive methods so that this type of error will not occur in the future. By the end of this article one will be able to stop updates and deletes that are unexpectedly made on a table without WHERE conditions.

I have two figures of the transaction table below named Fig 1 and Fig 2. The two important columns are 'MOP' and 'PayStatus'. The columns are important because they are dependent on each other. In FIG 1 notice the circled items. The dependencies are explained below:

  • In the MOP column where the value is 'Cash', the PayStatus column shows 'PAID' .
  • In the MOP column where the value is 'DD' or 'Cheque', the PayStatus column is 'Pending'.

The transaction table should look like this after an update query.

FIG 1.

The scenario changed when the database developer used an update query without a where clause. The transaction table after the developer's update is shown in FIG 2.

  • The PayStatus column shown in the red circle contains the wrong data. Is is wrong for the rows with an MOP column set to 'DD' / 'Cheque', the PayStatus should be 'Pending'.
  • The PayStatus column shown in the blue circle contains the correct data. For the MOP column rows with a value 'Cash', the PayStatus should be 'Paid'.

FIG 2.

The Solution

We have to make the use of a Wherecondition mandatory for all update and delete queries. If it is not included, an error message 'Cannot update all rows. Use WHERE CONDITION' or 'Cannot delete all rows. Use WHERE CONDITION' will be shown.

Code Description

I have written the stored procedure to generate a trigger for a table to stop the execution of update and delete statements without a WHERE condition. The explanation of the stored procedure is shown below along with instructions on how to use it.

The parameters used in this stored procedure are:

Store Procedure Parameter Declaration
Sl.No. Parameter Explanation Example
1 @tblname The name of the table on which trigger needs to be created. @tblname = 'TableName'
2 @Type The type of trigger to be generated like update trigger or delete trigger or update and delete triggers (both).

1) For delete @Type = 'delete',

2) For update @Type = 'update',

3) For update and delete (both) =@Type = 'both'

The variables used in this stored procedure are:

Store Procedure Variable Declaration
Sl.No. Parameter Explanation Example
1 @trgname To set up trigger name, table name is required and it's appended to the pre-fixed text '([dbo].[trg_])'depending on the type of trigger.

1) For delete trigger Set@trgname = [dbo].[trg_del_tblname],

2) For update trigger Set@trgname =[dbo].[trg_upd_tblname],

3) For update and delete trigger Set@trgname =[dbo].[trg_DelUpd_tblname]

2 @strTRGText To hold the trigger body text.
3 @errupd To hold the error message for update trigger. Set @errupd = 'Cannotupdate all rows. Use WHERE CONDITION'
4 @errdel To hold the error message for delete trigger. Set @errdel = 'Cannotdelete all rows. Use WHERE CONDITION'
5 @errboth To hold the error message for update and delete trigger (both). Set @errboth = 'Cannot update or delete all rows. Use WHERE CONDITION'
6 @severity It is the user-defined severity level associated with the message, note that the value of severity is fixed to 16 because severity levels from 0 to 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role and the WITH LOG option is required. Severity levels from 20 to 25 are considered fatal. Now it can be created by any user Set @severity = '16'
7 @state It is an integer and should be within 0 to 255. It is needed because if the same user-defined error is raised at multiple locations, using a unique state number for each location can help to find which section of code is raising the errors. Set @state = '1'

First the stored procedure will assign the trigger name then it will check whether that trigger exists in dbo.sysobjects or not. If yes, it will print the message 'Sorry!! [dbo].[trg_upd_aa] Already exists in the database...'. Otherwise it will create the trigger.

When the stored procedure when gets executed with all its needed parameters, an auto-generated trigger gets created. The only variable that has been declared in the auto-generated trigger code is @Count and it is set to @@ROWCOUNT, which returns the number of rows affected by the last statement.

The main code snippet which performs this action is this:

IF @Count >= (SELECT SUM(row_count)

FROM sys.dm_db_partition_stats
WHERE OBJECT_ID = OBJECT_ID('tablename')
)

This code snippet above compute the sum of the row_count column from the sys.dm_db_partition_stats table for those rows where the OBJECT_ID column is equals the tablename parameter. The selected value will be used by the auto-generated trigger to stop update and delete statements that have no WHERE conditions.

If the selected value exceeds or is equal to @@ROWCOUNT, then the RAISERROR statement will get fired and the update or delete statement without the WHERE condition is stopped.

When the auto-generated trigger gets created, it will print a message according to the @Type:

  • If the @Type = 'Update' then it will print 'Trigger done (update) Trigger [dbo].[trg_upd_tblname] Created Successfully'.
  • If @Type = 'delete' then it will print 'Trigger done (delete) Trigger [dbo].[trg_del_tblname] Created Successfully'.
  • If @Type = 'both' then it will print 'Trigger done (update & delete) Trigger [dbo].[trg_upd_tblname] created Successfully'.

The stored procedure code is in the resource section below. You can get all the trigger code when the stored procedure gets executed with the required parameters.

Conclusion

The accidental update and delete of data in a table can be stopped with this trigger created in the database. This will help you in ensuring data integrity, consistency, reliability and security.

Resources:

SP_Restriction.sql
Total article views: 16501 | Views in the last 30 days: 8
 
Related Articles
FORUM

Issues with Update DateTime column with GETDATE() using insert and delete triggers

Regarding Update DateTime column with GETDATE() using insert and delete triggers

FORUM

After Update trigger does not catch all updated columns

The after update trigger recognizes only the last column updated

FORUM

Update Trigger Loop?

Finding updates to columns without multiple triggers

FORUM

TRIGGERS AFTER UPDATE,DELETE

Is it possible to write a single trigger for UPDATE and DELETE and allow some Delete specific code ?...

FORUM

update trigger

update trigger with like statment

Tags
delete    
store procedure    
triggers    
update    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones