Trigger 4 (learning notes), trigger learning notes

Trigger 4 (learning notes), trigger learning notes
REFERENCING clause:Use ": new. field "or": old. if the field is not clearly marked, you can use the REFERENCING clause to set the alias for the two identifiers. For example, you can set ": new" to emp_new, or set ": old": emp_old.

Create or replace trigger before update on myemp referencing old as myemp_old new as myemp_new for each rowdeclare begin if abs (: myemp_new.sal-: myemp_old.SAL)/: myemp_old.SAL) 0.1 THEN raise_application_error (-20005, 'maximum salary increase cannot exceed 10% '); end if; end myempaddsal_trigger;

Use the WHEN clause to define the trigger conditions:": New" and ": old" can also be used in the WHEN clause to access the data before and after modification. WHEN new and old are used in the WHEN clause, the preceding ":" is not added. Example 1,Increase the number of employees to determine whether the employee's salary exists. If the salary is 0, an error is returned.

-- Create trigger create or replace trigger myemptestadd1 before insert on myemp for each row when (new. sal = 0) declarebegin raid _ application_error (-20003,: NEW. EMPNO | 'The salary is 0, which is not required! '); End myemptestadd1; -- execute the Add declarebegininsert into myemp (empno, ename, job, mgr, sal, deptno) VALUES (9999, 'bdqn', 'mnager', 0, 10); exception when others then dbms_output.put_line (SQLERRM); END; -- result ORA-20003: 9999 of the salary is 0, not required! ORA-06512: An error occurred while executing the trigger 'tests. MYEMPTESTADD1 ", line 4ORA-04088: trigger 'tests. myemptestadd1'

Example 2,Requires that wages only increase and not decrease

-- Create trigger create or replace trigger myemptestadd1 before UPDATE on myemp for each row when (new. sal <old. sal) declarebegin raid _ application_error (-20003,: old. EMPNO | 'the salary can only rise and cannot be lowered! '); End myemptestadd1; -- update declarebegin update myemp SET sal = 2000 WHERE empno = 7788; exception when others then dbms_output.put_line (SQLERRM); END; -- result ORA-20003: 7788 of wages can only rise and cannot be lowered! ORA-06512: An error occurred while executing the trigger 'tests. MYEMPTESTADD1 ", line 4ORA-04088: trigger 'tests. myemptestadd1'

Trigger predicates:

 

Trigger definition provides three trigger predicates: INSERTING, UPDATING, and DELETING

No.	Trigger predicates	Description
1	INSERTING	If the trigger statement is INSERT, TRUE is returned; otherwise, FALSE is returned.
2	UPDATING	If the trigger statement is UPDATE, TRUE is returned; otherwise, FALSE is returned.
3	DELETING	If the trigger statement is DELETE, TRUE is returned; otherwise, FALSE is returned.

 

 

Example 3,Use the deptlog table to record related operation dept tables

-Create table deptlog (logid NUMBER, TYPE VARCHAR2 (20) not null, deptno NUMBER (2), logdate DATE, dname Varchar2 (14) not null, loc Varchar2 (13) not null, CONSTRAINT pk_logid primary key (logid); -- create sequence 'create SEQUENCE deptlog_seq; -- create trigger create or replace trigger dept_trigger before insert or update or delete on dept for each rowdeclare begin if inserting then insert into deptlog (logid, type, deptno, logdate, dname, loc) VALUES (deptlog_seq.nextval, 'insert',: new. deptno, SYSDATE,: NEW. DNAME,: new. loc); ELSIF updating then insert into deptlog (logid, type, deptno, logdate, dname, loc) VALUES (deptlog_seq.nextval, 'update',: new. deptno, SYSDATE,: NEW. DNAME,: new. loc); ELSIF deleting then insert into deptlog (logid, type, deptno, logdate, dname, loc) VALUES (deptlog_seq.nextval, 'delete',: old. deptno, SYSDATE,: old. DNAME,: old. loc); end if; end dept_trigger; -- test data insert into dept (deptno, dname, loc) VALUES (87, 'test', 'sz '); insert into dept (deptno, dname, loc) VALUES (43, 'pr ', 'sz'); UPDATE dept SET dname = 'extension Department 'WHERE deptno = 66; delete from dept WHERE deptno = 87; commit; -- Query SELECT * FROM dept; -- Query table SELECT * FROM deptlog;

Use FOLLOWS clause

When multiple triggers are created for a table, they are not triggered in the order you want,

Add FOLLOWS trigger 1 name after for each row, indicating that the trigger is triggered after trigger 1

 

-- Create three identical triggers -- trigger 1 create or replace trigger dept1 before insert or update or delete on dept for each rowdeclare begin dbms_output.put_line ('execute 1st triggers dept1 '); end dept1; -- trigger 2 create or replace trigger dept2 before insert or update or delete on dept for each ROW follows dept1declare begin dbms_output.put_line ('execute 2nd triggers dept2'); end dept1; -- trigger 3 create or replace trigger dept3 before insert or update or delete on dept for each ROW follows dept2declare begin dbms_output.put_line ('execute 3rd triggers dept3'); end dept1;

 

Update, delete, and add operations

Insert into dept (deptno, dname, loc) VALUES (87, 'test', 'sz '); insert into dept (deptno, dname, loc) VALUES (43, 'pr ', 'sz'); UPDATE dept SET dname = 'extension Department 'WHERE deptno = 66; delete from dept WHERE deptno = 87;

Result:

Execute 1st triggers dept1 execute 2nd triggers dept2 execute 3rd triggers dept3

Trigger in the specified order