Trigger-based automatic backup for deleting a row Field

Trigger: automatic backup of a row field deleted by: enterprise database time: 10:52:07

At first, write a simple trigger: Create trigger trigger_delete_tmaintain On tmaintain for Delete As Begin Set nocount on; Insert into tdeletemaintainlog Select * From deleted End Go (Here we will introduce: Functions of set nocount on: When set nocount is on, no count is returned (indicating the number of rows affected by the transact-SQL statement ). When set nocount is off, return the count. If some statements contained in the stored procedure do not return much actual data, this setting greatly reduces network traffic and significantly improves performance. ) Start executing the above trigger. The problem arises. Error message: Message 311, level 16, status 1, process trigger_delete_tmaintain, row 12th Text, ntext, or image Columns cannot be used in the 'inserted' and 'deleted' tables. Problem: Create trigger trigger_delete_tmaintain On tmaintain for Delete cannot use for, instead (Introduction: After The specified trigger is triggered only when all specified operations in the SQL statement are successfully executed. This trigger can be executed only after all reference cascade operations and constraints check are completed successfully. If only the for keyword is specified, after is the default setting. You cannot define an After trigger on a view. Instead Specify the trigger to be executed, instead of the SQL statement to be triggered, to replace the trigger statement operation. In a table or view, each insert, update, or delete statement can define up to one instead trigger. However, you can define a view on each view with an instead of trigger. The instead of trigger cannot be defined in the updatable view with check option. If an instead of trigger is added to the updatable view with the check option specified, SQL Server generates an error. You must use alter view to delete this option before defining the instead of trigger. After does not exist in sql2005, which is equivalent to. ) There is another problem: Error message: Message 213, level 16, status 1, process trigger_delete_thardrepair, row 12th Insert error: the number of column names or provided values does not match the table definition. An error occurs because fields are added to the table to be deleted. What should I do? Simple: insert into tdeletehardrepairlog ([Hardrepairid] .... , [Makerphone]) Select * From deleted (write fields one by one) The problem is solved. Run the command. Then we will delete: Test statement: (select * From tmaintain Select * From tdeletemaintainlog Delete from tmaintain where maintainid = 1 Delete from tdeletemaintainlog where maintainid = 1) Execute Delete from tmaintain where maintainid = 1. The problem arises again. Error message: Message 544, level 16, status 1, process trigger_delete_tmaintain, row 12th When identity_insert is set to off, explicit values cannot be inserted for the ID column in the table 'tdeletemaintainlog. The statement has been terminated. Set identity_insert should be used to allow explicit values to be inserted into the table's ID column. Switch off to on. Add before and after insertion, set identity_insert tdeletemaintainlog on; Insert... Set identity_insert tdeletemaintainlog off; The problem is solved. Run the command. Then we will delete: It is still not deleted, but there is an automatic backup. What should I do? The method should be correct. Is it possible to delete it in this trigger. Add after insert: Delete thardrepair where hardrepairid in (select hardrepairid from deleted) Run it. No problem. Then we will delete: Yes. Deleted and automatically backed up. Success. The Code is as follows: -- ===================================================== ========== Set ansi_nulls on Go Set quoted_identifier on Go -- ===================================================== ====== -- Author: LCQ -- Create Date: 2010/4/7 -- Description: Create a trigger for the tmaintain deletion operation. -- ===================================================== ====== Alter trigger trigger_delete_tmaintain On tmaintain instead of Delete As Begin Set nocount on; Set identity_insert tdeletemaintainlog on; Insert into tdeletemaintainlog ([Maintainid] , [Question] , [Solution] , [Outtime] , [Inplacetime] , [Finishedtime] , [Predicttime] , [Region] , [Sign] , [Standby] , [Clientsid] , [Technician] , [Sendperson] , [Description] , [Accepttime] , [Productid] , [Number] , [Status]) Select * From deleted Delete tmaintain where maintainid in (select maintainid from deleted) Set identity_insert tdeletemaintainlog off; End Go (Note: In SQL2000, the inserted Table and the deleted table are used to store the modification information of the Data row in the table. They are automatically created when the trigger is executed. They are stored in the memory and are temporary tables. When the trigger is completed, they are also deleted. They are read-only tables and cannot be written to them.   Inserted Table: used to store copies of rows affected by insert and update statements. This means that the inserted or updated record rows are temporarily saved in the inserted Table. When an insert or update statement is executed, the newly added row is added to both the inserted Table and the trigger table. Therefore, you can check whether the inserted data meets the requirements from the inserted Table. If the data does not meet the requirements, perform rollback and undo operations (C branch O.   Deleted table: used to store copies of the rows affected by the delete and update statements. This means that the record rows before the deletion or update are temporarily saved in the delete table. When executing the delete or update statement, the row is deleted from the trigger table and uploaded to the deleted table. Therefore, you can check whether the deleted data rows can be deleted from the deleted table.   So when the value of a record in the table changes, the values before the change have already saved copies of deleted or inserted record rows in the deleted and inserted tables created by the system. We can query the values before the changes from these two tables and assign them to the variables .)