Oracle PL/SQL/Trigger/Business Logic Trigger — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 09:58, 26 мая 2010
Содержание
A trigger restricting updates
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> -- A trigger restricting updates.
SQL> -- This trigger allows changes to employee records
SQL> -- only on Mondays through Fridays, and only during
SQL> -- the hours of 8:00am to 5:00pm.
SQL>
SQL> CREATE OR REPLACE TRIGGER only_during_business_hours
2 BEFORE INSERT OR UPDATE OR DELETE ON employee
3 BEGIN
4 IF TO_NUMBER(TO_CHAR(SYSDATE,"hh24")) < 8 -- nothing before 8:00am
5 OR TO_NUMBER(TO_CHAR(SYSDATE,"hh24")) >= 5
6 -- changes must be made BEFORE 5:00pm
7 OR TO_CHAR(SYSDATE,"dy") in ("sun","sat") THEN -- nothing on weekends
8 RAISE_APPLICATION_ERROR (-20000, "Employee changes only allowed during business hours.");
9 END IF;
10 END;
11 /
Trigger created.
SQL>
SQL>
SQL> update employee set first_name = "asdf";
update employee set first_name = "asdf"
*
ERROR at line 1:
ORA-20000: Employee changes only allowed during business hours.
ORA-06512: at "sqle.ONLY_DURING_BUSINESS_HOURS", line 6
ORA-04088: error during execution of trigger "sqle.ONLY_DURING_BUSINESS_HOURS"
SQL>
SQL> select * from employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
SQL>
A Trigger to check the available room
SQL>
SQL>
SQL> CREATE TABLE place (
2 room_id NUMBER(5) PRIMARY KEY,
3 building VARCHAR2(15),
4 room_number NUMBER(4),
5 number_seats NUMBER(4),
6 description VARCHAR2(50)
7 );
Table created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20001, "Building 7", 201, 1000, "Large Lecture Hall");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20002, "Building 6", 101, 500, "Small Lecture Hall");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20003, "Building 6", 150, 50, "Discussion Room A");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20004, "Building 6", 160, 50, "Discussion Room B");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20005, "Building 6", 170, 50, "Discussion Room C");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20006, "Music Building", 100, 10, "Music Practice Room");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20007, "Music Building", 200, 1000, "Concert Room");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20008, "Building 7", 300, 75, "Discussion Room D");
1 row created.
SQL>
SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20009, "Building 7", 310, 50, "Discussion Room E");
1 row created.
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER CheckRoomID
2 BEFORE INSERT OR UPDATE OF room_id
3 ON session
4 FOR EACH ROW
5 DECLARE
6 v_RoomID NUMBER(5);
7 BEGIN
8 SELECT room_id
9 into v_RoomID
10 FROM place
11 where room_id = :new.room_id;
12 EXCEPTION
13 WHEN NO_DATA_FOUND THEN
14 RAISE_APPLICATION_ERROR(-20000, :new.room_id || " is not a " ||
15 " valid room");
16 END CheckRoomID;
17 /
Trigger created.
SQL>
SQL>
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
*
ERROR at line 1:
ORA-20000: 20000 is not a valid room
ORA-06512: at "sqle.CHECKROOMID", line 10
ORA-04088: error during execution of trigger "sqle.CHECKROOMID"
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
*
ERROR at line 1:
ORA-20000: is not a valid room
ORA-06512: at "sqle.CHECKROOMID", line 10
ORA-04088: error during execution of trigger "sqle.CHECKROOMID"
SQL>
SQL>
SQL>
SQL> drop table session;
Table dropped.
SQL>
SQL> drop table place;
Table dropped.
Define trigger to force all department names to uppercase
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> -- Example of a trigger.
SQL> CREATE OR REPLACE TRIGGER department_insert_update
2 BEFORE INSERT OR UPDATE ON dept
3 FOR EACH ROW
4 DECLARE
5 dup_flag INTEGER;
6 BEGIN
7 --Force all department names to uppercase.
8 :NEW.dname := UPPER(:NEW.dname);
9 END;
10 /
Trigger created.
SQL>
SQL> -- Testing the department_insert_update trigger.
SQL> INSERT INTO dept (deptno, dname) VALUES (10,"payroll");
1 row created.
SQL>
SQL> INSERT INTO dept (deptno, dname) VALUES (11,"Sewage");
1 row created.
SQL>
SQL> UPDATE dept SET dname = "Payroll" WHERE deptno = 10;
2 rows updated.
SQL>
SQL> SELECT deptno, dname FROM dept WHERE deptno BETWEEN 10 AND 11;
DEPTNO DNAME
---------- --------------
10 PAYROLL
10 PAYROLL
11 SEWAGE
SQL>
SQL>
SQL>
SQL>
SQL> drop table dept;
Table dropped.
SQL>
SQL>
SQL>
SQL>
SQL>
Force all department names to uppercase in a trigger
SQL>
SQL> create table department
2 ( dept_id number(2),
3 dept_name varchar2(14),
4 no_of_emps varchar2(13)
5 )
6 /
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER department_insert_update
2 BEFORE INSERT OR UPDATE ON department
3 FOR EACH ROW
4 DECLARE
5 dup_flag INTEGER;
6 BEGIN
7
8 :NEW.dept_name := UPPER(:NEW.dept_name);
9 END;
10 /
Trigger created.
SQL>
SQL>
SQL> INSERT INTO department VALUES (10, "a", "NEW YORK");
1 row created.
SQL> INSERT INTO department VALUES (20, "b", "DALLAS");
1 row created.
SQL> INSERT INTO department VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO department VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL> select * from department;
DEPT_ID DEPT_NAME NO_OF_EMPS
---------- -------------- -------------
10 A NEW YORK
20 B DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4 rows selected.
SQL>
SQL> drop table department;
Table dropped.
SQL>
SQL>
SQL> --
Trigger to check inserting value
SQL> --
SQL>
SQL> set echo on
SQL>
SQL> create table t1 ( x int primary key, y int );
Table created.
SQL> create table t2 (col1 int references t1, col2 int check (col2>0));
Table created.
SQL> create index t2_idx on t2(col2,col1);
Index created.
SQL>
SQL> create trigger t2_trigger before insert or update of col1, col2 on t2 for each row
2 begin
3 if ( :new.col1 < :new.col2 ) then
4 raise_application_error(-20001,"Invalid Operation Col1 cannot be less then Col2");
5 end if;
6 end;
7 /
Trigger created.
SQL>
SQL> insert into t2(col1, col2) values(1,2);
insert into t2(col1, col2) values(1,2)
*
ERROR at line 1:
ORA-20001: Invalid Operation Col1 cannot be less
then Col2
ORA-06512: at "sqle.T2_TRIGGER", line 3
ORA-04088: error during execution of trigger
"sqle.T2_TRIGGER"
SQL> insert into t2(col1, col2) values(2,1);
insert into t2(col1, col2) values(2,1)
*
ERROR at line 1:
ORA-02291: integrity constraint
(sqle.SYS_C006395) violated - parent key not
found
SQL>
SQL>
SQL> drop table t1 cascade constraints;
Table dropped.
SQL>
SQL> drop table t2 cascade constraints;
Table dropped.
SQL>
SQL> --
Trigger to check the employee count per department
SQL>
SQL> CREATE TABLE employee(
2 emp_id INTEGER,
3 emp_name VARCHAR2(32),
4 supervised_by INTEGER,
5 pay_rate NUMBER(9,2),
6 pay_type CHAR);
Table created.
SQL> CREATE TABLE department
2 (dept_id INTEGER,
3 dept_name VARCHAR2(32));
Table created.
SQL>
SQL> ALTER TABLE department
2 ADD CONSTRAINT pk_dept PRIMARY KEY (dept_id);
Table altered.
SQL>
SQL> CREATE TABLE emp_dept (emp_id INTEGER, dept_id INTEGER, CONSTRAINT unq_1 unique (emp_id, dept_id));
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER only_two_departments
2 BEFORE UPDATE OR INSERT ON emp_dept
3 FOR EACH ROW
4 DECLARE
5 dept_count INTEGER; --# of depts for this employee
6 max_depts INTEGER := 2; --max number of depts per employee.
7 BEGIN
8 SELECT COUNT(*) INTO dept_count
9 FROM emp_dept
10 WHERE emp_id = :NEW.emp_id;
11
12 IF :OLD.emp_id = :NEW.emp_id THEN
13 RETURN;
14 ELSE
15 IF dept_count >= max_depts THEN
16 RAISE_APPLICATION_ERROR (-20000,"Employees are limited to a max of two departments.");
17 END IF;
18 END IF;
19 END;
20 /
Trigger created.
SQL>
SQL> INSERT INTO employee (emp_id,emp_name) VALUES (401,"Harvey Wallbanger");
1 row created.
SQL> INSERT INTO employee (emp_id,emp_name) VALUES (402,"Scarlet Tanninger");
1 row created.
SQL> INSERT INTO department (dept_id, dept_name) VALUES (401,"Fermentation");
1 row created.
SQL> INSERT INTO department (dept_id, dept_name) VALUES (402,"Distillation");
1 row created.
SQL> INSERT INTO department (dept_id, dept_name) VALUES (403,"Bottling");
1 row created.
SQL> INSERT INTO emp_dept (emp_id, dept_id) VALUES (401,401);
1 row created.
SQL> INSERT INTO emp_dept (emp_id, dept_id) VALUES (401,402);
1 row created.
SQL> INSERT INTO emp_dept (emp_id, dept_id) VALUES (402,402);
1 row created.
SQL> INSERT INTO emp_dept (emp_id, dept_id) VALUES (402,403);
1 row created.
SQL> INSERT INTO emp_dept (emp_id, dept_id) VALUES (401,403);
INSERT INTO emp_dept (emp_id, dept_id) VALUES (401,403)
*
ERROR at line 1:
ORA-20000: Employees are limited to a max of two
departments.
ORA-06512: at "sqle.ONLY_TWO_DEPARTMENTS", line
13
ORA-04088: error during execution of trigger
"sqle.ONLY_TWO_DEPARTMENTS"
SQL> UPDATE emp_dept SET dept_id = 403 WHERE emp_id = 401 AND dept_id = 402;
UPDATE emp_dept SET dept_id = 403 WHERE emp_id = 401 AND dept_id = 402
*
ERROR at line 1:
ORA-04091: table sqle.EMP_DEPT is mutating,
trigger/function may not see it
ORA-06512: at "sqle.ONLY_TWO_DEPARTMENTS", line
5
ORA-04088: error during execution of trigger
"sqle.ONLY_TWO_DEPARTMENTS"
SQL>
SQL> drop table employee cascade constraints;
Table dropped.
SQL> drop table department cascade constraints;
Table dropped.
SQL> drop table emp_dept cascade constraints;
Table dropped.
SQL> --