The trigger is a special stored procedure that triggers execution when inserted, deleting, or modifying data in a particular table, which has more fine and more complex data control capability than the database itself. The database trigger has the following roles: * Security. The user can have some of the rights to operate the database based on the value of the database. # You can limit the user's operation based on the time limit, for example, the database data is changed after get off work and holidays. # You can restrict the user's operation based on data in the database, for example, no increase in the price of stocks over 10% at a time. * Audit. You can track the user's operation on the database. # Audit the statement of the user operating the database. # Write the user's update to the audit table. * Implement complex data integrity rules. # Implement non-standard data integrity checks and constraints. The trigger can result in more complex limitations than rules. Unlike rules, triggers can reference columns or database objects. For example, the trigger can fall back to any attempted to eat futures that exceed their own margin. # Provides a variable default value. * Implement complex non-standard database-related integrity rules. The flip-flop can be updated on the database related to the database. For example, the delete trigger on the AuthS table Author_code column can cause the corresponding to delete the matching line in other tables. # Modify or delete time-class modifications or delete the matching lines in other tables. # Seize the line in other tables into a null value when modifying or deleting. # Connect the row in other tables to the default value when modifying or deleting. # The flip-flop can reject or roll back the changes of the destruction-related integrity, and unsearched transactions attempting to perform data updates. This trigger works when inserted an external key that does not match its maintenance. For example, you can generate an insert trigger on the books.author_code column. If the new value does not match a value in the auths.author_code column, insertion is rolled back. * Synchronize the data in the table in real time. * Automatically calculate the data value, if the value of the data reaches a certain request, the specific process is performed. For example, if the funds on the company's account are less than 50,000 yuan, they will send a warning data to the finance. Oracle has a certain difference from the trigger of the Sybase database, which will be described below to describe the role of these two database triggers, respectively.
Two Oracle trigger
Oracle generates the syntax of the database trigger: create [or report] Trigger Trigger Name Trigger Time Trigger Event ON Name [for Each ROW] PL / SQL statement in which the trigger name: The name of the trigger object. Since the trigger is automatically executed automatically, the name is just a name, no substantive purpose. Trigger time: Indicate when the trigger is executed, the value is available: Before --- Indicates the trigger execution before the database action; After --- Indicates the start after the database action. Trigger event: indicating which database moves trigger this trigger: Insert: Database Insert trigger this trigger; Update: Database modification triggers this trigger; DELETE: Database delete trigger this trigger. Table name: The table where the database trigger is located. For Each ROW: Performs each row trigger of the table once. If there is no such option, only the entire table is performed once. Example: The following trigger is triggered before updating table auths, and the purpose is not allowed to modify the table on the weekend Trigger Auth_Secure Before Insert or Update or Delete / / Trigger ON Auths Begin if (To_Char (Sysdate, 'DY) before updating ') =' Sun 'raise_application_error (-20600,' can't modify table auths' on weekends); end if; end
The three Sybase database trigger Sybase database trigger is very similar to Oracle, only smaller differences. Sybase generates the syntax of the trigger: CREATE TRIGGER Trigger Name ON Name FOR INSERT, UPDATE, DELETE AS SQL_STATEMENT | For INSERT, UPDATE AS IF Update (Column_name) [and | or update] ... SQL_Statements top for The clause is used to specify which data update commands on the trigger can activate the trigger. The if update clause checks the type of operation of the specified column, which can specify multiple columns in the if Update clause. Unlike oracle, for each SQL statement, the trigger is only executed once. The trigger is executed immediately after the data update statement is completed. The trigger and the statement that starts it are treated as a transaction, and the transaction can fall back in the trigger. The following is an example of the writing of the Sybase trigger. Create Trigger forinsert_books on books for insert as if (Select Count (*) from auths, inserted where authous.author_code = insert.author_code)! = @@ Rowcount Begin
