Trigger 3 (learning notes)

Source: Internet
Author: User
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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.