Simple implementation of Oracle triggers

[Oracle] Simple implementation of triggers

Objective: To back up data in the uertest table to usertest_temp in real time. The two tables have the same structure. Use the oracle trigger to synchronize data. Result: 1. Create 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 (20) not null, AGE NUMBER, ISDELETE VARCHAR2 (4 )) -- backup table create table USERTEST_TEMP (NAME VARCHAR2 (20) 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 FOR 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. if you add, delete, modify, and query the USERTEST table, the usertest_temp table will be changed accordingly. Note: The punctuation marks must be English Punctuation Marks. Otherwise, a compilation error may occur to understand the concept: trigger) it is a special stored procedure, and its execution is triggered by events to automatically and implicitly run [for example, when performing operations (insert, delete, update) on a table) will activate it for execution]. Triggers are often used to enhance data integrity constraints and business rules. The trigger can be found in the DBA_TRIGGERS and USER_TRIGGERS data dictionary. The only difference between a trigger and a stored procedure is that a trigger is automatically triggered when a user executes a Transact-SQL statement instead of calling an EXECUTE 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: create or replace before and AFTER: the trigger sequence is "trigger the trigger currently created before the trigger event is executed" and "Trigger After the trigger event is executed" for each row: row triggers. Row triggers: the trigger is activated once for each affected data row as long as they meet the trigger constraints. Statement triggers: the entire statement operation is used as a trigger event. when it meets the constraints, activate a trigger once. If the for each row option is omitted, BEFORE and AFTER triggers are statement triggers, while instead of triggers can only be ROW triggers. REFERENCING: indicates the relevant names. In the PL/SQL block and WHEN clauses of a row trigger, you can use the relevant names to refer to the current NEW and OLD column values. The default names are OLD and NEW. WHEN the relevant names are applied in the PL/SQL block of the trigger, you must add a colon (:) before them, but not in the WHEN clause. The WHEN clause specifies the trigger constraint. Condition is a logical expression, which must contain the relevant name, but cannot contain query statements, nor can it call PL/SQL functions. The trigger constraints specified by the WHEN clause can only be used in BEFORE and AFTER row triggers, and cannot be used in instead of row triggers or other types OF triggers. Note the following: 1. The trigger does not accept parameters. 2. A table can have up to 12 triggers, but at the same time, colleagues and similar triggers can only have one and cannot conflict with each other. 3. The more triggers on a table, the greater the impact on the performance of DML operations on the table. 4. The maximum trigger size is 32 KB. If necessary, you can first create a process and then use the CALL statement in the trigger to CALL it. 5. Only DML statements (SELECT, INSERT, UPDATE, and DELETE) can be used for trigger execution. DDL statements (CREATE, ALTER, and DROP) cannot be used ). 6. The trigger cannot contain transaction control statements (COMMIT, ROLLBACK, and SAVEPOINT ). 7. No process or function called in the trigger body can use transaction control statements. 8. You cannot declare any Long or blob variables in the trigger body. The new value and old value cannot be added to any long or blob columns in the table. 9. Syntax formats and functions OF different types OF triggers (such as DML triggers, instead of triggers, and system triggers) are quite different. Problem: When a trigger is triggered, you must use the column values in the inserted, updated, or deleted records, and sometimes use the values in the pre-and post-operation columns. implementation: NEW modifier value of the column after the access operation is completed: OLD modifier value before the access operation is completed value features insert update deleteold null actual value NEW actual value NULL