"Oracle" triggers are simple to implement
Objective: Real-time backup uertest table data to usertest_temp, two-table structure consistent resolution: Synchronization results with Oracle triggers: 1. Build a table?
| 1 2 3 4, 5 6 7 8 9 10 11 12 13 14 15 |
[SQL]--simple user table CREATE table usertest (NAME VARCHAR2 NOT NULL, age number, Isdelete VARCHAR2 (4) )--Backup Table CREATE TABLE usertest_temp (NAME VARCHAR2 NOT NULL, age number, Isdelete VARCHAR2 (4)) |
2, Trigger?
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
[SQL] CREATE or REPLACE TRIGGER tr_user_temp before INSERT OR UPDATE OR delete on Usertest F OR each row declare begin IF inserting then insert into usertest_temp ( Name,age,isdelete) VALUES ( :new.name, :new.age, : new.isdelete ); elsif deleting then delete usertest_temp WHERE name =:old.name; elsif updating then update usertest_temp set Age =:new.age, isdelete =:new.isdelete where name =:old.name; end IF; end; |
3. usertest table to perform additions and deletions to check the operation, the Usertest_temp table will also change the note: punctuation must be English punctuation, or there will be compiler error understanding concept: Triggers (trigger) is a special stored procedure, Its execution is triggered by an event that automatically implicitly runs, such as when an action is performed on a table (insert,delete,update). Triggers are often used to enforce data integrity constraints and business rules. Triggers can be found in dba_triggers, user_triggers data dictionaries. The only difference between a trigger and a stored procedure is that a trigger cannot execute an EXECUTE statement call, but instead automatically triggers execution when the user executes a Transact-SQL statement. Create syntax:?
| 1 2 3 4 5 6 7 8 9 10 |
[SQL] CREATE [OR REPLACE] TRIGGER trigger_name {before | After} {INSERT | DELETE | UPDATE [of column [, Column ...]]} [OR {INSERT | DELETE | UPDATE [of column [, Column ...]]} ...] on [schema.] table_name | [Schema.] view_name [referencing {old [as] old | NEW [as] new| Parent as parent} [for each ROW] [when condition] pl/sql_block | Call procedure_name; |
Note: Create [or replace] TRIGGER: Creates or replaces before and after: Trigger timing is triggered "triggers the currently created trigger" and "after triggering event" triggers the currently created trigger after executing the trigger "for Each row: Row trigger. row triggers: Each data row that is affected activates one trigger, as long as they conform to trigger constraints; statement trigger: The entire statement action is invoked as the triggering event. Activates a trigger once it meets the constraint. when the For each row option is omitted, the before and after triggers are statement triggers, and the instead OF triggers are only row triggers. Referencing: Describes the relevant name, which can be used in the PL/SQL block and when clause of a row trigger to reference the current new and old column values, with the default related names old and new respectively. When you apply a correlation name to a pl/sql block of a trigger, you must precede them with a colon (:), but you cannot add a colon in the When clause. The When clause describes the trigger constraint. When Condition is a logical expression, it must contain the relevant name, not the query statement, or call the Pl/sql function. The trigger constraint specified by the When clause can only be used in before and after-row triggers and cannot be used in instead of row triggers and other types of triggers. Note the following points: 1. Triggers do not accept parameters. 2. A table can have up to 12 triggers, but at the same time, colleagues, similar triggers can only be one and not contradictory. 3. The more triggers on a table, the greater the performance impact on the DML operations on the table. 4. The maximum trigger is 32KB. If you do, you can create a procedure and then invoke it in a trigger with a call statement. 5. You cannot use DDL statements (CREATE, ALTER, DROP) with DML statements (SELECT, INSERT, UPDATE, DELETE) in the execution part of the trigger. 6. A trigger cannot contain a transaction control statement (Commit,rollback,savepoint). 7. No transaction control statements can be used for any procedure or function that is invoked in the trigger body. 8. You cannot declare any long and BLOB variables in the trigger body. The new value, the old value, and the OID cannot be made to any long and BLOB columns in the table. 9. The syntax format and function of different types of triggers, such as DML triggers, INSTEAD of triggers, and system triggers, differ considerably. PROBLEM: When triggers are triggered, use the column values in the records that are inserted, updated, or deleted, and sometimes use the values before and after the action. Implementation: The value of the column after the :new modifier access operation :old modifier access operation completion Front value attribute INSERT UPDATE DELETE old NULL Actual value actual value new actual value NULL
