sql_undo_30.zip_SQL 脚本

SQL-UNDO 3.0 service for Microsoft SQL server 2000 ================================================== The service is meant for undoing any commited in the past transaction. In point of fact, it is a service of "UNDO" in the multiuser mode. Installation ------------ 1. Run install.sql script on database where you want to have the service. The library's objects will be installed. The objects are following: 1) Table "SUActions" 2) Table "SURowsInfo" 3) Table "SUColsInfo" 4) Table "SULogins" 5) Stored procedure "spSUSaveActions" 6) Stored procedure "spSUUndoTransaction" 7) Stored procedure "spSUUndoLastOwnTransaction" 8) Stored procedure "spSUSetup" 2. Run procedure "spSUSetup" with the first string parameter "Create SQL-UNDO triggers". exec spSUSetup 'Create SQL-UNDO triggers' Triggers of the service will be installed by the stored procedure. After the action SQL-UNDO library will work and you will be have the prepared UNDO-service in multiuser mode within your client-server application. NB. You can place a table's name as the second parameter. Then the SQL-UNDO trigger will be created only on this table. Thus, you can solve where UNDO-service will work and where - not. Also you can drop the triggers on some separate tables by the following syntax: exec spSUSetup 'Remove SQL-UNDO triggers' [, TableName ] Thus, you can manage creating-removing of the SQL-UNDO triggers in work process at any time. Deinstallation -------------- Run procedure "spSUSetup" with the first string parameter "Remove SQL-UNDO objects" and the second string parameter "Yes, I really want to delete all SQL-UNDO objects with data" exec spSUSetup 'Remove SQL-UNDO objects','Yes, I really want to delete all SQL-UNDO objects with data' All tables, stored procedures and triggers of SQL-UNDO library will be killed. Work restrictions ----------------- 1. SQL-UNDO will work on tables where there are the Primary Keys and the Primary Keys containt only one field. Tables, where there are the composite Primary Keys, will be omitted by SQL-UNDO service. 2. SQL-UNDO will work on tables where there are not IMAGE, TEXT and NTEXT fields, because to work with blobs are impossible within triggers in MSSQL2000. It is a restriction of Microsoft. Tables, where there are blobs fields, will be omitted by SQL-UNDO service. 3. An ordinary user cannot undo an transaction of another user. Only DBA or member of "db_owner" can undo such transaction. 4. It is impossible - to undo an transaction, if the data within the records was changed after. SQL-UNDO puzzles out correctly such situations and gives warnings. 5. There is an particular situation. When table has IDENTITY column, the action was DELETE and the user is not the table's owner and not member of db_ddladmin, db_owner or sysadmin roles. Undoing of such action will be fail, because the undo-action will be INSERT with the directive "SET IDENTITY_INSERT", but an ordinary user has no rights to set the directive. And changing the rights are impossible. It is a restriction of Microsoft. Such action can be undo by DBA. 6. If your triggers modify other tables, set up the 'nested trigger' option for correct work of SQL-UNDO. The service saves data for undoing with the help of triggers. 7. The SQL-UNDO service cannot be useful for databases with a big degree of transaction's activity because of the reason of the performance. 8. If you change Primary Key's value, you will not be able to undo the action and all previous actions on this record. How it works ------------ When you do changes in the database, SQL-UNDO saves previous data, types of the actions, ID of the transaction and another information into special tables with the simple structure: SUActions, SURowsInfo, SUColsInfo and SULogins. Then, when you want to undo an transaction, you run stored procedure spSUUndoTransaction or spSUUndoLastOwnTransaction, that takes data from the tables, builds dynamic queries for back actions and executes them within one transaction. spSUUndoTransaction has one parameter "@IdAction". You can take Value for the parameter from the field "Id" of the table "SUActions". If the action was runned within a transaction, all actions of the transaction will be undone too. You can build a service of viewing actions on the SQL-UNDO tables for comfortable work with the SQL-UNDO service. The tables' structures are very easy and understandable. Licensing and limitations ------------------------- SQL-UNDO library is fully freeware. It is "AS IS". You can use, modify, copy it without any restrictions. But any warranties, responsibilities are not given and any claims will not be taken. You use the library on your own risk. Gleb Oufimtsev gvu@newmail.ru http://www.gvu.newmail.ru Moscow, Russia, January 2002 If you like my work, why you didn't still invite me to work? :-) (It's a joke. Almost)