If you delete a batch of records in a data table without a trigger, you can use a delete statement to delete all the records that meet the condition. However, I found that if a data table has a deletion trigger, in this case, to delete a batch of qualified records in this table, you can only delete one record that meets the condition each time you execute the delete statement.
Example:
In the tbldocument table, you must delete records whose status is 4001. In the database, tbldocument's primary key docid is also the foreign key of multiple other tables.
Process:
Create a delete trigger on the table tbldocument. before deleting the qualified records in the table tbldocument, delete the data in the associated table.
Problem Discovery:
Statement: delete from tbldocument where status = 4001. Only one record with status = 4001 can be deleted at a time in the tbldocument table.
Solution:
Use the while statement to delete all objects:
While (select count (*) from tbldocument where status = '000000')> 0
Begin
Delete from tbldocument where status = '20140901'
If (select count (*) from tbldocument where status = '000000')> 0
Continue
End