Databases Reference
In-Depth Information
4.
Create and populate a table, and call it
TRAVELS_COUNTER
:
CREATE TABLE TRAVELS_COUNTER (ID NUMBER(9) NOT NULL);
INSERT INTO TRAVELS_COUNTER(ID) VALUES (0);
COMMIT;
5.
Create a trigger to populate the primary key of the
TRAVELS
table using the
TRAVELS_COUNTER
table:
CREATE OR REPLACE TRIGGER HR.TR_TRAVELS_INS
BEFORE INSERT ON HR.TRAVELS FOR EACH ROW
WHEN (NEW.TRAVELID IS NULL)
DECLARE MYID HR.TRAVELS.TRAVELID%TYPE;
BEGIN
SELECT ID + 1 INTO MYID FROM TRAVELS_COUNTER FOR UPDATE;
UPDATE TRAVELS_COUNTER SET ID = MYID
RETURNING ID INTO :NEW.TRAVELID;
END;
6.
Populate the
TRAVELS
table for measuring performance:
SET TIMING ON
INSERT INTO HR.TRAVELS(EMPLOYEE_ID, LOCATION_ID,
START_DATE, END_DATE)
SELECT E.EMPLOYEE_ID, L.LOCATION_ID, SYSDATE, SYSDATE + ROWNUM
FROM HR.EMPLOYEES E, HR.LOCATIONS L;
SET TIMING OFF
7.
Create a trigger to populate the primary key of the
TRAVELS
table using the
TRAVEL_SEQ
sequence:
CREATE OR REPLACE TRIGGER TR_TRAVELS_INS
BEFORE INSERT ON TRAVELS FOR EACH ROW
WHEN (NEW.TRAVELID IS NULL)
BEGIN
SELECT TRAVEL_SEQ.NEXTVAL INTO :NEW.TRAVELID FROM DUAL;
END;
