Trigger 2 (learning notes), trigger learning notes

Trigger 2 (learning notes), trigger learning notes
DML triggers (statement triggers)

Triggered by a DML statement. An INSERT, UPDATE, or DELETE operation is triggered when the user executes the INSERT, UPDATE, and DELETE operations.

Example 1,New employees can be hired and resigned only on the 10th day of each month. Data of new employees cannot be added or deleted at other times.

-- Create table myemp as select * FROM emp;

-- Create trigger create or replace trigger changemyemp_trigger before insert or delete on myemp declare v_curdate Varchar2 (20); begin select to_char (SYSDATE, 'dd') INTO v_curdate FROM dual; IF trim (v_curdate) <> '10' THEN Raise_application_error (-20003, 'onboarding and resignation are allowed on the 10 th of each month'); end if; end changemyemp_trigger;

-- Add or delete data to or from a table: DECLAREBEGIN -- insert into myemp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (8888, 'test ', 'cler', 7369, SYSDATE, 8000, NULL, 10); delete from myemp WHERE empno = 7369; exception when others then dbms_output.put_line (SQLERRM); END;

If the date is incorrect, the following message is displayed:

ORA-20003: onboarding and resignation are permitted on the 10th of each month

Example 2,The myemp table cannot be updated on weekends and off-duty times (before and after ).

-Create trigger create or replace trigger changemyemp_trigger before insert or delete on myemp declare v_curhour Varchar2 (20); v_week VARCHAR2 (20); begin select to_char (SYSDATE, 'day '), to_char (SYSDATE, 'hh24') INTO v_week, v_curhour FROM dual; IF trim (v_week) IN ('satur', 'sunday') THEN Raise_application_error (-20003, 'myemp table cannot be updated on weekends '); elsif trim (v_curhour) <'9' or trim (v_curhour)> '18' THEN Raise_application_error (-20004, 'Update myemp tables not allowed during off-duty Time'); end if; end changemyemp_trigger;

-- Add or delete data to or from a table: DECLAREBEGIN -- insert into myemp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (8888, 'test ', 'cler', 7369, SYSDATE, 8000, NULL, 10); delete from myemp WHERE empno = 7369; exception when others then dbms_output.put_line (SQLERRM); END;

The result is a weekend:

ORA-20003: The myemp table ORA-06512 cannot be updated on weekends: An error occurred while executing the "TESTS. CHANGEMYEMP_TRIGGER", line 7ORA-04088: trigger 'tests. CHANGEMYEMP_TRIGGER'

Normal off-duty time:

ORA-20004: The myemp table ORA-06512 is not allowed to be updated during off-duty hours: An error occurred while executing the "TESTS. CHANGEMYEMP_TRIGGER", line 9ORA-04088: trigger 'tests. changemp_trigger'

Example 3,Every employee pays taxes based on the basic wage income, which is less than 2000, 3% ~ 5000, 10%, or above, a new table is required for storage, employee ID, name, Salary Commission, and tax payable, the records are automatically updated after the SAL and COMM fields in the employee table are modified.

-CREATE myemp_tax table create table myemp_tax (empno NUMBER (4), ename VARCHAR2 (10), sal NUMBER (), comm NUMBER (), tax NUMBER ), CONSTRAINT pk_myempno primary key (empno), CONSTRAINT fk_myempno foreign key (empno) REFERENCES myemp (empno) on delete cascade );

-- Create trigger create or replace trigger myemp_out after insert or update or delete on myemp declare PRAGMA AUTONOMOUS_TRANSACTION; -- trigger independent transaction CURSOR cur_myemp is select * FROM myemp; -- Define the cursor to find the record v_sal myemp for each row. sal % TYPE; -- Define variable calculation revenue v_myemptax myemp_tax.tax % TYPE; -- tax v_myemp myemp % ROWTYPE; begin delete from myemp_tax; -- clear myemp_tax table; FOR v_myemp IN cur_myemp LOOP v_sal: = v_myemp.sal + nvl (v_myemp.comm, 0); -- calculate the total salary IF v_sal <2000 THEN v_myemptax: = v_sal * 0.03; -- pay 3% ELSIF v_sal BETWEEN 2000 AND 5000 THEN v_myemptax: = v_sal * 0.08; -- tax 8% ELSIF v_sal> 5000 THEN v_myemptax: = v_sal * 0.1; -- tax 10% end if; insert into myemp_tax (empno, ename, sal, comm, tax) VALUES (v_myemp.empno, v_myemp.ename, v_myemp.sal, v_myemp.comm, v_myemptax); end loop; COMMIT; end myemp_out;

-- Add a record to the myemp table and query the insert into myemp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (8898, 'test', 'cler', 7369, SYSDATE, 800,100, 10); SELECT * FROM myemp_tax;