Oracle PL/SQL Tutorial/Trigger/Before Event Trigger — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 1 BEFORE EVENT triggers, are for preventing the event from actually happening
- 2 BEFORE INSERT OR UPDATE OF id
- 3 Change data in a before insert or update trigger
- 4 Check and change new value in a before-insert trigger
- 5 Check new value in before insert trigger
- 6 Check new value in before trigger
- 7 convert character values to upper case
- 8 Fire a trigger before an update of the table
- 9 Implementing Autonumbering Functionality
- 10 Insert before trigger
- 11 Use trigger to create autoincrement column
BEFORE EVENT triggers, are for preventing the event from actually happening
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key,
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>
SQL> create or replace trigger emp_biu
2 before insert or update
3 on employee
4 referencing new as new old as old
5 for each row
6 begin
7 if :new.city = "VAN" and :new.salary < 1000 then
8 raise_application_error (-20999,"Wrong salary!");
9 end if;
10
11 if :new.city = "VAN" and :new.salary >3000 then
12 raise_application_error (-20999,"Too much!");
13 end if;
14 end;
15 /
Trigger created.
SQL>
SQL>
SQL>
SQL> update employee set salary = salary + salary, city="VAN";
update employee set salary = salary + salary, city="VAN"
*
ERROR at line 1:
ORA-20999: Too much!
ORA-06512: at "sqle.EMP_BIU", line 7
ORA-04088: error during execution of trigger "sqle.EMP_BIU"
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> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
BEFORE INSERT OR UPDATE OF id
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key,
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>
SQL> CREATE OR REPLACE TRIGGER CheckID
2 BEFORE INSERT OR UPDATE OF id
3 ON employee
4 FOR EACH ROW
5 DECLARE
6 v_ID NUMBER(5);
7 BEGIN
8 SELECT id
9 into v_ID
10 FROM employee
11 where id = :new.id;
12 EXCEPTION
13 WHEN NO_DATA_FOUND THEN
14 RAISE_APPLICATION_ERROR(-20000, :new.id || " is not a " ||" valid ID");
15 END CheckID;
16 /
Trigger created.
SQL>
SQL>
SQL> update employee set salary = 1000;
8 rows updated.
SQL>
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("99","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
*
ERROR at line 1:
ORA-20000: 99 is not a valid ID
ORA-06512: at "sqle.CHECKID", line 10
ORA-04088: error during execution of trigger "sqle.CHECKID"
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
Change data in a before insert or update trigger
SQL>
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> CREATE OR REPLACE TRIGGER employee_insert_update
2 BEFORE INSERT OR UPDATE ON employee
3 FOR EACH ROW
4 DECLARE
5 dup_flag INTEGER;
6 BEGIN
7 --Force all employee names to uppercase.
8 :NEW.first_name := UPPER(:NEW.first_name);
9 END;
10 /
Trigger created.
SQL>
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>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
Check and change new value in a before-insert trigger
SQL>
SQL> CREATE TABLE incremented_values
2 (value_inserted NUMBER,
3 value_incremented NUMBER);
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER increment_by_one
2 BEFORE INSERT ON incremented_values
3 FOR EACH ROW
4 BEGIN
5 :new.value_incremented := :new.value_incremented + 1;
6 END;
7 /
Trigger created.
SQL>
SQL> CREATE OR REPLACE TRIGGER increment_by_two
2 BEFORE INSERT ON incremented_values
3 FOR EACH ROW
4 BEGIN
5 IF :new.value_incremented > 1 THEN
6 :new.value_incremented := :new.value_incremented + 2;
7 END IF;
8 END;
9 /
Trigger created.
SQL>
SQL> INSERT INTO incremented_values VALUES(1,1);
1 row created.
SQL>
SQL> SELECT * FROM incremented_values;
VALUE_INSERTED VALUE_INCREMENTED
-------------- -----------------
1 2
SQL>
SQL>
SQL> DROP TABLE incremented_values;
Table dropped.
SQL>
Check new value in before insert trigger
SQL>
SQL> CREATE TABLE employee (
2 id number,
3 name varchar(100),
4 birth_date date,
5 gender varchar2(30) );
Table created.
SQL>
SQL> CREATE TRIGGER employee_bir
2 BEFORE INSERT ON employee
3 FOR EACH ROW
4 begin
5 if upper(:new.name) = "J" then
6 raise_application_error(20000, "Sorry, that genius is not allowed.");
7 end if;
8 end;
9 /
Trigger created.
SQL>
SQL> INSERT INTO employee (id,name,birth_date,gender )VALUES (100,"J",to_date("19230823", "YYYYMMDD"),"MALE" );
INSERT INTO employee (id,name,birth_date,gender )VALUES (100,"J",to_date("19230823", "YYYYMMDD"),"MALE" )
*
ERROR at line 1:
ORA-21000: error number argument to raise_application_error of 20000
is out of range
ORA-06512: at "sqle.EMPLOYEE_BIR", line 3
ORA-04088: error during execution of trigger "sqle.EMPLOYEE_BIR"
SQL>
SQL> drop table employee;
Table dropped.
Check new value in before trigger
SQL>
SQL> CREATE TABLE game_player
2 (player_id NUMBER,
3 game_id NUMBER,
4 group_number NUMBER,
5 marked VARCHAR2(1) DEFAULT "N",
6 pcmac VARCHAR2(1) DEFAULT "N",
7 score NUMBER,
8 CONSTRAINT game_player_pk
9 PRIMARY KEY (player_id, game_id, group_number));
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER set_score
2 BEFORE INSERT ON game_player
3 FOR EACH ROW
4 WHEN ( new.score IS NULL )
5 BEGIN
6 IF :NEW.marked = "Y" THEN
7 :NEW.score := 10;
8 ELSIF :NEW.pcmac = "Y" THEN
9 :NEW.score := 5;
10 END IF;
11 END;
12 /
Trigger created.
SQL>
SQL>
SQL>
SQL> drop table game_player;
Table dropped.
convert character values to upper case
SQL>
SQL> create table employee
2 (
3 empl_no integer primary key
4 ,lastname varchar2(20) not null
5 ,firstname varchar2(15) not null
6 ,midinit varchar2(1)
7 ,street varchar2(30)
8 ,city varchar2(20)
9 ,state varchar2(2)
10 ,zip varchar2(5)
11 ,zip_4 varchar2(4)
12 ,area_code varchar2(3)
13 ,phone varchar2(8)
14 ,company_name varchar2(50));
Table created.
SQL>
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1,"Jones","Joe","J","10 Ave","New York","NY","11111","1111","111", "111-1111","A Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Smith","Sue","J","20 Ave","New York","NY","22222","2222","222", "222-111","B Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Anderson","Peggy","J","500 St","New York","NY","33333","3333","333", "333-3333","C Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Andy","Jill", null,"930 St","New York","NY","44444","4444","212", "634-7733","D Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"OK","Carl","L","19 Drive","New York","NY","55555","3234","212", "243-4243","E Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Jee","Q","38 Ave","New York","NY","66666","4598","212", "454-5443","F Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Baker","Paul","V","738 St.","Queens","NY","77777","3842","718", "664-4333","G Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Young","Steve","J","388 Ave","New York","NY","88888","3468","212", "456-4566","H Associates Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","T","9300 Ave","Kansas City","MO","99999","3658","415", "456-4563","J Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hackett","Karen","S","Kings Rd. Apt 833","Bellmore","NY","61202","3898","516", "767-5677","AA Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bob","Jack","S","12 Giant Rd.","Newark","NJ","27377","3298","908", "123-7367","Z Associates");
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> create trigger employee_name
2 before update or insert on employee
3 for each row
4 begin
5 /* convert character values to upper case */
6 :new.lastname := upper( :new.lastname );
7 :new.firstname := upper( :new.firstname );
8 dbms_output.put_line("trigger fired");
9 end;
10 /
Trigger created.
SQL>
SQL>
SQL> insert into employee
2 (empl_no, firstname, lastname)
3 values
4 (3423, "dave", "anderson");
1 row created.
SQL>
SQL> select firstname, lastname
2 from employee
3 where empl_no = 3423;
FIRSTNAME LASTNAME
--------------- --------------------
DAVE ANDERSON
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
Fire a trigger before an update of the table
SQL>
SQL>
SQL>
SQL> create table myaudit(
2 id VARCHAR2(4 BYTE) NOT NULL,
3 old_value VARCHAR2(40 BYTE),
4 new_value VARCHAR2(40 BYTE)
5 );
Table created.
SQL>
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>
SQL> CREATE OR REPLACE TRIGGER before_employee_salary_update
2 BEFORE UPDATE OF salary
3 ON employee
4 FOR EACH ROW WHEN (new.salary < old.salary * 0.75)
5 BEGIN
6 dbms_output.put_line("id = " || :old.id);
7 dbms_output.put_line("Old salary = " || :old.salary);
8 dbms_output.put_line("New salary = " || :new.salary);
9 dbms_output.put_line("The salary reduction is more than 25%");
10
11 INSERT INTO Myaudit (
12 id, old_value, new_value
13 ) VALUES (
14 :old.id, :old.salary, :new.salary
15 );
16 END before_employee_salary_update;
17 /
Trigger created.
SQL>
SQL> update employee set salary = 0;
8 rows updated.
SQL>
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 0 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 0 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 0 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 0 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 0 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 0 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 0 New York Manager
08 James Cat 17-SEP-96 15-APR-02 0 Vancouver Tester
8 rows selected.
SQL>
SQL> select * from myaudit;
ID OLD_VALUE NEW_VALUE
---- ---------------------------------------- ----------------------------------------
01 1234.56 0
02 6661.78 0
03 6544.78 0
04 2344.78 0
05 2334.78 0
06 4322.78 0
07 7897.78 0
08 1232.78 0
8 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL> drop table myaudit;
Table dropped.
SQL>
SQL>
SQL>
Implementing Autonumbering Functionality
SQL>
SQL>
SQL> CREATE SEQUENCE idSeq;
Sequence created.
SQL>
SQL> CREATE TABLE myTable (Name VARCHAR(50) PRIMARY KEY NOT NULL,
2 PhoneNo VARCHAR(15));
Table created.
SQL>
SQL> CREATE TABLE myTableAudit
2 (id INT PRIMARY KEY NOT NULL,
3 Operation VARCHAR(10),
4 RecordedOn DATE DEFAULT SysDate,
5 OldName VARCHAR(50),
6 NewName VARCHAR(50),
7 OldPhone VARCHAR(15),
8 NewPhone VARCHAR(15));
Table created.
SQL>
SQL> CREATE OR REPLACE TRIGGER idAutonumberTrigger
2 BEFORE INSERT ON myTableAudit
3 FOR EACH ROW
4 BEGIN
5 SELECT idSeq.NEXTVAL
6 INTO :NEW.id FROM DUAL;
7 END;
8 /
Trigger created.
SQL>
SQL> drop SEQUENCE idSeq;
Sequence dropped.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL> drop table myTableAudit;
Table dropped.
SQL>
Insert before trigger
SQL>
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> CREATE OR REPLACE TRIGGER employee_insert_update
2 BEFORE INSERT OR UPDATE ON employee
3 FOR EACH ROW
4 DECLARE
5 dup_flag INTEGER;
6 BEGIN
7 --Force all employee names to uppercase.
8 :NEW.first_name := UPPER(:NEW.first_name);
9 END;
10 /
Trigger created.
SQL>
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>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
Use trigger to create autoincrement column
SQL>
SQL> CREATE TABLE myTable (
2 myTableID INT PRIMARY KEY NOT NULL,
3 Name VARCHAR(50),
4 PhoneNo VARCHAR(15) DEFAULT "Unknown Phone");
Table created.
SQL>
SQL> CREATE SEQUENCE SEQ;
SQL>
SQL> CREATE OR REPLACE TRIGGER AUTONUMBER
2 BEFORE INSERT ON myTable
3 FOR EACH ROW
4 BEGIN
5 SELECT SEQ.NEXTVAL
6 INTO :NEW.myTableID FROM DUAL;
7 END;
8 /
Trigger created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.