Trigger 3 (learning notes)

Row-level DML triggers

Define for each row when an update operation is triggered for a record.

Use the ": Old. Field" and ": New. Field" identifier

No.	Trigger statement	: Old Field	: New Field
1	Insert	Undefined. The field content is null.	After the insert operation is complete
2	Update	Original value before data update	New value after the update operation
3	Delete	Original value before deletion	Undefined. All fields are null.

": Old. Field" and ": New. field is only valid for Row-level triggering. If the trigger is not defined for each ro, the syntax error is returned.

Example 1,When adding employee information, the position must be selected within the position, and the salary cannot exceed 5000. The employment date is the current date.

 

-- Create trigger create or replace trigger myempinsert before insert on myemp for each row declare v_date date; v_jobcount number; begin select count (empno) into v_jobcount from myemp where: New. job In (select distinct job from myemp); If v_jobcount = 0 then -- the position raise_application_error (-20009, 'position error') is not found; else if: New. SAL> 5000 then raise_application_error (-20003, 'salary cannot exceed 5000 '); end if;: New. hiredate: = sysdate; end myempinsert; -- call and execute declarebegin -- add error message insert into myemp (empno, ename, job, Mgr, Sal, deptno) values (9999, 'bdqn ', 'mnager ', 7788,8000, 10); Exception when others then dbms_output.put_line (sqlerrm); end;

 

Example 2,Myemp salary increase cannot exceed 10%

-- Create trigger create or replace trigger myempaddsal_trigger Before update on myemp for each rowdeclarebegin If ABS (: New. sal-: Old. sal)/: Old. sal)> 0.1 then raise_application_error (-20005, 'maximum salary increase cannot exceed 10% '); end if; end myempaddsal_trigger; -- call declarebegin -- add error message -- insert into myemp (empno, ename, job, Mgr, Sal, deptno) values (9999, 'bdqn', 'mnager ', 7788,8000, 10); Update myemp set sal = 3000 where empno = 7369; exception when others then dbms_output.put_line (sqlerrm); end;

  

Example 3,Use Sequence

 

-- Create Table create table mt (ID number, name varchar2 (30), address varchar2 (50), constraint pd_mtid primary key (ID); select * from Mt; -- create sequence mt_se; -- create trigger create or replace trigger mt_trigger before insert on Mt for each rowdeclare -- local variables herebegin select mt_se.nextval into: New. ID from dual; end mt_trigger; -- insert data into MT (name, address) values ('zhang san', 'shenzhen '); insert into MT (name, address) values ('lily', 'shenzhen'); select * from Mt; -- the ID is automatically added.

12c Automatic sequence

 

The following statements can be used in oracle12c: more intuitive

-- Create Table create table mt (ID number, name varchar2 (30), address varchar2 (50), constraint pd_mtid primary key (ID )); create Table mtme as select * from Mt where 1 = 2; select * from Mt; select * From mtme; -- create sequence mt_se; -- create trigger create or replace trigger mtme before insert on mtme for each rowdeclare begin Delete mtme; insert into MT (ID, name, address) values (mt_se.nextval,: New. name,: New. address); End mtme; -- insert data insert into MT (name, address) values ('zhang san', 'shenzhen '); insert into MT (name, address) values ('Li si', 'shenzhen ');

 

Trigger 3 (learning notes)