ORACLE trigger usage notes

Syntax Rules: Create [or replace] trigger [mode.] trigger name

Before | after insert | Delete | (update of column name)

On Table Name

[For each row]

When Condition

PL/SQL Block

Note:

The meaning of for each row is that in a statement that operates a table, each row that succeeds is triggered once. If you do not write a row that is a table-Level Trigger, no matter how many rows are operated, trigger only once;

The emergence of the when condition indicates that the trigger may be triggered during DML operations, but the trigger may not necessarily do the actual work. For example, when the condition after the when is not true, the trigger simply skips the PL/SQL block;

Example:

SQL Code

2. Create Or Replace TriggerWf_tri_user_list beforeInsert Or Update Or Delete OnUser_list
4. ForEach row
6. Declare
8. UID varchar2 (10); useq varchar2 (10); asql varchar2 (200); namea varchar2 (200); nameb varchar2 (200 );
10. Begin
12. Namea: =Null;
14. Nameb: =Null;
16. If insertingThen
18. Insert IntoWflow. bpm_org_user (userid, username, diaplayname, SEQ)Values(: New. user_id,: New. user_name,: New. user_realname,: New. user_id );
20. Dbms_output.put_line ('Insert Trigger IsChufale .....');
24. EndIf;
26. If updatingThen
28. If (: New. user_name <>: Old. user_name)And(: New. user_realname <>: Old. user_realname)Then
30. Namea: =: New. user_name;
32. Nameb: =: New. user_realname;
34. Asql: ='UpdateWflow. bpm_org_userSetDiaplayname =: 1WhereUsername =: 2 ';
36. ExecuteImmediate asql using namea, nameb;
38. Else
40. If: New. user_name <>: Old. user_nameThen
42. Namea: =: New. user_name;
44. Asql: ='UpdateWflow. bpm_org_userSetUser_name =: 1WhereUsername =: 2 ';
46. ExecuteImmediate asql using namea;
48. Else
50. If: New. user_realname <>: Old. user_realnameThen
52. Nameb: =: New. user_realname;
54. Asql: ='UpdateWflow. bpm_org_userSetDiaplayname =: 1WhereUsername =: 2 ';
56. ExecuteImmediate asql using nameb,: Old. user_id;
58. EndIf;
60. EndIf;
62. EndIf;
64. EndIf;
66. If deletingThen
68. UpdateWflow. bpm_org_jobusersSetUserid = 0Where: Old. user_id = useridAndParentid =-1;
70. Delete FromWflow. bpm_org_jobusersWhereUserid =: Old. user_id;
72. DeleteWflow. bpm_org_userWhereUserid =: Old. user_id;
74. EndIf;
76. Commit;
78. End;

 

Keywords:

: New and: Old use methods and meanings. New only appears in insert and update, and old only appears in update and delete. New indicates the row data to be inserted during insert, new indicates the new data to be replaced during update, and old indicates the original row to be changed, old indicates the data to be deleted.

Note:

Commit cannot be used in triggers.